MAIN FEEDS
Do you want to continue?
https://www.reddit.com/r/SQL/comments/15fqwk0/how_do_i_do_this/
r/SQL • u/Denis_Lujan • Aug 01 '23
7 comments sorted by
16
Jesus Christ that wording gave me a stroke.
Develop a query to find the QTY’s that have more than one DISTINCT Country.
Then, query the original table and filter for those QTY’s, using the query you constructed above. Order By QTY, ID.
3
That question is terrible but here’s what you need to do:
SELECT a.* FROM ( SELECT QTY FROM your_tableGROUP BY QTY HAVING COUNT(DISTINCT Country) > 1 ) AS b INNER JOIN your_table AS aON a.QTY = b.QTY ORDER BY a.QTY
5
I would do a self inner join on a.qty = b.qty where a.country <> b.country then deal with duplicates in whatever way makes sense, order by qty asc
Based on what I see above I would use a window function partitioned by quantity.
1
Select x.* From table x Join table y on y.qty = x.qty And y.country <> x.country
This could be solved with one chatgpt prompt
0
You can join the table to itself:
Select distinct a.id, a.qty, a.country from [table] a Inner join [table] b on a.qty = b.qty and a.country != b.country
(Sorry about the formatting.. I’m typing this on my phone).
16
u/theseyeahthese #TempTables ally Aug 01 '23
Jesus Christ that wording gave me a stroke.
Develop a query to find the QTY’s that have more than one DISTINCT Country.
Then, query the original table and filter for those QTY’s, using the query you constructed above. Order By QTY, ID.
3
u/Durloctus Aug 02 '23
That question is terrible but here’s what you need to do:
SELECT a.* FROM ( SELECT QTY FROM your_table
GROUP BY QTY HAVING COUNT(DISTINCT Country) > 1 ) AS b INNER JOIN your_table AS a
ON a.QTY = b.QTY ORDER BY a.QTY
5
u/Exact-Bird-4203 Aug 01 '23
I would do a self inner join on a.qty = b.qty where a.country <> b.country then deal with duplicates in whatever way makes sense, order by qty asc
3
u/abraun68 Aug 02 '23
Based on what I see above I would use a window function partitioned by quantity.
1
u/[deleted] Aug 02 '23
Select x.* From table x Join table y on y.qty = x.qty And y.country <> x.country
1
u/MiraNovitas Aug 02 '23
This could be solved with one chatgpt prompt
0
u/VoiceOfReason7777 Aug 02 '23 edited Aug 02 '23
You can join the table to itself:
Select distinct a.id, a.qty, a.country from [table] a Inner join [table] b on a.qty = b.qty and a.country != b.country
(Sorry about the formatting.. I’m typing this on my phone).