SQL Sorting



IBM's flagship relational database management system

SQL Sorting

Postby maragatham » Thu Nov 18, 2010 12:43 pm

My requirement is to sort rows based on two fields in the table. The fields are "Top_ind" and "date_updated".
The sorting should be in such a way that when ten records are entered today with 5 records with top_ind as "Y" and 5 with top_ind as 'N". All records with top_ind "Y' should be on top and sorted by decending order. ALso, the next non top-indicators should get sorted on descending order and based on date_updated.
And the day before data also should be sorted in a similar fashion.

My query is as below:

SELECT
date_updated
,TOP_IND
FROM news
ORDER BY date(DATE_updated)desc, top_ind DESC;

and the result is

DATE_UPDATED TOP_IND
---------+---------+---------+------
2010-11-17-14.39.22.496128 Y
2010-11-17-14.37.28.432262 Y
2010-11-17-14.32.43.940921 Y
2010-11-17-10.18.40.279265 Y
2010-11-17-10.16.04.435309 Y
2010-11-17-10.17.06.739308 Y
2010-11-17-10.16.49.658154 Y
2010-11-17-07.51.26.927016 Y
2010-11-17-10.16.10.254067 N
2010-11-17-10.20.01.410450 N
2010-11-17-07.52.04.117770 N
2010-11-17-10.19.06.939982 N
2010-11-17-10.20.16.867745 N
2010-11-17-07.51.53.979563 N
2010-11-17-14.33.16.785889 N

If we carefully observe, records with top_ind 'Y' are sorted properly but not the ones with top_ind "N'.

Please help.
maragatham
 
Posts: 9
Joined: Tue Nov 09, 2010 3:08 pm
Has thanked: 0 time
Been thanked: 0 time

Re: SQL Sorting

 

Re: SQL Sorting

Postby GuyC » Thu Nov 18, 2010 1:19 pm

ORDER BY date(DATE_updated) desc, top_ind DESC, time(DATE_updated) desc
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: SQL Sorting

Postby maragatham » Thu Nov 18, 2010 1:44 pm

Thank you. It works
maragatham
 
Posts: 9
Joined: Tue Nov 09, 2010 3:08 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2