Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Finding combinations of values Expand / Collapse
Author
Message
Posted Thursday, December 13, 2012 1:20 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 8, 2014 9:24 AM
Points: 51, Visits: 140
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
Posted Thursday, December 13, 2012 1:38 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Posted Thursday, December 13, 2012 1:43 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 8, 2014 9:24 AM
Points: 51, Visits: 140
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
Posted Thursday, December 13, 2012 1:44 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Posted Thursday, December 13, 2012 1:51 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:32 PM
Points: 2,030, Visits: 3,027
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1396387
Posted Thursday, December 13, 2012 5:54 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:28 PM
Points: 1,945, Visits: 2,893
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
Posted Friday, December 14, 2012 8:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:32 PM
Points: 2,030, Visits: 3,027
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1396681
Posted Friday, December 14, 2012 8:16 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Posted Friday, December 14, 2012 8:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:32 PM
Points: 2,030, Visits: 3,027
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1396688
Posted Friday, December 14, 2012 4:23 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:28 PM
Points: 1,945, Visits: 2,893
.. 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 123»»»

Permissions Expand / Collapse