Display top 20 items

• Somebody

  SSC Eights!

  Points: 928

  More actions

  March 21, 2007 at 12:20 pm

  #369920

  Hello everyone,

  I am working on a report that requires to display either the top 20 or the bottom 20 items, ranked by price.  I am trying to use the rownumber() but it does not work.  It seems to me that  rownumber() returns the total rows of the whole  dataset or a group not the row number of each row.

  Would any of you please help me on this?  So basically I just wanted to display only 20 items at a time in my table.

  Thanks!

• Aaron Ingold

  SSCrazy Eights

  Points: 9070

  More actions

  March 21, 2007 at 12:53 pm

  #696232

  SELECT TOP 20 * FROM items ORDER BY Price ASC

  SELECT TOP 20 * FROM items ORDER BY Price DESC

  If you're doing something like a parameterized report where you can only have one query, you might be able to do something like:

  SELECT TOP 20 * FROM items ORDER BY CASE WHEN @TopBottom = 'B' THEN -1*Price ELSE Price END DESC

  And have @TopBottom be a user-entered parameter for whether they'd like the top 20 or bottom 20... I think that would work.