how to select top 5 records per category.

Question

Post reply

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:-)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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 · Answer 4

Digs

SSCertifiable

Points: 7333

April 11, 2013 at 3:28 pm

#1605793

Thanks...