homepage Welcome to WebmasterWorld Guest from 54.161.175.231
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Ordering twice with group by
optik




msg:4196801
 2:55 pm on Sep 5, 2010 (gmt 0)

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




msg:4196803
 3:26 pm on Sep 5, 2010 (gmt 0)

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




msg:4196804
 3:26 pm on Sep 5, 2010 (gmt 0)

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




msg:4196811
 3:49 pm on Sep 5, 2010 (gmt 0)

adding title doesn't seem to change the order

rocknbil




msg:4196818
 4:40 pm on Sep 5, 2010 (gmt 0)

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




msg:4196838
 5:22 pm on Sep 5, 2010 (gmt 0)

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




msg:4196869
 7:19 pm on Sep 5, 2010 (gmt 0)

"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




msg:4196900
 8:18 pm on Sep 5, 2010 (gmt 0)

sorry that didn't work

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved