SQL Query
-
Hi,
i have a sample table:
table
ID flag name
1 0 test1
1 0 test2
1 1 test3
2 0 test4
2 0 test5
3 1 test6
4 1 test7
4 1 test8
i would like to have a query that can have a result:
if same id and have both flag = 0 and 1 then only show row with flag =0
if same id and have all flag of 0 then show all the row with flag 0
if id only have flag of 1 then show that row or rows.
Query result:
ID flag name
1 0 test1
1 0 test2
2 0 test4
2 0 test5
3 1 test6
4 1 test7
4 1 test8
Please advised.
Thanks
Ddee
-
Thanks to ChatGPT for creating the temp table
DROP TABLE if exists #TempTable;
go
CREATE TABLE #TempTable
(
ID INT,
flag int,
name NVARCHAR(50)
);
-- Insert data into the temporary table
INSERT INTO #TempTable (ID, flag, name)
VALUES
(1, 0, 'test1'),
(1, 0, 'test2'),
(1, 1, 'test3'),
(2, 0, 'test4'),
(2, 0, 'test5'),
(3, 1, 'test6'),
(4, 1, 'test7'),
(4, 1, 'test8');
select *
from #TempTable t
where t.flag=0
or
(t.flag=1
and not exists (select 1
from #TempTable tt
where tt.ID=t.ID
and tt.flag=0));Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
You can get it via this SQL code:
SELECT t.ID, t.flag, t.name
FROM your_table_name t
WHERE
(t.flag = 0 AND NOT EXISTS (
SELECT 1 FROM your_table_name
WHERE ID = t.ID AND flag = 1
))
OR
(t.flag = 0 AND NOT EXISTS (
SELECT 1 FROM your_table_name
WHERE ID = t.ID AND flag = 1
))
OR
-- Show rows with flag = 1 when ID only has flag = 1
(t.flag = 1 AND NOT EXISTS (
SELECT 1 FROM your_table_name
WHERE ID = t.ID AND flag = 0
));Replace 'your_table_name' with the actual name of your table.
I hope this will work!!
- Priyanka Chouhan wrote:
You can get it via this SQL code:
SELECT t.ID, t.flag, t.name
FROM your_table_name t
WHERE
(t.flag = 0 AND NOT EXISTS (
SELECT 1 FROM your_table_name
WHERE ID = t.ID AND flag = 1
))
OR
(t.flag = 0 AND NOT EXISTS (
SELECT 1 FROM your_table_name
WHERE ID = t.ID AND flag = 1
))
OR
-- Show rows with flag = 1 when ID only has flag = 1
(t.flag = 1 AND NOT EXISTS (
SELECT 1 FROM your_table_name
WHERE ID = t.ID AND flag = 0
));Replace 'your_table_name' with the actual name of your table.
I hope this will work!!
You might want to have a second look... The OR's will very likely be a performance killer and cause heavy resource usage.
Your code also returns an incorrect answer for the given test data...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Steve Collins wrote:
Thanks to ChatGPT for creating the temp table
Let's see what the actual prompt for that was, please.
For those interested, here's a link to the prompt that I used and the code it produced. There's really no excuse for people to not create test data either when they post of if they decide to try to solve someone's problem that hasn't done such I thing.
https://chat.openai.com/share/166b76be-aa16-4bed-b3b0-b18f0ff41b15
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Steve Collins wrote:
Thanks to ChatGPT for creating the temp table
DROP TABLE if exists #TempTable;
go
CREATE TABLE #TempTable
(
ID INT,
flag int,
name NVARCHAR(50)
);
-- Insert data into the temporary table
INSERT INTO #TempTable (ID, flag, name)
VALUES
(1, 0, 'test1'),
(1, 0, 'test2'),
(1, 1, 'test3'),
(2, 0, 'test4'),
(2, 0, 'test5'),
(3, 1, 'test6'),
(4, 1, 'test7'),
(4, 1, 'test8');
select *
from #TempTable t
where t.flag=0
or
(t.flag=1
and not exists (select 1
from #TempTable tt
where tt.ID=t.ID
and tt.flag=0));It would be interesting to see how this operates on a much larger table with some decent indexing. On this small of a table, the execution plan has a node that reads 25 rows to output 1 row. Hopefully and as it frequently happens, the optimizer would make a better choice in the plan especially in the presence of some indexes on larger data.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Ddee,
You can achieve the desired result using the following SQL query:
sql
SELECT ID, flag, name
FROM your_table t
WHERE (flag = 0 AND NOT EXISTS (SELECT 1 FROM your_table WHERE ID = t.ID AND flag = 1))
OR (flag = 1 AND EXISTS (SELECT 1 FROM your_table WHERE ID = t.ID AND flag = 0))
OR flag = 1;
Replace your_table with the actual name of your table. This query will give you the expected output as mentioned in your query result. Let me know if you have question. Thanks
Thanks
Ellen Litwack
SQL Assignment Helper at Buddy Assignment Help -
Hi, Ellen. Welcome aboard.
It'll be interesting to see how the ORs in that affect performance on larger tables. I'll try to setup a larger test tonight.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Here is an alternative that a) does not use an
ORand b) does not use a subquery.WITH TempOrdered AS
(
SELECT t.ID
, t.flag
, t.name
, DENSE_RANK() OVER(PARTITION BY t.ID ORDER BY t.flag) AS dr
FROM #TempTable AS t
)
SELECT t.ID
, t.flag
, t.name
FROM TempOrdered AS t
WHERE t.dr = 1Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - buddyhelp wrote:
Hi Ddee,
You can achieve the desired result using the following SQL query:
sql
SELECT ID, flag, name FROM your_table t WHERE (flag = 0 AND NOT EXISTS (SELECT 1 FROM your_table WHERE ID = t.ID AND flag = 1)) OR (flag = 1 AND EXISTS (SELECT 1 FROM your_table WHERE ID = t.ID AND flag = 0)) OR flag = 1;
Replace your_table with the actual name of your table. This query will give you the expected output as mentioned in your query result. Let me know if you have question. Thanks
Tested this... It returns an incorrect result set and it has the same resource issues as all the other proposed solutions above.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - drew.allen wrote:
Here is an alternative that a) does not use an
ORand b) does not use a subquery.WITH TempOrdered AS
(
SELECT t.ID
, t.flag
, t.name
, DENSE_RANK() OVER(PARTITION BY t.ID ORDER BY t.flag) AS dr
FROM #TempTable AS t
)
SELECT t.ID
, t.flag
, t.name
FROM TempOrdered AS t
WHERE t.dr = 1Drew
+1000000 Drew! This bit of simplicity returns the correct result set, does only the required single scan, and wins in all cases. Even indexes didn't help the others.
The internet is full of "Master SQL" and "Advanced SQL" classes. Those titles are click bait and people, who unfortunately don't know any different, complete the courses, think they've "mastered" advanced SQL, go to an interview, and then wonder why they weren't chosen for the job. Ladies and Gentlemen, the simple analysis that Drew did and then materialized as code is a good example of the reason why. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
For anyone that wants to do a little analysis as to why the use of OR is so bad and to do a little performance testing, here's code to build a million row test example.
--=====================================================================================================================
-- Create the test table and load it with data
--=====================================================================================================================
DROP TABLE IF EXISTS #TestTable;
GO
--===== Create the test table. No indexes were provided in original problem.
CREATE TABLE #TestTable
(
ID INT
,Flag BIT
,Name VARCHAR(50)
)
;
--===== Large data set for performance testing
-- Randomly produces 1 to 4 rows for each ID with random flags of 0 or 1
-- Name is generated by ROW_NUMBER() and is UNIQUE for each row.
DECLARE @Rows INT = POWER(10,6);
INSERT INTO #TestTable WITH (TABLOCK)
(ID, Flag, Name)
SELECT TOP (@Rows)
ID = t.value
,Flag = r2.Flag
,Name = CONCAT('Test',RIGHT('0000000'+CONVERT(VARCHAR(10),ROW_NUMBER() OVER (ORDER BY t.value)),7))
FROM GENERATE_SERIES(1,CONVERT(INT,@Rows/2.4)) t
CROSS APPLY (SELECT value FROM GENERATE_SERIES(1,ABS(CHECKSUM(NEWID())%4)+1))r4(DupeCount)
CROSS APPLY (SELECT ABS(CHECKSUM(NEWID())%2))r2(Flag)
ORDER BY Name
;--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
@steve-2 Collins,
I'm still interested in the ChatGPT prompt that you used to generate the populated test table because it'll help people understand how to use ChatGPT to do such a thing.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Yes sorry, I didn't save that gpt context. Iirc I posted the question in its entirety and asked for sample data and a solution. The query it came up (which was the same/similar to what Priyanka Chouhan posted above (after my reply)) wasn't what I considered a good one. So I wrote the query above which was accepted as the answer. Once the answer is accepted... My approach in general has been to offer a working solution which is as simple as possible. Imo many times this is a helpful approach given the questioner's level of experience. If performance is a requirement then yes it could be refactored
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
DENSE_RANK is the better solution in this case though. Good on Drew it's another interesting one
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply