unique query - SQL Server Forums

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)

unique query

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-12-19 : 05:03:40

Hi,
There is a table called tblPrices with fields
Security_ID int
Bid_Price decimal(12, 4)
Ask_Price decimal(12, 4)
Price_Quote_Date smalldatetime

In this table there are thousands of records. For each Security_ID, there are several records with different Price_Quote_Dates
What I would like to have is:
A select query which shows every single Security_ID with the prices for the LATEST date that there is a price for that Security_ID

Example:

1,100, 102.32, 1/12/2005
1, 43, 76.33, 6/12/2005
2,65.77, 45.76, 12/12/2007
...
...
...

Result

1, 43, 76.33, 6/12/2005
2,65.77, 45.76, 12/12/2007
...
...
...

This is what I have started but it is not correct yet because the Security_IDs are repeated
select
Security_ID,
Bid_Price,
Ask_Price,
max(Price_Quote_Date)
from
tblPrices
group by
Security_ID,
Bid_Price,
Ask_Price

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-19 : 05:10:53

May be this:

Select
t1.Security_ID,
t1.Bid_Price,
t1.Ask_Price,
t1.Price_Quote_Date
from
tblPrices t1 join (select Security_ID, max(Price_Quote_Date) as Price_Quote_Date from tblPrices group by Security_ID) t2
on t1.Security_ID = t2.Security_ID and t1.Price_Quote_Date = t2.Price_Quote_Date

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-12-19 : 05:15:27

Very good.
Thanks

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-12-20 : 04:58:12

quote:

Originally posted by harsh_athalye

May be this:

Select
t1.Security_ID,
t1.Bid_Price,
t1.Ask_Price,
t1.Price_Quote_Date
from
tblPrices t1 join (select Security_ID, max(Price_Quote_Date) as Price_Quote_Date from tblPrices group by Security_ID) t2
on t1.Security_ID = t2.Security_ID and t1.Price_Quote_Date = t2.Price_Quote_Date

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Hello again,
I had to add a group by to the query as follows because otherwise I get duplicates:

Select
t1.Security_ID,
t1.Bid_Price,
t1.Ask_Price,
t1.Price_Quote_Date
from
tblPrices t1 join (select Security_ID, max(Price_Quote_Date) as Price_Quote_Date from tblPrices group by Security_ID) t2
on t1.Security_ID = t2.Security_ID and t1.Price_Quote_Date = t2.Price_Quote_Date
group by
t1.Security_ID,
t1.Bid_Price,
t1.Ask_Price,
t1.Price_Quote_Date
order by
t1.Security_ID

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-20 : 05:00:42

Use DISTINCT instead of GROUP BY.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-12-20 : 07:01:26

Why?
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 07:18:12

Learn the new tools in SQL Server 2005

SELECT	Security_ID,
	Bid_Price,
	Ask_price,
	Price_Quote_Date
FROM	(
		SELECT	Security_ID,
			Bid_Price,
			Ask_price,
			Price_Quote_Date,
			ROW_NUMBER() OVER (PARTITION BY Security_ID ORDER BY Price_Quote_Date DESC) AS RecID
		FROM	tblPrices
	) AS d
WHERE	RecID = 1

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-20 : 09:16:51

and see what you can do with row_number() function
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-12-20 : 11:28:45

I will study the Row_Number at some stage later but for now can you help with this please?
If it is ok, can you please let me know how I can modify this query so that if the Bid_Price or Ask_Price is 0 for the max(price_quote_date), then I want to get the prices for a date that has a value > 0 or is not null

Select
distinct
t1.VTB_Security_ID,
isnull(t1.Bid_Price, 0) Bid_Price,
isnull(t1.Ask_Price, 0) Ask_Price,
t1.Price_Quote_Date
from
tblPrices t1 inner join (select
VTB_Security_ID,
max(Price_Quote_Date) as Price_Quote_Date
from
tblPrices
group by
VTB_Security_ID) t2
on t1.VTB_Security_ID = t2.VTB_Security_ID
and t1.Price_Quote_Date = t2.Price_Quote_Date
order by
t1.VTB_Security_ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-20 : 11:48:04

[code]Select
distinct
t1.VTB_Security_ID,
isnull(t1.Bid_Price, 0) Bid_Price,
isnull(t1.Ask_Price, 0) Ask_Price,
t1.Price_Quote_Date
from
tblPrices t1 inner join (select
VTB_Security_ID,
max(Price_Quote_Date) as Price_Quote_Date
from
tblPrices
Where isnull(t1.Bid_Price, 0)<> 0
AND isnull(t1.Ask_Price, 0)<>0group by
VTB_Security_ID) t2
on t1.VTB_Security_ID = t2.VTB_Security_ID
and t1.Price_Quote_Date = t2.Price_Quote_Date
order by
t1.VTB_Security_ID[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 11:51:17

Learn the new tools in SQL Server 2005

SELECT	Security_ID,
	Bid_Price,
	Ask_Price,
	Price_Quote_Date
FROM	(
		SELECT	Security_ID,
			Bid_Price,
			Ask_Price,
			Price_Quote_Date,
			ROW_NUMBER() OVER (PARTITION BY Security_ID ORDER BY Price_Quote_Date DESC) AS RecID
		FROM	tblPrices
		WHERE	Bid_Price > 0
			AND Ask_Price > 0
	) AS d
WHERE	RecID = 1

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-12-21 : 04:04:46

quote:

Originally posted by visakh16

Select
distinct 
t1.VTB_Security_ID,
isnull(t1.Bid_Price, 0) Bid_Price,
isnull(t1.Ask_Price, 0) Ask_Price,
t1.Price_Quote_Date
from
tblPrices t1 inner join (select 
VTB_Security_ID, 
max(Price_Quote_Date) as Price_Quote_Date 
from 
tblPrices 
Where isnull(t1.Bid_Price, 0)<> 0
AND isnull(t1.Ask_Price, 0)<>0group by 
VTB_Security_ID) t2
on t1.VTB_Security_ID = t2.VTB_Security_ID 
and t1.Price_Quote_Date = t2.Price_Quote_Date 
order by
t1.VTB_Security_ID

There are records missing using this query

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-12-21 : 04:32:07

quote:

Originally posted by Peso

Learn the new tools in SQL Server 2005

SELECT	Security_ID,
	Bid_Price,
	Ask_Price,
	Price_Quote_Date
FROM	(
		SELECT	Security_ID,
			Bid_Price,
			Ask_Price,
			Price_Quote_Date,
			ROW_NUMBER() OVER (PARTITION BY Security_ID ORDER BY Price_Quote_Date DESC) AS RecID
		FROM	tblPrices
		WHERE	Bid_Price > 0
			AND Ask_Price > 0
	) AS d
WHERE	RecID = 1

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

using this query, there are records missing

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-21 : 04:46:08

[code]SELECT Security_ID,
MAX(Bid_Price) AS Bid_Price,
MAX(Ask_Price) AS Ask_Price,
MAX(Price_Quote_Date) AS Price_Quote_Date
FROM (
SELECT Security_ID,
Bid_Price,
NULL AS Ask_Price,
Price_Quote_Date,
ROW_NUMBER() OVER (PARTITION BY Security_ID ORDER BY Price_Quote_Date DESC) AS RecID
FROM tblPrices
WHERE Bid_Price > 0

UNION ALL

SELECT Security_ID,
NULL,
Ask_Price,
Price_Quote_Date,
ROW_NUMBER() OVER (PARTITION BY Security_ID ORDER BY Price_Quote_Date DESC)
FROM tblPrices
WHERE Ask_Price > 0

) AS d
WHERE RecID = 1
GROUP BY Security_ID
ORDER BY Security_ID[/code]

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-12-21 : 05:05:01

still records missing.
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-21 : 05:08:51

Can you please enlighten us and tell which records are missing from what?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-21 : 05:11:21

[code]SELECT Security_ID,
MAX(Bid_Price) AS Bid_Price,
MAX(Bid_Date) AS Bid_Date,
MAX(Ask_Price) AS Ask_Price,
MAX(Ask_Date) AS Price_Quote_Date
FROM (
SELECT Security_ID,
Bid_Price,
Price_Quote_Date AS Bid_Date,
NULL AS Ask_Price,
NULL AS Ask_Date,
ROW_NUMBER() OVER (PARTITION BY Security_ID ORDER BY Price_Quote_Date DESC) AS RecID
FROM tblPrices
WHERE Bid_Price > 0

UNION ALL

SELECT Security_ID,
NULL,
NULL,
Ask_Price,
Price_Quote_Date,
ROW_NUMBER() OVER (PARTITION BY Security_ID ORDER BY Price_Quote_Date DESC) AS RecID
FROM tblPrices
WHERE Ask_Price > 0

) AS d
WHERE RecID = 1
GROUP BY Security_ID
ORDER BY Security_ID[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-21 : 05:16:06

Have your requirements changed? Your originally wrote

quote:
A select query which shows every single Security_ID with the prices for the LATEST date that there is a price for that Security_ID

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-12-21 : 05:45:48

I know why there are records missing and it is fine.

It seems the two queries below give the same results. Is there a difference between these two queries please?

Select
distinct
t1.Security_ID,
isnull(t1.Bid_Price, 0) Bid_Price,
isnull(t1.Ask_Price, 0) Ask_Price,
t1.Price_Quote_Date
from
tblPrices t1 inner join (select
Security_ID,
max(Price_Quote_Date) as Price_Quote_Date
from
tblPrices
Where isnull(t1.Bid_Price, 0)<> 0
AND isnull(t1.Ask_Price, 0)<>0group by
Security_ID) t2
on t1.Security_ID = t2.Security_ID
and t1.Price_Quote_Date = t2.Price_Quote_Date
order by
t1.Security_ID

----------------------------------------------------
SELECT Security_ID,
MAX(Bid_Price) AS Bid_Price,
MAX(Bid_Date) AS Bid_Date,
MAX(Ask_Price) AS Ask_Price,
MAX(Ask_Date) AS Price_Quote_Date
FROM (
SELECT Security_ID,
Bid_Price,
Price_Quote_Date AS Bid_Date,
NULL AS Ask_Price,
NULL AS Ask_Date,
ROW_NUMBER() OVER (PARTITION BY Security_ID ORDER BY Price_Quote_Date DESC) AS RecID
FROM tblPrices
WHERE Bid_Price > 0

UNION ALL

SELECT Security_ID,
NULL,
NULL,
Ask_Price,
Price_Quote_Date,
ROW_NUMBER() OVER (PARTITION BY Security_ID ORDER BY Price_Quote_Date DESC) AS RecID
FROM tblPrices
WHERE Ask_Price > 0

) AS d
WHERE RecID = 1
GROUP BY Security_ID
ORDER BY Security_ID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-21 : 07:08:24

There is a difference between the two queries above.

The first selects the record with the last date and provide the bids for that date.
The second query returns the last date and bid value together with last date and ask value.
These two values (maximum bid and maximum ask) is not necessarily on the same record.

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-12-21 : 07:18:49

Very good.
Thank you

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources