select top 1 * returns diffrent recordset each time

Question

Hi all,

In my application I use SELECT TOP 12 * clause to select top 12 records from database and show it to user. In another case I have to show the same result one by one. So I use SELECT TOP 1 * clause,rest of the query is same. I used Sql row_number() function to select items one by on serially.

The problem is SELECT TOP 1 * doesn't return me same row as I get in SELECT TOP 12 *. Also the result set of SELECT TOP 12 * get changed each time I execute the query.

Can anybody explain me why the result is not get same in SELECT TOP 12 * and SELECT TOP 1 *.

FYI: here is my sql

select distinct top 1 *  from( 
select row_number() over (  ORDER BY Ratings desc  ) as Row, * from( 
SELECT vw.IsHide, vw.UpdateDate, vw.UserID, vw.UploadPath, vw.MediaUploadID, vw.Ratings, vw.Caption, vw.UserName, vw.BirthYear, vw.BirthDay, vw.BirthMonth, vw.Gender, vw.CityProvince, vw.Approved 
FROM VW_Media as vw ,Users as u WITH(NOLOCk) 
WHERE vw.IsHide='false' and 
GenderNVID=5 and 
vw.UserID=u.UserID and 
vw.UserID not in(205092) and 
vw.UploadTypeNVID=1106 and 
vw.IsDeleted='false' and 
vw.Approved = 1 and 
u.HideProfile=0 and 
u.StatusNVID=126 and 
vw.UserID not in(Select BlockedToUserID from BlockList WITH(NOLOCk) where UserID=205092) a) totalres where row >0

Thanks in Advance

Sachin

Answer 1

When you use SELECT TOP, you must use also the ORDER BY clause to avoid different results every time.

Answer 2

For performance resons, the database is free to return the records in any order it likes if you don't specify any ordering.

So, you always have to specify in which order you want the records, if you want them in any specific order.

Up to some version of SQL Server (7 IIRC) the natural order of the table was preserved in the result if you didn't specify any ordering, but this feature was removed in later versions.