how to select top 5 records per category.

• Digs

  SSCertifiable

  Points: 7333

  More actions

  April 11, 2013 at 2:10 pm

  #272937

  IF OBJECT_ID('tempdb..#priceData') IS NOT NULL DROP TABLE #priceData;

  create table #priceData(symbol varchar(10), quote_date [datetime],close_price [decimal](6,2))

  INSERT INTO #priceData (symbol, quote_date, close_price) VALUES ('AAPL','20091026', 555.75)

  INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091027', 550.97)

  INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091028', 547.87)

  INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091029', 543.01)

  INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091030', 550.00)

  INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091102', 537.08)

  INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091103', 535.48)

  INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091104', 534.80)

  INSERT INTO #priceData (symbol, quote_date, close_price) VALUES ('MSFT','20091026', 555.75)

  INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091027', 550.97)

  INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091028', 547.87)

  INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091029', 543.01)

  INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091030', 523.00)

  INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091102', 537.08)

  INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091103', 535.48)

  INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091104', 585.80)

  CREATE CLUSTERED INDEX ix_goog on #priceData(quote_date)

  SELECT *

  FROM #priceData

  ORDER By Symbol, quote_date

  IF OBJECT_ID('tempdb..#CountInfo') IS NOT NULL DROP TABLE #CountInfo;

  CATEGORY: Symbol

  So how does one SELECT the top 5 records for AAPL and MSFT (in DATE DESC order) for each

  That means 5 records for AAPL and 5 records for MSFT??

  Any ideas:-)

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 11, 2013 at 2:17 pm

  #1605771

  I'd start by looking at the row_number() windowing function. This makes what you need simple, that and a CTE.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  April 11, 2013 at 2:18 pm

  #1605772

  fantastic job providing the DDL and sample data!

  i think you can use row_number() to get what you are after:

  SELECT * FROM (

  SELECT

  ROW_NUMBER() OVER(PARTITION BY symbol ORDER BY quote_date DESC) AS RW,

  #priceData.*

  FROM #priceData

  ) MyAlias WHERe RW <=5

  ORDER By Symbol, quote_date

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 11, 2013 at 2:18 pm

  #1605773

  Lowell (4/11/2013)

  ---

  fantastic job providing the DDL and sample data!

  i think you can use row_number() to get what you are after:

  SELECT * FROM (

  SELECT

  ROW_NUMBER() OVER(PARTITION BY symbol ORDER BY quote_date DESC) AS RW,

  #priceData.*

  FROM #priceData

  ) MyAlias WHERe RW <=5

  ORDER By Symbol, quote_date

  Or a derived table like Lowell is demonstrating above.

• Digs

  SSCertifiable

  Points: 7333

  More actions

  April 11, 2013 at 3:28 pm

  #1605793

  Thanks...