Thursday, April 11, 2013

[SQL Server] how to select top 5 records per category.

[SQL Server] how to select top 5 records per category.

how to select top 5 records per category.

Posted: 11 Apr 2013 07:10 AM PDT

[code="sql"]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_dateIF OBJECT_ID('tempdb..#CountInfo') IS NOT NULL DROP TABLE #CountInfo;[/code]CATEGORY: SymbolSo how does one SELECT the top 5 records for AAPL and MSFT (in DATE DESC order) for eachThat means 5 records for AAPL and 5 records for MSFT??Any ideas:-)

How calc change in value over several records for different categories

Posted: 10 Apr 2013 03:35 PM PDT

Test data[code="sql"]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_dateIF OBJECT_ID('tempdb..#CountInfo') IS NOT NULL DROP TABLE #CountInfo;[/code]I am trying to get this outcome...AAPL -0.68 1 day Change -15.2 3 day change -16.17 6 day changeMSFT 50.32 1 day Change 62.8 3 day change 34.83 6 day change1 day change is AAPL is -0.68 is [2091104] 534.80 less [20091103] 535.48 = -0.683 day change is AAPL is -15.2 is [2091104] 534.80 less [20091030] 550.00 = -15.2Any ideas how to do this?:-)

Count how many times contains a single value

Posted: 11 Apr 2013 06:13 AM PDT

Hello everyone, I need your help.This is my table:[code]+------+----+-----------------------+| Att | q | x |+------+----+-----------------------+| Att | 9 | 5,4,4,4,5,3,5,4,5 || Lav | 3 | 5,5,3 || Pred | 11 | 4,5,5,5,5,5,5,4,5,5,4 || RGdM | 3 | 5,3,5 |+------+----+-----------------------+[/code]I need count how many times contains a single value, I mean:The row *Att* contains 4 times the value 5 and 4 times the value 4 and 1 time the value 3, etc.Can you help me?Thank you in advance.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog