views:

535

answers:

2

I'm trying to do some basic paging in MSSQL. The problem I'm having is that I'm sorting the paging on a row that (potentially) has similar values, and the ORDER BY clause is returning "random" results, which doesn't work well.

So for example.

If I have three rows, and I'm sorting them by a "rating", and all of the ratings are = '5' - the rows will seemingly "randomly" order themselves. How do I make it so the rows are showing up in the same order everytime?

I tried ordering it by a datetime that the field was last edited, but the "rating" is sorted in reverse, and again, does not work how i expect it to work.

Here is the SQL I'm using thus far. I know it's sort of confusing without the data so.. any help would be greatful.

SELECT * FROM 
(
  SELECT 
    CAST(grg.defaultthumbid AS VARCHAR) + '_' + 
    CAST(grg.garageid AS VARCHAR) AS imagename,
    (
      SELECT COUNT(imageid) 
      FROM dbo.images im (nolock) 
      WHERE im.garageid = grg.garageid
    ) AS piccount, 
    (
      SELECT COUNT(commentid) 
      FROM dbo.comments cmt (nolock) 
      WHERE cmt.garageid = grg.garageid
    ) AS commentcount,
    grg.GarageID, mk.make, mdl.model, grg.year, 
    typ.type, usr.username, grg.content, 
    grg.rating, grg.DateEdit as DateEdit,
    ROW_NUMBER() OVER (ORDER BY Rating DESC) As RowIndex 
  FROM 
    dbo.garage grg (nolock)
    LEFT JOIN dbo.users (nolock) AS usr ON (grg.userid = usr.userid)
    LEFT JOIN dbo.make (nolock) AS mk ON (grg.makeid = mk.makeid)
    LEFT JOIN dbo.type (nolock) AS typ ON (typ.typeid = mk.typeid)
    LEFT JOIN dbo.model (nolock) AS mdl ON (grg.modelid = mdl.modelid)
  WHERE 
    typ.type = 'Automobile' AND
    grg.defaultthumbid != 0 AND
    usr.username IS NOT NULL
) As QueryResults 
WHERE 
  RowIndex BETWEEN (2 - 1) * 25 + 2 AND 2 * 25
ORDER BY 
  DateEdit DESC
+3  A: 

Try ordering by both, e.g.: ORDER BY Rating DESC, DateEdit ASC

tehvan
Thanks! I have no idea why I didnt' try that in the first place. Cheers.
footose
+1  A: 

The query first numbers the rows by [Rating], and then re-sorts the results by [DateEdit]. Possibly not what you intended. Ordering by [RowIndex] ASC should sort it out.

ROW_NUMBER() OVER (ORDER BY [Rating] DESC) As [RowIndex]
...
ORDER BY [RowIndex]
Anthony Faull
I tried ordering by RowIndex but it wasn't working. I ll give it another shot.
footose