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 - 2009-02-06 : 21:16:53
|
| This the table I am using. ,[ID] ,[SCORE] ,[DATE] ,[ASSESSED_BY_ID] ,[CONFIRMED_BY] ,[POSITION] ,[DEPARTMENT] ,[COMMENT] ,[LOCATION] ,[PROPERTY] ,[SCORE_1] ,[SCORE_2] ,[SCORE_3] ,[SCORE_4] ,[SCORE_5] ,[SCORE_6] ,[SCORE_7] ,[SCORE_8] ,[SCORE_9] ,[DEVIATION] ,[CURRENT_POSITION] ,[STATUS] ,[INVOLVED_ID] ,[REASON] ,[TYPE]This the queryselect * from (select ROW_NUMBER() over(order by Score desc ) as rownum, * from assessments) as myset where rownum >= 1 and rownum <= 38 and position < 850 and department = 800 and location like '%PIT001%' and date > '7/February/2009 00:41:12'this query orders by 'score' and returns no records althought the records are there.This query orders by 'date' and returns the records as it should.select * from (select ROW_NUMBER() over(order by Date desc ) as rownum, * from assessments) as myset where rownum >= 1 and rownum <= 38 and position < 850 and department = 800 and location like '%PIT001%' and date > '7/February/2009 00:54:38' The database was created in sql server 2005 and attached to 2008.Could it be corruption or is there something I can't see.Most appreciate any help.Thanks Squiffy |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-07 : 03:24:38
|
select * from (select ROW_NUMBER() over(order by Score desc ) as rownum, * from assessments where position < 850 and department = 800 and location like '%PIT001%' and date > '7/February/2009 00:41:12') as myset where rownum >= 1 and rownum <= 38
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
Squiffy
Starting Member
8 Posts |
Posted - 2009-02-07 : 05:45:38
|
| Thanks Peso, works like a charm. |
|
|
|
|
|
|
|
|
order by problem