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 |
|
Squiffy
Starting Member
8 Posts |
 Posted - 2008-10-04 : 05:03:23
|
| SQL SERVER 2008 X86VB 2008select * from (select ROW_NUMBER() over(order by ID desc ) as rownum, * from assessments) as myset where rownum >= 1 and rownum <= 38 and position < 850 and property = 1 and department = 800 and date > '3/October/2008 14:52:38' I really need help with this one.The user selects the order by field from a combo.There are 3 records in the table.I'm using the same criteria for all queries except changing the order by field.The weird part is some order bys produce no records some one record and some return all.Can anyone explain why?Thanks in advance |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-04 : 06:42:38
|
| where have you selectively included field for order by in above query? you've just given order by field as ID so it will always be ordering on basis of id.what you want is CASE statement in ORDER BY based on parameter passed to determine column to be ordered. Also are you sure you've data in your table that staifies other conditions ( position < 850 and property = 1 and department = 800 and date > '3/October/2008 14:52:38' ) |
 |
|
|
Squiffy
Starting Member
8 Posts |
Posted - 2008-10-04 : 07:07:23
|
| Thanks for the reply visakh16Yes I do have a case for the order by. What I posted was the compiled query string.All other fields in the record are identical except for a auto ident key index and a few other fields that are not queried.I have tried every possible combination of cursor, lock and location.Interestingly, different combinations bring different results.I.e. order by 'date' using client returned all records. Using server no records were returned.I'm going to re-start my computer now as this is my last resort.I will post if this works.Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-04 : 07:19:08
|
quote:
Originally posted by Squiffy
Thanks for the reply visakh16Yes I do have a case for the order by. What I posted was the compiled query string.All other fields in the record are identical except for a auto ident key index and a few other fields that are not queried.I have tried every possible combination of cursor, lock and location.Interestingly, different combinations bring different results.I.e. order by 'date' using client returned all records. Using server no records were returned.I'm going to re-start my computer now as this is my last resort.I will post if this works.Thanks
before you restart the computer can you post the sample data from your table and also the different queries you used? |
|
|
|
|
|
|
SQL bug I think!