SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 How to query first, last, min, max?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ryan_hunter1200
Starting Member

3 Posts

Posted - 09/01/2013 :  18:37:52  Show Profile  Reply with Quote
I want to take a bunch of stock trade prices and determine the first, last, high, and low prices.

example:

SELECT FIRST(Price), LAST(Price), MAX(Price), MIN(Price) FROM my_table_of_stock_trades WHERE Symbol='IBM' and TradeTime > '01/01/2013 12:00:00' AND TradeTime < '01/01/2013 12:15:00'

Right now I am having to break this up into multiple queries to get the First, Last, and High/Low (SELECT TOP 1 Price WHERE ... ORDER BY TradeTime) and then flip it the other way for the last.

It is not a big deal if I am querying this once or twice, but I want to query this thousands of times a minute and it is just too slow.

Ideally, I would like to eventually do a GROUP BY Symbol and get data on every symbol for the time period:

SELECT Symbol, FIRST(Price), LAST(Price), MAX(Price), MIN(Price) FROM my_table_of_stock_trades WHERE TradeTime > '01/01/2013 12:00:00' AND TradeTime < '01/01/2013 12:15:00' GROUP BY Symbol

Can you please point me in the right direction to do this as quickly and efficiently as possible? There is no concept of FIRST/LAST in SQL Server 2008. I am prepared to install SQL 2012 if there is a better way to do this? If not, what do you think is the fastest way to do this with the GROUP BY?

khtan
In (Som, Ni, Yak)

Singapore
17638 Posts

Posted - 09/01/2013 :  21:57:08  Show Profile  Reply with Quote

; with cte as
(
	select	*, 
		rn = row_number() over (partition by Symbol order by TradeTime), 
		cnt = count(*) over (partition by Symbol)
	from	my_table_of_stock_trades
	WHERE 	Symbol	= 'IBM' 
	and 	TradeTime > '01/01/2013 12:00:00' 
	AND 	TradeTime < '01/01/2013 12:15:00'
)
SELECT 	[FIRST] = max(case when rn = 1 then Price end),
	[LAST]	= max(case when rn = cnt then Price end),
	[MIN]	= min(Price),
	[MAX]	= max(Price)
FROM 	cte



KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/02/2013 :  09:55:13  Show Profile  Reply with Quote
2012 has FIRST_VALUE, LAST_VALUE etc so you may use this too

SELECT 	FIRST_VALUE(Price) OVER (partition by Symbol order by TradeTime) AS [FIRST],
	FIRST_VALUE(Price) OVER (partition by Symbol order by TradeTime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS [LAST],
	min(Price) OVER (partition by Symbol) AS [MIN],
	max(Price) OVER (partition by Symbol) AS [MAX],
....
FROM  my_table_of_stock_trades
	WHERE 	Symbol	= 'IBM' 
	and 	TradeTime > '01/01/2013 12:00:00' 
	AND 	TradeTime < '01/01/2013 12:15:00'	


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ryan_hunter1200
Starting Member

3 Posts

Posted - 09/02/2013 :  12:32:16  Show Profile  Reply with Quote
khtan,

thank you for the sql query. this works very well!

I am trying to modify this for my GROUP BY exchange (so I group by symbol -- so i do this for every symbol in the table for the time period and not just 'IBM') but I cannot figure it out.

SELECT Symbol, FIRST(Price), LAST(Price), MAX(Price), MIN(Price) FROM my_table_of_stock_trades WHERE TradeTime > '01/01/2013 12:00:00' AND TradeTime < '01/01/2013 12:15:00' GROUP BY Symbol

how do I use your approach for the group by?

The Row_number() OVER (Partition ..) is something i saw when I googled this issue but i have to admit it is very confusing. with your working example, I find this is very helpful, so thank you very much.

Can you kindly help with a GROUP BY solution? the only solution I have right now is to use a CURSOR over every DISTINCT(Symbol) during the time period and run your query. But this is much too expensive. If I can modify your query to return first/last/high/low for each distinct symbol during the time period that would be precise. thank you.


quote:
Originally posted by khtan


; with cte as
(
	select	*, 
		rn = row_number() over (partition by Symbol order by TradeTime), 
		cnt = count(*) over (partition by Symbol)
	from	my_table_of_stock_trades
	WHERE 	Symbol	= 'IBM' 
	and 	TradeTime > '01/01/2013 12:00:00' 
	AND 	TradeTime < '01/01/2013 12:15:00'
)
SELECT 	[FIRST] = max(case when rn = 1 then Price end),
	[LAST]	= max(case when rn = cnt then Price end),
	[MIN]	= min(Price),
	[MAX]	= max(Price)
FROM 	cte



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/02/2013 :  13:31:23  Show Profile  Reply with Quote
quote:
Originally posted by ryan_hunter1200

khtan,

thank you for the sql query. this works very well!

I am trying to modify this for my GROUP BY exchange (so I group by symbol -- so i do this for every symbol in the table for the time period and not just 'IBM') but I cannot figure it out.

SELECT Symbol, FIRST(Price), LAST(Price), MAX(Price), MIN(Price) FROM my_table_of_stock_trades WHERE TradeTime > '01/01/2013 12:00:00' AND TradeTime < '01/01/2013 12:15:00' GROUP BY Symbol

how do I use your approach for the group by?

The Row_number() OVER (Partition ..) is something i saw when I googled this issue but i have to admit it is very confusing. with your working example, I find this is very helpful, so thank you very much.

Can you kindly help with a GROUP BY solution? the only solution I have right now is to use a CURSOR over every DISTINCT(Symbol) during the time period and run your query. But this is much too expensive. If I can modify your query to return first/last/high/low for each distinct symbol during the time period that would be precise. thank you.


quote:
Originally posted by khtan


; with cte as
(
	select	*, 
		rn = row_number() over (partition by Symbol order by TradeTime), 
		cnt = count(*) over (partition by Symbol)
	from	my_table_of_stock_trades
	WHERE 	Symbol	= 'IBM' 
	and 	TradeTime > '01/01/2013 12:00:00' 
	AND 	TradeTime < '01/01/2013 12:15:00'
)
SELECT 	[FIRST] = max(case when rn = 1 then Price end),
	[LAST]	= max(case when rn = cnt then Price end),
	[MIN]	= min(Price),
	[MAX]	= max(Price)
FROM 	cte






did you try my suggestion? is it any better?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ryan_hunter1200
Starting Member

3 Posts

Posted - 09/02/2013 :  16:46:55  Show Profile  Reply with Quote
Hi Visakh16,

I did not try your solution because I do not have SQL 2012 installed. I obviously would prefer to stick with sql2008 if I can (assuming 2012 has some performance benefit for this type of query).

Do you think the other solution can be tweaked to work with a GROUP BY or do I need to upgrade to 2012 to do what I am looking for?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/03/2013 :  09:29:05  Show Profile  Reply with Quote
quote:
Originally posted by ryan_hunter1200

Hi Visakh16,

I did not try your solution because I do not have SQL 2012 installed. I obviously would prefer to stick with sql2008 if I can (assuming 2012 has some performance benefit for this type of query).

Do you think the other solution can be tweaked to work with a GROUP BY or do I need to upgrade to 2012 to do what I am looking for?


I thought you're on sql 2012 as you posted this on SQL 2012 forum
I missed the part in your post where you specified you're on SQL 2008.

for your case this is enough i guess.

; with cte as
(
	select	*, 
		rn = row_number() over (partition by Symbol order by TradeTime), 
		cnt = count(*) over (partition by Symbol)
	from	my_table_of_stock_trades
	WHERE 	Symbol	= 'IBM' 
	and 	TradeTime > '01/01/2013 12:00:00' 
	AND 	TradeTime < '01/01/2013 12:15:00'
)
SELECT 	Symbol,
        [FIRST] = max(case when rn = 1 then Price end),
	[LAST]	= max(case when rn = cnt then Price end),
	[MIN]	= min(Price),
	[MAX]	= max(Price)
FROM 	cte
GROUP BY Symbol


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000