complicate query 1

[snaper22]

Hi!

I have 3 columns in my table, t1, t2, t3
There are integer values from 0 - 999 in every column.

Now I wanna sort by all 3 columns like that:

t1 t2 t3
==========
000 034 04
200 204 24
201 002 02

I used CONCAT, but then the 3 row would be placed wrong, because the CONCAT generates: 20122.

How can I order that? Thx for any tips and comments!

holgerp

 

[sleipnir214]

That's because in order to concatenate three integers, MySQL has to convert them all to strings. It will convert their values, which will trim all leading zeros.

Treat the three columns as the 3-digit groupings of a single number with implied leading zeros in each group. Alphabetical and numerical order should be the same.

Try:
select * from foo order by (t1 * 1000000) + (t2 * 1000) + t3 Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[snaper22]

Wonderful, thx!

 

[r937]

what's wrong with

order by t1, t2, t3

maybe i'm missing something in your situation, holgerp, but that's the best way


rudy

http://r937.com/