Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Sorting

Posted on 2007-07-30
7
Medium Priority
?
229 Views
Last Modified: 2013-12-07
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!
0
Comment
Question by:jeremyduj
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 19593064
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...
0
 
LVL 1

Author Comment

by:jeremyduj
ID: 19593200
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
0
 
LVL 10

Expert Comment

by:lahousden
ID: 19593837
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;
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:jeremyduj
ID: 19593876
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.
0
 
LVL 32

Expert Comment

by:awking00
ID: 19593937
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;
0
 
LVL 10

Accepted Solution

by:
lahousden earned 2000 total points
ID: 19594020
If 5, 6, 7 are always checked then:

select col1, col2, col3...
from table1
where ...
order by case
                when id in (5, 6, 7)
                then 1
                else 2
               end, id
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 19594042
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.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question