correlated 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)

correlated query

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-01-21 : 06:05:05

Hi,
The following query returns the result below TABLE 1.
What I am really after is the result table TABLE 2.
i.e.
retrieve one security_ID which has the max(Bid_Price) and the Max(Import_Date) for that Security_ID
Max(Import_Date), Max(Bid_Price) for each Security_ID
How can this select query be changed to produce what I am after?

p.s.I have not included sample data because I thought this could be pretty easy for you profesioinal.
Please let me know if you still require any sample data

Thanks

select
t1.Security_ID,
s.Source_Code Source_Code,
h.Bid_Size as Bid_Size,
h.Bid_Price as Bid_Price,
max(h.Import_Date) as Import_Date
from
tblTEST t1
inner join tblSources s on t1.Security_ID = s.Security_ID
inner join tblPricesSourcesImportHistory h on s.Source_Code = h.Source_Code
inner join (
select
t.Security_ID,
max(s.Source_Code) as Source_Code,
max(h.Import_Date) as Max_Date
from
tblTEST t
inner join tblSources s on t.Security_ID = s.Security_ID
inner join tblPricesSourcesImportHistory h on s.Source_ID = h.Source_ID
where
(t.Bid_Price_Best_Latest is null)
and len(h.Bid_Price) > 1
group by
t.Security_ID
) t2
on t1.Security_ID = t2.Security_ID
--and h.Source_ID = t2.Source_ID
and h.Source_Code = t2.Source_Code
and h.Import_Date = t2.Max_Date
where
(t1.Bid_Price_Best_Latest is null)
and len(h.Bid_Price) > 1
group by
t1.Security_ID,
s.Source_Code,
h.Bid_Size,
h.Bid_Price
order by
t1.Security_ID

TABLE 1

Security_ID Source_Code Bid_Size Bid_Price Import_Date
84 TTFN01 0.5 96.00 2008-01-21 10:47:00
130 TR76 0.5 101.75 2008-01-18 16:58:00
130 TR76 0.5 103.125 2008-01-18 16:58:00
130 TR76 0.5 91.00 2008-01-18 16:58:00
130 TR76 0.5 99.50 2008-01-18 16:58:00
130 TR76 1 90.00 2008-01-18 16:58:00
173 TTFN01 0.5 96.00 2008-01-21 10:47:00
189 TR75 0.5 92.00 2008-01-18 17:29:00
249 GFI01 1 89.75 2008-01-18 17:34:00

Correct Result
TABLE 2

Security_ID Source_Code Bid_Size Bid_Price Import_Date
84 TTFN01 0.5 96.00 2008-01-21 10:47:00
130 TR76 0.5 103.125 2008-01-18 16:58:00
173 TTFN01 0.5 96.00 2008-01-21 10:47:00
189 TR75 0.5 92.00 2008-01-18 17:29:00
249 GFI01 1 89.75 2008-01-18 17:34:00

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-21 : 06:20:12

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=95631
for explanation why sample data is required.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-21 : 06:21:41

For the 16th time, use ROW_NUMBER function.

quote:
Originally posted by Peso

SELECT Col1, Col2
FROM (
SELECT Col1, Col2, ROW_NUMBER() OVER (PARTITION BY Col3 ORDER BY Col4 DESC) AS RecID
FROM Table1 WHERE Col5 IS NULL
) AS d
WHERE RecID = 1

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-01-21 : 06:23:00

quote:
Originally posted by Peso

For the 16th time, use ROW_NUMBER function.
quote:
Originally posted by Peso

SELECT Col1, Col2
FROM (
SELECT Col1, Col2, ROW_NUMBER() OVER (PARTITION BY Col3 ORDER BY Col4 DESC) AS RecID
FROM Table1 WHERE Col5 IS NULL
) AS d
WHERE RecID = 1

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

Thanks for that but I just can not understand ROW_NUMBER() OVER (PARTITION
May be it is because I only have a fwe months of sql experience.
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-21 : 06:34:41

Then this is an excellent oppotunity for you to read Books Online and learn?

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-01-21 : 06:40:21

quote:
Originally posted by Peso

Then this is an excellent oppotunity for you to read Books Online and learn?

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

I have indeed.
If it is ok, please see if you can help with the existing query without the row_number function.
I will definitely read in more details once this issue is off my head.
Many thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources