MySQL query: remove duplicates from ordered list

Question

My table is called items. It has the columns item_id and source as well as some other columns.

I want to do a select ordered by item_id but I also want to not have any duplicates in the source column of the result.

What's wrong with this query?

SELECT *
FROM items
WHERE item_section='sp_500'
ORDER BY item_id DESC
GROUP BY source
LIMIT 3

are you getting an output? is it an error? – t q Mar 12 '12 at 07:17 — t q, Mar 12 '12 at 07:17
says its not a valid mysql result set – MaxYarmolinsky Mar 12 '12 at 07:18 — MaxYarmolinsky, Mar 12 '12 at 07:18

Erwin Brandstetter · Accepted Answer · 2012-03-12T08:24:47.027

4

Should work like this:

SELECT *
FROM   items
JOIN  (
    SELECT max(item_id) AS item_id
    FROM   items
    WHERE  item_section = 'sp_500'
    GROUP  BY source
    ORDER  BY 1 DESC
    LIMIT  3
    ) i USING (item_id)

Why?

1) Filter by item_section = 'sp_500'

2) Collapse multiple items with the same source in a GROUP BY, because:

not have any duplicates in the source column

I take the biggest item_id per source - seems most plausible and you did not specify.

3) ORDER BY item_id DESC to get the greatest ones and LIMIT 3 (without dupes by now).

4) JOIN to the original table to get the whole row for the selected item_ids.

edited Mar 12 '12 at 08:24

answered Mar 12 '12 at 08:19

Erwin Brandstetter

605,456
145
1,078
1,228

by the way the fact that this is way beyond me tells me i really don't know much about SQL and there is alot to learn – MaxYarmolinsky Mar 12 '12 at 08:35
@user557862: We live and learn, right? :) Your problem is a bit tricky - there were 6 answers, already, that didn't get it, either. – Erwin Brandstetter Mar 12 '12 at 08:48
you've got ORDER BY 1 DESC, but you say ORDER BY item_id DESC in the explanation – raquelhortab Oct 05 '20 at 07:23
1

@raquelhortab: It's effectively the same. `ORDER BY 1` is syntax shorthand referencing the first output column. (Plus, in `ORDER BY` "item_id" resolves to the *output* column.) See: https://stackoverflow.com/a/8194088/939860 or https://stackoverflow.com/a/7630564/939860 or https://stackoverflow.com/a/8119815/939860 or https://stackoverflow.com/a/11235752/939860 - and https://www.postgresql.org/docs/current/sql-select.html#SQL-ORDERBY – Erwin Brandstetter Oct 05 '20 at 23:38

score 1 · Answer 2 · answered Mar 12 '12 at 07:19

1

First of all, you cannot select everything * when you have GROUP BY clause. You can select source in you query and some other functions like count(*).
Second, you cannot order by item_id. You can order by source and some other functions.
Third, order by should be after group by

answered Mar 12 '12 at 07:19

popfalushi

1,332
9
15

but i need the whole record in the result set not just the source – MaxYarmolinsky Mar 12 '12 at 07:22

score 1 · Answer 3 · answered Mar 12 '12 at 07:20

1

First thing is that order by item_id to go after the group by. The common rule: group by ... HAVinG ... order by ... .

answered Mar 12 '12 at 07:20

Vikram

8,235
33
47

Milap · Answer 4 · 2012-03-12T07:33:13.543

0

Change it to below :-

SELECT DISTINCT fieldnames FROM items WHERE item_section='sp_500' GROUP BY source ORDER BY item_id DESC LIMIT 3

Here in DISTINCT you have to specify field names.

edited Mar 12 '12 at 07:33

answered Mar 12 '12 at 07:20

Milap

6,915
8
26
46

score 0 · Answer 5 · edited Mar 12 '12 at 07:56

0

You cannot combine select * and group by.
If you just need to select unique rows, use

SELECT DISTINCT item_id, source 
FROM items  
WHERE item_section='sp_500'  
ORDER BY item_id DESC`

edited Mar 12 '12 at 07:56

Aliaksei Kliuchnikau

13,589
4
59
72

answered Mar 12 '12 at 07:21

Vijay EK

184
4

this isn't working because i have multiple columns and something is fking up – MaxYarmolinsky Mar 12 '12 at 08:05

score 0 · Answer 6 · edited Mar 12 '12 at 07:34

0

Use this

SELECT * FROM items 
WHERE item_section='sp_500'  
GROUP BY source 
ORDER BY item_id DESC LIMIT 3

Gudluck!!

edited Mar 12 '12 at 07:34

Vishwanath Dalvi

35,388
41
123
155

answered Mar 12 '12 at 07:28

joni_demon

656
6
12

Well,what would be the right order it is ascending or descending?maybe you need to change the desc to asc or add more column name after the first column name in order by,just separate it by a comma "," – joni_demon Mar 12 '12 at 08:10

score 0 · Answer 7 · answered Mar 12 '12 at 08:23

change the query

SELECT *
FROM items
WHERE item_section='sp_500'
GROUP BY source
ORDER BY item_id DESC
LIMIT 3

if no results are there then try to remove the where clause or change the value of item_section you may be getting no output for this item_section

hope this helps

MySQL query: remove duplicates from ordered list

7 Answers7

Why?