order by problem

[eduac]

Hi all,

I need help with order by. I cant make this work, because i have 3 fields to sort, and it only sort 2 fields. This is my query:

SELECT ref_prefix,ref,year FROM table ORDER by ref_prefix DESC,ref DESC,year DESC;

The result is:

 ref_prefix |     ref     |  year
------------+-------------+-------
 RS         | RS0054/06   | 2006
 RS         | RS0034/05   | 2005
 BA         | BA4445/05   | 2005
 BA         | BA3201/04   | 2004
 BA         | BA3093/05   | 2005
 BA         | BA2906/04   | 2004

But the correct would be:

 ref_prefix |     ref     |  year
------------+-------------+-------
 RS         | RS0054/06   | 2006
 RS         | RS0034/05   | 2005
 BA         | BA4445/05   | 2005
 BA         | BA3093/05   | 2005
 BA         | BA3201/04   | 2004
 BA         | BA2906/04   | 2004

How can i solve this?

[Drakla]

From your results it looks like you want the year sort to have priority over the reference, so swap them around in the order by clause

[cahva]

ORDER BY year DESC , ref DESC;

Simple as that.. First by year and then by ref. No need to sort by ref_prefix..

[eduac]

yeap, but i need to sort by reference prefix too (BA,RS,AG)...something like:

RS0005/06
RS0006/05
BA0005/05 
BA0001/05  

BA0005/04 
BA0001/04
AG0003/05 
AG0002/05 
AG0001/05 
AG0003/04 

Did you get the problem?

[tsinka]

Hi,

what about

ORDER by ref_prefix DESC,year DESC,ref DESC

Thomas

[eduac]

nothing 🙁

[Roger_Ramjet]

Sort on text data runs from left to right, so BA3201/04 comes before BA3093/05 (when order by is DESC) because BA32 is higher than BA30.

To force the desired order you will have to do as Drakla sez and sort by prefix, then year, then ref.

If need be you can select only part of the ref field and sort by that

"SELECT ref_prefix,ref,year, SUBSTRING(ref, 3, 4) AS s FROM table ORDER by ref_prefix DESC,year DESC, s DESC";

That will extract just the digits from your ref and allow sorting on them.

[tsinka]

Did you solve the problem ?