Ordering twice with group by

[optik]

I'm selecting some results using GROUP BY which are order by one field, that works fine but I then need that result order a second time by another field.

so GROUP BY inventory_code ORDER BY variation_number DESC

this gives me one result for each unique inventory code and the first variation of that inventory item is selected.

What I now need is for all the result for every inventory to be sorted alphabetically by a third field, called title.

[edited by: optik at 3:48 pm (utc) on Sep 5, 2010]

[syber]

Unless I'm missing something, wouldn't this be:

SELECT inventory_code, <aggregate functions>
FROM table
GROUP BY inventory_code
ORDER BY variation_number DESC, title

[syber]

Unless I'm missing something, wouldn't this be:

SELECT inventory_code, <aggregate functions>
FROM table
GROUP BY inventory_code
ORDER BY variation_number DESC, title

[optik]

adding title doesn't seem to change the order

[rocknbil]

Why do you need group by?

this gives me one result for each unique inventory code and the first variation of that inventory item is selected.

With one unique result, there's nothing else to order by. :-)

Run this complete test, you'll see it works.

create table test (id int(11) primary key auto_increment, title varchar(255) not null, inventory_code varchar(6) not null default '', variation_number int(11) not null);

insert into test (title,inventory_code,variation_number) values ('Zebra','Z104',12345);
insert into test (title,inventory_code,variation_number) values ('Zebra','Z101',12345);
insert into test (title,inventory_code,variation_number) values ('Zebra','Z101',12311);
insert into test (title,inventory_code,variation_number) values ('Zebra','Z101',12310);
insert into test (title,inventory_code,variation_number) values ('Fragrance','F220',59681);
insert into test (title,inventory_code,variation_number) values ('Fragrance','F189',58999);
insert into test (title,inventory_code,variation_number) values ('Fragrance','F220',58123);
insert into test (title,inventory_code,variation_number) values ('Fragrance','F220',45123);

select * from test order by inventory_code asc, variation_number desc, title asc;

+----+-----------+----------------+------------------+ 
| id | title | inventory_code | variation_number | 
+----+-----------+----------------+------------------+ 
| 6 | Fragrance | F189...........|............58999 | 
| 5 | Fragrance | F220...........|............59681 | 
| 7 | Fragrance | F220...........|............58123 | 
| 8 | Fragrance | F220...........|............45123 | 
| 2 | Zebra.....| Z101...........|............12345 | 
| 3 | Zebra.....| Z101...........|............12311 | 
| 4 | Zebra.....| Z101...........|............12310 | 
| 1 | Zebra.....| Z104...........|............12345 | 
+----+-----------+----------------+------------------+ 

Since the inventory codes begin with the same letter as the title, the sorting still appears to sort by title. You'll get different results if this is not the case, but you can see . . . group by is not needed.

[optik]

I'm using GROUP BY to retrieve only one entry for each unique inventory code, the entry being picked is currently decided by the variation number ORDER BY statement which is fine that gives me results of a list of unique inventory codes where the entry with the lowest variation number is selected.

What I now need to do is order the results I get back by the title field.

GROUP BY will give me 4 results in your example above, if I don't use GROUP BY I'll get the whole list which is not what I need.

[syber]

"adding title doesn't seem to change the order"

Looking at your example you probably want the order by to be:

ORDER BY inventory_code, title, variation_number DESC

[optik]

sorry that didn't work