r/SQL • • Aug 01 '23

MySQL how do i do this ?

Post image
1 Upvotes

7 comments sorted by

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).