Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to select top 5 records per category. Expand / Collapse
Author
Message
Posted Thursday, April 11, 2013 2:10 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 4:00 PM
Points: 420, Visits: 560


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
Post #1441472
Posted Thursday, April 11, 2013 2:17 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:19 PM
Points: 23,243, Visits: 31,938
I'd start by looking at the row_number() windowing function. This makes what you need simple, that and a CTE.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1441473
Posted Thursday, April 11, 2013 2:18 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 12,906, Visits: 31,984
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1441474
Posted Thursday, April 11, 2013 2:18 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:19 PM
Points: 23,243, Visits: 31,938
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1441475
Posted Thursday, April 11, 2013 3:28 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 4:00 PM
Points: 420, Visits: 560
Thanks...
Post #1441500
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse