MySQL Query with DISTINCT and ORDER BY

Question

Here is my query:

$result = $mysqli->query('SELECT DISTINCT SKU_SIZE_PART1 
                          FROM SKU_DATA 
                          ORDER BY SKU_SIZE_PART1 DESC');

Now this works perfect for SKU_SIZE_PART1 but I have 2 more parts that I need to grab. Now when I put a comma and do this: 'SKU_SIZE_PART1, SKU_SIZE_PART2, SKU_SIZE_PART3' then the DISTINCT doesn't work and I get a ton of duplicates, and then I'm not sure how to order the query so that all of them are ordered by the size and DESC.

Does that make sense? I could just duplicate that query 2 more times and have 3 separate queries but I would like to know how to accomplish this with just one.

Possible dupe of http://stackoverflow.com/questions/1181374/mysql-select-statement-distinct-for-multiple-columns — Savino Sguera, Oct 04 '11 at 19:49
@DaOgre, Why would you? Distinct is something you apply on a result record, not per field. — GolezTrol, Oct 04 '11 at 19:50
`DISTINCT` is applied to all columns, so only duplicates where the value of the 3 columns are identical will be filtered out. On ORDER BY, you can add as many columns as you wish. — bfavaretto, Oct 04 '11 at 19:51
Keep in mind that `DISTINCT` is applied to the entire grouping of columns as a set, not to each column individually. — Joe Stefanelli, Oct 04 '11 at 19:52

score 2 · Accepted Answer · edited Oct 05 '11 at 07:38

2

I'm not positive that I understand what you're trying to do, but it sounds like you might actually want something like this:

SELECT SKU_SIZE_PART1 AS SKU_SIZE_PART
  FROM SKU_DATA
UNION
SELECT SKU_SIZE_PART2 AS SKU_SIZE_PART
  FROM SKU_DATA
UNION
SELECT SKU_SIZE_PART3 AS SKU_SIZE_PART
  FROM SKU_DATA
 ORDER BY SKU_SIZE_PART DESC

which will return all distinct SKU_SIZE_PART1/2/3 values in a single column, rather than all distinct (SKU_SIZE_PART1, SKU_SIZE_PART2, SKU_SIZE_PART3) triads in three columns.

edited Oct 05 '11 at 07:38

Johan

74,508
24
191
319

answered Oct 04 '11 at 20:00

ruakh

175,680
26
273
307

@Johan: No, it won't; UNION implies DISTINCT. (Maybe you're thinking of UNION ALL?) – ruakh Oct 04 '11 at 20:11
@Johan: I'm sorry, but you are simply mistaken. UNION does not behave the way that you think it does. (At least in the RDBMSes I'm familiar with. That includes MySQL, which is apparently what Drew is using.) I tested my query; I invite you to do the same. – ruakh Oct 05 '11 at 04:20

score 1 · Answer 2 · answered Oct 04 '11 at 19:54

1

After reading your question several times, I figured this might be what you are looking for:

SELECT SKU_SIZE_PART1 AS ssp
FROM SKU_DATA 
UNION
SELECT SKU_SIZE_PART2 AS ssp
FROM SKU_DATA 
UNION
SELECT SKU_SIZE_PART3 AS ssp
FROM SKU_DATA 
ORDER BY ssp DESC

answered Oct 04 '11 at 19:54

bfavaretto

71,580
16
111
150

This will generate heaps of duplicates, you meant `select distinct ... union select distinct ... union select distinct` right? – Johan Oct 04 '11 at 20:08
I understand that is not necessary. `UNION` is implicitly `DISTINCT` (unlike `UNION ALL`). See [docs](http://dev.mysql.com/doc/refman/5.0/en/union.html) – bfavaretto Oct 04 '11 at 20:10
Distinct between the selects, not distinct inside the selects. – Johan Oct 04 '11 at 20:51

score 1 · Answer 3 · answered Oct 04 '11 at 20:02

SELECT d.sku_size_part1, d.sku_size_part2, d.sku_size_part3 
FROM sku_data d
WHERE d.id IN (
  SELECT s.id   <<--- replace `id` with the real primary-key for table `sku_data`
  FROM sku_data s 
  GROUP BY s.sku_size_part1)
ORDER BY d.sku_size_part1 DESC

Note that this will select rows more or less at random.
Although all sku_size_parts will be from the same row, lots of values will be hidden.
If you want to make the query stable, you need to add a having clause in the inner subselect.

Something like this:

SELECT d.sku_size_part1, d.sku_size_part2, d.sku_size_part3 
FROM sku_data d
WHERE d.id IN (
  SELECT s.id   <<--- replace `id` with the real primary-key for table `sku_data`
  FROM sku_data s 
  GROUP BY s.sku_size_part1
  HAVING s.sku_size_part2 = MIN(s.sku_size_part2) 
     AND s.sku_size_part3 = MIN(s.sku_size_part3))
ORDER BY d.sku_size_part1 DESC

Either that or you want @bfavaretto's UNION variant.

score -1 · Answer 4 · answered Oct 04 '11 at 19:57

DISTINCT selects a distinct set of rows, not columns... the assumption/problem here is how to condense multiple columns. If you had the following table

  sku1 | sku2 | sku3
 ---------------------
    a  |   a  |  b
    b  |   b  |  b

Telling it to select destinct would return both rows because none of them are distinct, you couldn't just remove the third column because then the row data would be inconsistent. If you want everything in one table you can do this with subqueries.

 SELECT (SELECT DISTINCT SKU_SIZE_PART1 FROM SKU_DATA ORDER BY SKU_SIZE_PART1 DESC)
 as part1, (SELECT DISTINCT SKU_SIZE_PART2 FROM SKU_DATA ORDER BY SKU_SIZE_PART2 DESC)      
 as part2, (SELECT DISTINCT SKU_SIZE_PART3 FROM SKU_DATA ORDER BY SKU_SIZE_PART1 DESC)  
 as part3 FROM SKU_DATA

You can read up a little on how DISTINCT works to see why you can't just do SELECT DISTINCT SKU_SIZE_PART1, PART2, PART3. Somewhere like This Link

-1, this will mix and match random `sku_size_part`s together that are not even from the same row. Very poor code. — Johan, Oct 04 '11 at 20:03

MySQL Query with DISTINCT and ORDER BY

4 Answers4