asked on

SQL Sorting

SQL question: Suppose I have a datagrid with email messages... I use a simple sql statement such as select * from Messages where id = 5; or something like that. Then suppose I have at the leftmost column checkboxes that indicate something like messages marked for deletion. Now you have a datagrid with messages, some marked, some not marked. Now suppose on the leftmost column, I then wanted to create a sorting mechanism - where you can sort the rows that are checked and make them show up at the top. This is basically what I'm trying to do.

I have the IDs of the rows that are marked, but because the datagrid spans many pages I want the user to be able to sort by everything he selected (the ones selected are in a session variable) - any idea how to sort them without having to resort to using asp.net DataTable, DataRow, etc. functionalities? I would definitely rather do this by SQL than by chopping up the datagrid via C# and putting it back together again.

Any ideas? Thanks so much in advance!

Naveen Kumar

select col1, col2, col3...
from table1
where ...
order by decode(checked_flag,'Y',1,2); -- this will make sure checked rows to come at the top;

I have just given some sample sql to make you understand...

jeremyduj

ASKER

Thanks - but how does the decode function work? I basically have a datagrid with ids 1-10 and if the user "checks" ids 5, 6, 7 and then clicks sort, it should put 5, 6, 7, 1, 2, 3, 4, 8, 9, 10

How could I specify to sql the ids that are supposed to come first - I was thinking of using UNION but I think it has become outdated

lahousden

DECODE is Oracle not SQL Server. For situations where you find yourself reaching for "DECODE" you will need to use CASE in SQL Server, e.g. to adapt na_kum_c's example::

select col1, col2, col3...
from table1
where ...
order by case
when 'Y' = Checked_flag
then 1
else 2
end -- this will make sure checked rows to come at the top;

jeremyduj

ASKER

basically if I knew that ids 5, 6, 7 were checked is there a way to force them at the top of the full results list? There is nothing in the db that can discern this - it would have to be inputted.

awking00

Whether or not you use decode (Oracle only) or case (many dbms), you need to add id to your order by statement to avoid getting results like 6,7,5,3,2,1,8,10, 9 where user "checked" ids 5, 6, and 7 as in your example -
order by <decode or case statement>, id;

ASKER CERTIFIED SOLUTION

lahousden

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Naveen Kumar

in addition if your front end/web control has the append feature,

control.populate('select id, col1,... from table1 where id in ( 5,6,7 ) order by id);
control.append('select id, col1,... from table1 where id not in ( 5,6,7 ) order by id );

the above is just a pseudocode and i am not an expert in front end/web technologies. The above
is to first display/populate your front end control/grid with selected values and then we are
appending records to the same control/grid which are not selected.