Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Finding combinations of values
Add to briefcase
21 posts, Page 1 of 3
123»»»

Finding combinations of values Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
robin.pryor
Posted Thursday, December 13, 2012 1:20 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 17, 2012 7:53 AM
Points: 10, Visits: 23
My table structure

CREATE TABLE trefClientShares{
intLMid int NULL,
intMLid int NULL,
intISid int NULL,
intFRid int NULL
}

I need to figure out a query that will delete all rows that don't have at least 2 non-null values.
Post #1396369
GSquared
Posted Thursday, December 13, 2012 1:38 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:45 PM
Points: 15,442, Visits: 9,572 
delete from MyTable
where Col1 is null and Col2 is null and Col3 is null
or Col1 is null and Col2 is null and Col4 is null
or Col1 is null and Col3 is null and Col4 is null
or Col2 is null and Col3 is null and Col4 is null;

Seems like that should do it, right?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1396380
robin.pryor
Posted Thursday, December 13, 2012 1:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 17, 2012 7:53 AM
Points: 10, Visits: 23
That did it. I have no idea why I was unable to see the solution without asking. Brain fry. Everything starting to look the same!!! Thanks!
Post #1396382
GSquared
Posted Thursday, December 13, 2012 1:44 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:45 PM
Points: 15,442, Visits: 9,572
You're welcome. Glad I could help.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1396383
ScottPletcher
Posted Thursday, December 13, 2012 1:51 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 10:11 AM
Points: 1,333, Visits: 1,803
Here's another possible approach, in case you ever have to do (many) more columns :


DELETE FROM dbo.trefClientShares
WHERE
    CASE WHEN intLMid IS NULL THEN 1 ELSE 0 END +
    CASE WHEN intMLid IS NULL THEN 1 ELSE 0 END +
    CASE WHEN intISid IS NULL THEN 1 ELSE 0 END +
    CASE WHEN intFRid IS NULL THEN 1 ELSE 0 END >= 3

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson
Post #1396387
CELKO
Posted Thursday, December 13, 2012 5:54 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
My table structure


Please read just one little book on SQL and RDBMS. Please, just one little book! This is not a table; it has no key; it has to way to ever have a key!! Why did you put the data type as prefix on the column names? That was 1960's BASIC, not SQL! SQL does not use {} like you did. Her is a guess at fixing the disaster.

CREATE TABLE Client_Shares
(share_id CHAR(10) NOT NULL PRIMARY KEY,
lm_id INTEGER,
ml_id INTEGER,
is_id INTEGER,
r_id INTEGER);

I need to figure out a query that will delete all rows that don't have at least 2 non-null values.


No, you need to figure out a lot more than that!

DELETE FROM Client_Shares
WHERE IFNULL(lm_id, 1) + IFNULL(ml_id, 1)
+ IFNULL(is_id, 1) + IFNULL(t_id, 1) >= 2;

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1396446
ScottPletcher
Posted Friday, December 14, 2012 8:01 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 10:11 AM
Points: 1,333, Visits: 1,803
CELKO (12/13/2012)
My table structure


Please read just one little book on SQL and RDBMS. Please, just one little book! This is not a table; it has no key; it has to way to ever have a key!! Why did you put the data type as prefix on the column names? That was 1960's BASIC, not SQL! SQL does not use {} like you did. Her is a guess at fixing the disaster.

CREATE TABLE Client_Shares
(share_id CHAR(10) NOT NULL PRIMARY KEY,
lm_id INTEGER,
ml_id INTEGER,
is_id INTEGER,
r_id INTEGER);

I need to figure out a query that will delete all rows that don't have at least 2 non-null values.


No, you need to figure out a lot more than that!

DELETE FROM Client_Shares
WHERE IFNULL(lm_id, 1) + IFNULL(ml_id, 1)
+ IFNULL(is_id, 1) + IFNULL(t_id, 1) >= 2;



We don't need a key to solve this problem, so no need for irrelevancies just to satisfy ueber-pedants.

Please read one book on SQL Server because IFNULL() does not exist in SQL Server.

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson
Post #1396681
GSquared
Posted Friday, December 14, 2012 8:16 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:45 PM
Points: 15,442, Visits: 9,572
CELKO (12/13/2012)
My table structure


Please read just one little book on SQL and RDBMS. Please, just one little book! This is not a table; it has no key; it has to way to ever have a key!! Why did you put the data type as prefix on the column names? That was 1960's BASIC, not SQL! SQL does not use {} like you did. Her is a guess at fixing the disaster.

CREATE TABLE Client_Shares
(share_id CHAR(10) NOT NULL PRIMARY KEY,
lm_id INTEGER,
ml_id INTEGER,
is_id INTEGER,
r_id INTEGER);

I need to figure out a query that will delete all rows that don't have at least 2 non-null values.


No, you need to figure out a lot more than that!

DELETE FROM Client_Shares
WHERE IFNULL(lm_id, 1) + IFNULL(ml_id, 1)
+ IFNULL(is_id, 1) + IFNULL(t_id, 1) >= 2;


IFNULL is a MySQL item. In SQL Server, you'd use IsNull or Coalesce.

Also, this solution (Joe's Where clause) is completely wrong.

Let's assume none of the columns are null, and all have positive integer values. For example, they all have 10 as their value. 10+10+10+10 = 40. 40 >= 2, so that row would be deleted. But it's not supposed to be. None of the columns are null in that row.

Total failure to understand the requirements, Joe.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1396684
ScottPletcher
Posted Friday, December 14, 2012 8:22 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 10:11 AM
Points: 1,333, Visits: 1,803
GSquared (12/14/2012)
CELKO (12/13/2012)
My table structure


Please read just one little book on SQL and RDBMS. Please, just one little book! This is not a table; it has no key; it has to way to ever have a key!! Why did you put the data type as prefix on the column names? That was 1960's BASIC, not SQL! SQL does not use {} like you did. Her is a guess at fixing the disaster.

CREATE TABLE Client_Shares
(share_id CHAR(10) NOT NULL PRIMARY KEY,
lm_id INTEGER,
ml_id INTEGER,
is_id INTEGER,
r_id INTEGER);

I need to figure out a query that will delete all rows that don't have at least 2 non-null values.


No, you need to figure out a lot more than that!

DELETE FROM Client_Shares
WHERE IFNULL(lm_id, 1) + IFNULL(ml_id, 1)
+ IFNULL(is_id, 1) + IFNULL(t_id, 1) >= 2;


IFNULL is a MySQL item. In SQL Server, you'd use IsNull or Coalesce.

Also, this solution (Joe's Where clause) is completely wrong.

Let's assume none of the columns are null, and all have positive integer values. For example, they all have 10 as their value. 10+10+10+10 = 40. 40 >= 2, so that row would be deleted. But it's not supposed to be. None of the columns are null in that row.

Total failure to understand the requirements, Joe.



Good points!

Besides which, I believe my earlier post was the same idea, except coded correctly .

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson
Post #1396688
CELKO
Posted Friday, December 14, 2012 4:23 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
.. Total failure.. Besides which, I believe my earlier post was the same idea, except coded correctly .


Yeah, it was awful!

What I wanted to do was avoid the CASE expression solution and go for something using functions that compile to simple assembly language.


DELETE FROM Client_Shares
WHERE COALESCE (SIGN(ABS(lm_id)+1), 0)
+ COALESCE (SIGN(ABS(ml_id) +1), 0)
+ COALESCE (SIGN(ABS(is_id) +1), 0)
+ COALESCE (SIGN(ABS(t_id)+1), 0) >= 2;

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1396836
« Prev Topic | Next Topic »

Add to briefcase
21 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.