sorting query in php

akaki

Hi,
I want to sort from highest to lowest. I've some numbers like this:

17070700097A
07070700085A
16060700098AF
Please observe that 1st 6 digits are dates.
Now I need a query which will accept largest value of last 2 digit of the numbers (just ignore the dates & last upper case later(A,AF..))
Thanks

NogDog

SELECT *, SUBSTRING(col_name, 7, 5) AS sort_str ORDER BY sort_str DESC LIMIT 1

akaki

Hi NogDog,
Thanks for ur good reply. It works.
But ur query is not displaying just the whole numbers, becoz of SUBSTR() function. Itz ignoring first 6 numbers & last letters. But I want to display the whole numbers after sorting. Just look :
Before sort: 28060700045AB
30060700065A
01070700020AB

After sort: 30070700065A
28060700045AB
01070700020AB
I hope itz clear now. Thanks again...

NogDog

Don't output the sort_str value, but rather that of the actual column name. In my example query I retrieved all columns via "*", but you may want to replace that with just the actual columns you want to use:

SELECT `col_name`, SUBSTRING(`col_name`, 7, 5) AS `sort_str`
FROM `table_name` ORDER BY `sort_str` DESC

So in this example you would output the "col_name" values, not the "sort_str" values.

bradgrafelman

If you have the two digits you're trying to sort by at the time you insert data into the table, it might be easier & more efficient to create a second column to store these two digits in. That way, you could index that column and do a simple ORDER BY (that_column) .

Sxooter

You don't have to select it to order by it...

SELECT *, col_name ORDER BY SUBSTRING(col_name, 7, 5) DESC

akaki

Hi NogDog&Others, (I m not using * coz date problem)
Here is my original query:
$q1 = "SELECT Sl_No, Comp_Name, NetOrder_No, CustOrder_No,
DATE_FORMAT(LogIn_Date, '%d-%m-%y') LogIn_Date,
DATE_FORMAT(LogOut_Date, '%d-%m-%y') LogOut_Date, Grand_Total, Status, LabOrder_No, SUBSTRING('LabOrder_No', 7, 5) FROM orders ORDER BY 'LabOrder_No' DESC";
$r1 = mysql_query($q1);

This query is working well, but not 100%. Coz, some troubles after sort like this:
31070700098AB
31050700043A
30070700109A
..... ..... ......
26070700097A
26060700069A
25070700096AB
... .... ....... .....
So, why these are happeng???
Thanks