Return Ranked Results?

• jamesearich

  Newbie

  Points: 2

  More actions

  February 8, 2008 at 2:58 pm

  #183762

  Hi Everyone,

  I need some help from a SQL guru! I have a table that consists of 10,000's of records. I need to return the most recent entry by time and code for that day. I know I can get what I want using MS SQL 2005 ROW_NUMBER OVER() but I have no idea how I can do this in MS SQL 2000 e.g.

  DATA:

  2008-02-01 10:00AM ASX1

  2008-02-01 10:30AM ASX1

  2008-02-02 10:00AM ASX1

  2008-02-02 10:30AM ASX1

  2008-02-03 10:00AM ASX1

  2008-02-03 10:30AM ASX1

  2008-02-04 10:00AM ASX2

  2008-02-04 10:30AM ASX2

  EXPECTED RESULTS FOR ASX1:

  2008-02-03 10:30AM ASX1

  2008-02-02 10:30AM ASX1

  2008-02-01 10:30AM ASX1

  I would like to rank each result in a nested query and then only select the top n.

  Any help or a solution would be much appreciated!

  Thanks in advance!

• John Rowan

  SSC Guru

  Points: 56440

  More actions

  February 8, 2008 at 4:12 pm

  #776647

  This should work for you.

  DECLARE @TableVar TABLE (MyDate datetime, Col2 varchar(10))

  INSERT INTO @TableVar

  SELECT '2008-02-01 10:00AM','ASX1' UNION ALL

  SELECT '2008-02-01 10:30AM','ASX1' UNION ALL

  SELECT '2008-02-02 10:00AM','ASX1' UNION ALL

  SELECT '2008-02-02 10:30AM','ASX1' UNION ALL

  SELECT '2008-02-03 10:00AM','ASX1' UNION ALL

  SELECT '2008-02-03 10:30AM','ASX1' UNION ALL

  SELECT '2008-02-04 10:00AM','ASX2' UNION ALL

  SELECT '2008-02-04 10:30AM','ASX2'

  SELECT MAX(MyDate) as MostRecent, Col2

  FROM @TableVar

  GROUP BY Col2, CONVERT(varchar, MyDate,101)

  ORDER BY MAX(MyDate) DESC

  John Rowan

  ======================================================
  ======================================================
  Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

• jamesearich

  Newbie

  Points: 2

  More actions

  February 8, 2008 at 10:54 pm

  #776677

  Thanks very much John. Exactly what I was trying to do!