Have I written Hokey SQL?(Yes - now closed - thax)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Have I written Hokey SQL?(Yes - now closed - thax)

Author

Topic

Julien.Crawford
Starting Member

21 Posts

Posted - 2007-10-17 : 20:49:27

I have a large table (18million rows)
the columns of interest are stockId, dateStamp, closePrice
I needed to get the stockId, closePrice pairs that have the maximum date per stockId. (Primary key on stockid, dateStamp)
My first try hit the table twice - once to get the stockId, max(dateStamp) group by stockId then again using this pair to lookup the closePrice. (note the datetime is really just a date)

I wanted to avoid this double hit. I tinkered with 'case when' and 'having', but all were problematic (I couldn't get my head around it).
So I did this:
select x.stockId
, convert(float, substring( convert(char(8), x.dateStamp, 112) + convert(char(32), x.value) 8,100) ) as closePrice
from Table x
group by x.stockId

So I'm combining the two fields into one so the result of 'max' actually has the data I want, I then strip out the date bit that I don't care about.

This works quite well, and is much faster - but I feel that SQL shuold have a means of doing this without tying the two cells together.

Thoughts?

J

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 23:45:26

"My first try hit the table twice - once to get the stockId, max(dateStamp) group by stockId then again using this pair to lookup the closePrice"

Will an index that covered stockId and dateStamp I would expect that to be very fast.

" select x.stockId
, convert(float, substring( convert(char(8), x.dateStamp, 112) + convert(char(32), x.value) 8,100) ) as closePrice
from Table x
group by x.stockId"

and I would expect that to give an error!! because you don't have an aggregate on the [closePrice] expression. Mind you, putting a MAX on that would work I suppose ...

How many distinct stockId are there (out of your 18M rows)?

Do they have different MAX(dateStamp) - e.g. some stocks have stopped trading? - or are you looking for the most recent date that is in the table? because that would let you do:


SELECT stockId, dateStamp, closePrice
from Table x
WHERE dateStamp = (SELECT MAX(dateStamp) FROM Table)

Kristen

Julien.Crawford
Starting Member

21 Posts

Posted - 2007-10-18 : 00:28:11

1) Yes, you are correct there was supposed to be a 'max' on the 2nd column.
2) about the distinct-ness, most stockId's are there every day (but not all)
3) the SQL you provided will get all the rows for just the one date, but I need every stockId, even the ones that have a max(dataStamp) that is less that other stocks.

Thanks for your help.

Kristen
Test

22859 Posts

Posted - 2007-10-18 : 00:34:17

"even the ones that have a max(dataStamp) that is less that other stocks"

I thought that was probably the case. Pity though!

Is it worth storing [duplicating] the "latest" closing price for a stock in a table?

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-18 : 02:12:14

Compare this SQL Server 2005 approach

SELECT	stockId,
	dateStamp,
	closePrice
from	(
		SELECT	stockId,
			dateStamp,
			closePrice,
			row_number() over (partition by stockid order by datestamp desc) as recid
		from	Table
	) as d
where	recid = 1

E 12°55'05.25"
N 56°04'39.16"

Julien.Crawford
Starting Member

21 Posts

Posted - 2007-10-18 : 02:28:45

hey - thats pretty cool.
Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-18 : 02:35:14

Kristen probably meant

SELECT	x.stockId,
	x.dateStamp,
	x.closePrice
from	Table AS x
WHERE	x.dateStamp = (SELECT MAX(y.dateStamp) FROM Table AS y WHERE y.StockID = x.StockID)

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-18 : 03:15:39

Actually that's wasn't my intention. I was hoping that all stocks had a closing price on all days, and thus the MAX(dateStamp) would be the same for all of them.

But it was a forlorn hope unfortunately!

What you posted will work of course ... just not as well as what you posted earlier ... but this will probably work under MySQL as well!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources