SQL Distinct While Selecting All Columns

Question

I've tried Googling this but couldn't find anything. When I run the query below, it outputs 4 identical rows. So I want to use the DISTINCT keyword to eliminate the duplicates. But I get an SQL error when I run the query. Remove the DISTINCT and it works fine.

SELECT DISTINCT list.`id`, * 
FROM `listings` list 
INNER JOIN `selections` sel
ON list.`id` = sel.`lid` 
WHERE 1 AND `activity` = 'running' 
AND ( 0 OR (sel.`parent` = 
'1') OR (sel.`parent` = '2') )

distinct like that applies to ALL of the fields you're selecting. it's not a per-field distinct. — Marc B
– Marc B, Commented Jul 30, 2014 at 14:58
possible duplicate of SQL - Select distinct but return all columns? — BraedenP
– BraedenP, Commented Jul 30, 2014 at 14:58
In addition to the above. Your sql can be simplified. WHERE activity = 'running' AND sel.parent in ('1','2') — crthompson
– crthompson, Commented Jul 30, 2014 at 14:58

Strawberry · Accepted Answer · 2014-07-30 15:07:04Z

1

SELECT DISTINCT columns.you
              , actually.want
           FROM listings l
           JOIN selections s
             ON l.id = s.lid 
          WHERE activity = 'running' 
            AND s.parent IN(1,2)

edited Jul 30, 2014 at 15:07

answered Jul 30, 2014 at 15:01

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

The Hawk Over a year ago

Where do I put the DISTINCT then? SELECT DISTINCT id, title, address ?

John Ruddell Over a year ago

@TheHawk DISTINCT is put first.. it applies to all of the fields.. you shouldn't select a column then * like you did in your query. thats not valid syntax for MySQL regardless.

crthompson Over a year ago

@JohnRuddell hit the nail. The misunderstanding is thinking that distinct id, * only affects id. It affects the * as well.

John Ruddell Over a year ago

@paqogomez DISTINCT id, * is not valid syntax in MySQL, so if the OP is using MySQL that wouldn't work. unless it was specified... something like SELECT DISTINCT s.id, l.*

John Ruddell Over a year ago

as is a lot of Strawberries comments ;) many are helpful though

Collectives™ on Stack Overflow

SQL Distinct While Selecting All Columns

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related