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 12»»

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: Monday, July 27, 2015 9:46 AM
Points: 66, Visits: 226
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 9, 2015 10:03 AM
Points: 13,872, Visits: 9,607
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: Monday, July 27, 2015 9:46 AM
Points: 66, Visits: 226
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 9, 2015 10:03 AM
Points: 13,872, Visits: 9,607
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: Yesterday @ 4:02 PM
Points: 2,946, Visits: 4,495
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1396387
Posted Friday, December 14, 2012 8:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:02 PM
Points: 2,946, Visits: 4,495
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1396681
Posted Friday, December 14, 2012 8:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 9, 2015 10:03 AM
Points: 13,872, Visits: 9,607
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: Yesterday @ 4:02 PM
Points: 2,946, Visits: 4,495
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1396688
Posted Friday, December 14, 2012 5:11 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:02 PM
Points: 2,946, Visits: 4,495
CELKO (12/14/2012)
.. 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;



Yiikes, that's incredibly convoluted. What a royal pita to try to understand, much less change or debug later!!


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1396840
Posted Friday, December 14, 2012 5:43 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:02 PM
Points: 2,946, Visits: 4,495
CELKO (12/14/2012)
[ Yiikes, that's incredibly convoluted. What a royal pita to try to understand, much less change or debug later!!


But it is fast and portable

On a more serious note, that kind of function nesting used to be standard idioms in early SQL. If you can find some articles and books by David Rozenshtein, he did a lot of stuff with it to create the Characteristic function, etc.



Yeah, and we used to use punch cards and write our own bubble sorts too. For me to use something that indecipherable, there'd have to be a noticeable performance diff.

As to "portable", that really is a myth. There are so many custom approaches in each variant of SQL you can never port anything easily.


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1396843
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse