Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
xenticateam
Starting Member
2 Posts |
 Posted - 2008-03-11 : 16:46:32
|
| Hi all, wonder if someone can help. I have developed a db search facility in .net but the client wants the results to display on the results page in random order. The problem is that the results are on a separate page which has paging and a sort facility. What I need to do is use the dataset session but I can't use the same session on the results page onload as all it does is display in random order each time I use the paging links, which means when I use the page navigation links (next/previous), I get different results each time (which is of course what I would expect to get). What I have tried is looping through the dataset and creating a variable containing the order of the record ids found, then I create another sql select query using this string like:-"select id, name, address from tbl_customers where tbl_customers.id IN (" + result_id_order + ")" result_id_order being the variable created from the first random order query from the search page (something like 16,32,4,9). This doesn't display in the same order though, it really just does an order by id (4,9,16,32). Is there a way to get results to order in a specific order in just the one query? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-03-12 : 02:24:34
|
To get random order:SELECT ... FROM ... ORDER BY NEWID()To get specfic order:SELECT ... FROM ... ORDER BY CASE Col1WHEN 1 THEN 9WHEN 2 THEN 2WHEN 5 THEN 1WHEN 4 THEN 3ELSE 10END
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
xenticateam
Starting Member
2 Posts |
Posted - 2008-03-12 : 05:37:26
|
| Fantastic! I haven't used that before it's perfect, many thanks. |
|
|
|
|
|
|
|
|
Random ordering problem