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

row_number

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-01-21 : 09:23:11

Hi,
I believe this deserves a new post.
I am trying to learn Row_Number as someone has suggested it is the way to resolve my complex sql query.
So, I am reading alot regarding this and trying to implement it.
Several sample data is provided...Let me know if you need more please. so, please do not tell me off if I have not posted this properly :-)

This is my initial query. The problem with it is:
I only want the record which has the latest date but it returns the result below.
At the very bottom of the post you will see my query with ROW_Number. Does it look ok to you? Thanks

select
t.Security_ID,
s.Source_Code,
h.Bid_Price,
h.Bid_Size,
max(Import_Date) as Import_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,
s.Source_ID,
s.Source_Code,
h.Bid_Price,
h.Bid_Size
order by
t.Security_ID, Import_Date desc

RESULT of the above query is here but just below you see what should be returned as the CORRECT result:

Security_ID Source_Code Bid_Price Bid_Size Import_Date
84 GFI01 102.25 1 2008-01-21 13:05:00
84 GFI01 102.812 1 2008-01-21 12:02:00
84 GFI01 102.75 1 2008-01-21 10:12:00
84 GFI01 102.625 1 2008-01-21 10:08:00
84 GFI01 103.062 1 2008-01-18 16:47:00
84 TR75 103.00 0.5 2008-01-18 15:46:00
84 GFI01 103.00 1 2008-01-18 09:33:00
84 GFI01 103.125 1 2008-01-17 15:00:00
84 EUROVCSC45 103.00 0.5 2008-01-17 15:00:00
84 GFI01 103.25 1 2008-01-17 12:21:00
84 GFI01 103.125 2 2008-01-17 09:53:00
84 TR75 103.125 0.5 2008-01-16 17:49:00
84 TR75 102.875 0.5 2008-01-16 09:10:00
84 TR75 103.25 0.5 2008-01-15 17:23:00
84 TTFN01 103.00 0.5 2008-01-14 08:54:00
130 TR76 103.125 0.5 2008-01-18 16:58:00
130 TR76 102.625 0.5 2008-01-18 12:08:00
130 TR76 102.75 0.5 2008-01-17 16:45:00
130 EUROVCSC45 102.50 0.5 2008-01-17 11:52:00
130 TR76 102.50 0.5 2008-01-17 09:39:00
130 TR76 102.375 0.5 2008-01-17 09:38:00
130 TR76 102.25 0.5 2008-01-16 16:44:00
173 TR75 101.625 0.5 2008-01-21 13:05:00
173 TR75 101.50 0.5 2008-01-21 11:28:00
173 TR75 102.062 0.5 2008-01-18 16:58:00
173 TR75 102.00 0.5 2008-01-18 09:34:00
173 EUROVCSC45 102.06 0.5 2008-01-17 15:00:00
173 TTFN01 102.00 0.5 2008-01-17 13:28:00
173 TR75 101.875 0.5 2008-01-17 08:25:00
173 TTFN01 101.875 0.5 2008-01-16 16:51:00
173 EUROVCSC45 102.00 0.5 2008-01-16 16:34:00
173 EUROVCSC45 101.87 0.5 2008-01-16 08:38:00
189 TR75 92.00 0.5 2008-01-18 17:29:00
189 TR75 92.25 0.5 2008-01-17 17:06:00
189 TR75 92.125 0.5 2008-01-17 14:15:00
189 TR75 93.00 0.5 2008-01-17 08:01:00
189 EUROVCSC8 92.25 0.5 2008-01-16 16:58:00
189 EUROVCSC8 92.00 0.5 2008-01-16 10:05:00
189 EUROVCSC8 91.00 0.5 2008-01-15 16:30:00
189 TR75 91.50 0.5 2008-01-15 14:52:00
249 GFI01 89.75 1 2008-01-18 17:34:00
249 4GDC 90.25 1 2008-01-17 10:40:00
249 EUROVCSC45 90.00 0.5 2008-01-17 09:29:00
249 GFI01 90.25 1 2008-01-17 08:13:00
249 EUROVCSC45 90.25 0.5 2008-01-16 10:56:00

CORRECT RESULT SHOULD BE:

Security_ID Source_Code Bid_Price Bid_Size Import_Date
84 GFI01 102.25 1 2008-01-21 13:05:00
130 TR76 103.125 0.5 2008-01-18 16:58:00
173 TR75 101.625 0.5 2008-01-21 13:05:00
189 TR75 92.00 0.5 2008-01-18 17:29:00
249 GFI01 89.75 1 2008-01-18 17:34:00

--------------

Here is some sample data for you :

create table tblTEST
(
Security_ID smallint,
Security_Name varchar(50),
Bid_Price_Best_Latest decimal(12, 4),
Bid_Size_Best_Latest decimal(12, 2),
Bid_Source varchar(1000),
Ask_Price_Best_Latest decimal(12, 4),
Ask_Size_Best_Latest decimal(12, 2),
Ask_Source varchar(1000)
)

insert into tblTEST(Security_ID, Security_Name, Bid_Price_Best_Latest,Bid_Size_Best_Latest,Bid_Source,Ask_Price_Best_Latest,Ask_Size_Best_Latest,Ask_Source)

values(22,'RUSSIA12 3/4 06/28', 183.0000,2.00,G,184.2500,2.00,'N') union all
insert into tblTEST(Security_ID, Security_Name, Bid_Price_Best_Latest,Bid_Size_Best_Latest,Bid_Source,Ask_Price_Best_Latest,Ask_Size_Best_Latest,Ask_Source)

values(34,'ALFARU 7 7/8 10/09', 98.0000,0.50,'B',98.8800,0.50,'B') union all
insert into tblTEST(Security_ID, Security_Name, Bid_Price_Best_Latest,Bid_Size_Best_Latest,Bid_Source,Ask_Price_Best_Latest,Ask_Size_Best_Latest,Ask_Source)

values(35,'ALFARU 8.635 02/17',93.0000,0.50,'B',94.0000,0.50,'B') union all
insert into tblTEST(Security_ID, Security_Name, Bid_Price_Best_Latest,Bid_Size_Best_Latest,Bid_Source,Ask_Price_Best_Latest,Ask_Size_Best_Latest,Ask_Source)

values(36,'ALROSA 8 7/8 11/14',107.5000,0.50,'B',108.2500,1.00,'G') union all
insert into tblTEST(Security_ID, Security_Name, Bid_Price_Best_Latest,Bid_Size_Best_Latest,Bid_Source,Ask_Price_Best_Latest,Ask_Size_Best_Latest,Ask_Source)

values(51,'EVRAZ 8 1/4 11/15', 98.5000 0.50,'T',99.5000,0.50,'T') union all
insert into tblTEST(Security_ID, Security_Name, Bid_Price_Best_Latest,Bid_Size_Best_Latest,Bid_Source,Ask_Price_Best_Latest,Ask_Size_Best_Latest,Ask_Source)

values(56,'GAZPRU 7.201 02/20', 101.7500,2.00,'T',,NULL,NULL,NULL) union all
insert into tblTEST(Security_ID, Security_Name, Bid_Price_Best_Latest,Bid_Size_Best_Latest,Bid_Source,Ask_Price_Best_Latest,Ask_Size_Best_Latest,Ask_Source)

values(60,'GAZPRU 5 7/8 06/15', 93.5000 2.00,'I',NULL,NULL,NULL) union all
insert into tblTEST(Security_ID, Security_Name, Bid_Price_Best_Latest,Bid_Size_Best_Latest,Bid_Source,Ask_Price_Best_Latest,Ask_Size_Best_Latest,Ask_Source)

values(74,'LUKOIL 6.356 06/17', 94.2500 1.50,'G,N',94.8750,0.50,'N') union all
insert into tblTEST(Security_ID, Security_Name, Bid_Price_Best_Latest,Bid_Size_Best_Latest,Bid_Source,Ask_Price_Best_Latest,Ask_Size_Best_Latest,Ask_Source)

values(75,'LUKOIL 6.656 06/22', 91.5000 0.50,'T',92.5000,0.50,'T') union all
insert into tblTEST(Security_ID, Security_Name, Bid_Price_Best_Latest,Bid_Size_Best_Latest,Bid_Source,Ask_Price_Best_Latest,Ask_Size_Best_Latest,Ask_Source)

values(84,'MOBTEL 8 3/8 10/10', NULL,NULL,NULL,103.5000,0.50,'T') union all
insert into tblTEST(Security_ID, Security_Name, Bid_Price_Best_Latest,Bid_Size_Best_Latest,Bid_Source,Ask_Price_Best_Latest,Ask_Size_Best_Latest,Ask_Source)

values(90,'NOMOS 8 1/4 05/09',97.2500,1.00,'B', 98.2500,1.00,'B') union all
insert into tblTEST(Security_ID, Security_Name, Bid_Price_Best_Latest,Bid_Size_Best_Latest,Bid_Source,Ask_Price_Best_Latest,Ask_Size_Best_Latest,Ask_Source)

values(94,'PETRRU 8 3/4 12/09', 97.7500 0.50,'B',98.5000,0.50,'B') union all
insert into tblTEST(Security_ID, Security_Name, Bid_Price_Best_Latest,Bid_Size_Best_Latest,Bid_Source,Ask_Price_Best_Latest,Ask_Size_Best_Latest,Ask_Source)

values(95,'PROMBK 8 3/4 10/10', 94.2500 0.50,'T',95.5000,0.50,'T') union all
insert into tblTEST(Security_ID, Security_Name, Bid_Price_Best_Latest,Bid_Size_Best_Latest,Bid_Source,Ask_Price_Best_Latest,Ask_Size_Best_Latest,Ask_Source)

values(96,'RASPAD 7 1/2 05/12', 96.2500 1.00,'G',97.2500,1.00,'G') union all
insert into tblTEST(Security_ID, Security_Name, Bid_Price_Best_Latest,Bid_Size_Best_Latest,Bid_Source,Ask_Price_Best_Latest,Ask_Size_Best_Latest,Ask_Source)

values(125,'RUSSIA 7 1/2 03/30',115.06204.00,'B,G',115.1800,6.00,'B,G,N') union all
insert into tblTEST(Security_ID, Security_Name, Bid_Price_Best_Latest,Bid_Size_Best_Latest,Bid_Source,Ask_Price_Best_Latest,Ask_Size_Best_Latest,Ask_Source)

values(129,'UKRAIN 6.58 11/16', 99.8750 1.00,'T',NULL,NULL,NULL) union all
insert into tblTEST(Security_ID, Security_Name, Bid_Price_Best_Latest,Bid_Size_Best_Latest,Bid_Source,Ask_Price_Best_Latest,Ask_Size_Best_Latest,Ask_Source)

values(130,'AFKSRU 8 7/8 01/11',NULL,NULL,NULL,103.5000,0.50,'N') union all
insert into tblTEST(Security_ID, Security_Name, Bid_Price_Best_Latest,Bid_Size_Best_Latest,Bid_Source,Ask_Price_Best_Latest,Ask_Size_Best_Latest,Ask_Source)

values(135,'ALFARU8.2 06/25/12',94.0000,0.50,'B',95.0000,0.50,'B') union all
insert into tblTEST(Security_ID, Security_Name, Bid_Price_Best_Latest,Bid_Size_Best_Latest,Bid_Source,Ask_Price_Best_Latest,Ask_Size_Best_Latest,Ask_Source)

values(171,'MDMBK 7.765 01/10', 97.2500,1.00,'B',NULL,NULL,NULL) union all
insert into tblTEST(Security_ID, Security_Name, Bid_Price_Best_Latest,Bid_Size_Best_Latest,Bid_Source,Ask_Price_Best_Latest,Ask_Size_Best_Latest,Ask_Source)

values(173,'MEGAFO 8 12/10/09', NULL,NULL,NULL,102.4370,0.50,'T') union all
insert into tblTEST(Security_ID, Security_Name, Bid_Price_Best_Latest,Bid_Size_Best_Latest,Bid_Source,Ask_Price_Best_Latest,Ask_Size_Best_Latest,Ask_Source)

values(176,'MOBTEL 8 01/28/12', 101.5000,1.00,'G',102.5000,1.00,'G') union all
insert into tblTEST(Security_ID, Security_Name, Bid_Price_Best_Latest,Bid_Size_Best_Latest,Bid_Source,Ask_Price_Best_Latest,Ask_Size_Best_Latest,Ask_Source)

values(180,'NOMOS 8.1875 02/10',95.2500,1.00,'B',NULL,NULL,NULL) unin all
...
...
...

RESULT of the above inserts:

22 RUSSIA12 3/4 06/28 183.0000 2.00 G 184.2500 2.00 N
34 ALFARU 7 7/8 10/09 98.0000 0.50 B 98.8800 0.50 B
35 ALFARU 8.635 02/17 93.0000 0.50 B 94.0000 0.50 B
36 ALROSA 8 7/8 11/14 107.5000 0.50 B 108.2500 1.00 G
51 EVRAZ 8 1/4 11/15 98.5000 0.50 T 99.5000 0.50 T
56 GAZPRU 7.201 02/20 101.7500 2.00 T NULL NULL NULL
60 GAZPRU 5 7/8 06/15 93.5000 2.00 I NULL NULL NULL
74 LUKOIL 6.356 06/17 94.2500 1.50 G,N 94.8750 0.50 N
75 LUKOIL 6.656 06/22 91.5000 0.50 T 92.5000 0.50 T
84 MOBTEL 8 3/8 10/10 NULL NULL NULL 103.5000 0.50 T
90 NOMOS 8 1/4 05/09 97.2500 1.00 B 98.2500 1.00 B
94 PETRRU 8 3/4 12/09 97.7500 0.50 B 98.5000 0.50 B
95 PROMBK 8 3/4 10/10 94.2500 0.50 T 95.5000 0.50 T
96 RASPAD 7 1/2 05/12 96.2500 1.00 G 97.2500 1.00 G
125 RUSSIA 7 1/2 03/30 115.0620 4.00 B,G 115.1800 6.00 B,G,N
129 UKRAIN 6.58 11/16 99.8750 1.00 T NULL NULL NULL
130 AFKSRU 8 7/8 01/11 NULL NULL NULL 103.5000 0.50 N
135 ALFARU8.2 06/25/12 94.0000 0.50 B 95.0000 0.50 B
171 MDMBK 7.765 01/10 97.2500 1.00 B NULL NULL NULL
173 MEGAFO 8 12/10/09 NULL NULL NULL 102.4370 0.50 T
176 MOBTEL 8 01/28/12 101.5000 1.00 G 102.5000 1.00 G
180 NOMOS 8.1875 02/10 95.2500 1.00 B NULL NULL NULL
188 PROMBK 8 3/4 10/11 93.5000 0.50 T 94.5000 0.50 B
189 PROMBK 9 5/8 05/12 NULL NULL NULL 93.5000 0.50 T
221 VIP 8 1/4 05/23/16 99.0000 1.00 G 100.0000 1.00 G
233 ZENIT 8 3/4 10/09 98.7500 0.50 N 99.2500 0.50 N
249 TMENRU 6 5/8 03/17 NULL NULL NULL 90.3750 1.00 G
250 TNEFT 6.103 06/12 99.1250 0.50 T 99.8750 0.50 T
255 VIP 10 06/16/09 103.5000 0.50 T 104.5000 0.50 T
333 GAZPRU 6.605 02/18 93.5000 1.00 T NULL NULL NULL
341 TMENRU 7 7/8 03/18 97.5000 0.50 T 98.5000 0.50 T
346 NKNCRU 8 1/2 12/15 96.5000 0.50 L 97.0000 0.50 L
355 FIUKR 9 3/4 02/10 95.0000 0.50 L NULL NULL NULL
373 URSAP 7 05/21/10 90.0000 1.00 T NULL NULL NULL
381 TURKEY11 7/8 01/30 155.1200 2.00 B 156.0000 2.00 B

-----------------

CREATE TABLE [dbo].[tblSources](
[Source_ID] [int] IDENTITY(1,1) NOT NULL,
[Security_ID] [smallint] NOT NULL,
[Source_Parent] [varchar](50) NOT NULL,
[Source_Code] [varchar](20) NOT NULL,
[Source_Security_Name] [varchar](50) NOT NULL)

insert into tblSources (Source_ID, Security_ID,Source_Parent,Source_Code,Source_Security_Name) values (121,84,'Testers','GFI01','MTS 10') union all
insert into tblSources (Source_ID, Security_ID,Source_Parent,Source_Code,Source_Security_Name) values (169,84,'Testers','QLABND1','MTS 10 .') union all
insert into tblSources (Source_ID, Security_ID,Source_Parent,Source_Code,Source_Security_Name) values (177,130,'Testers','QLABND1','SIST 11') union all
insert into tblSources (Source_ID, Security_ID,Source_Parent,Source_Code,Source_Security_Name) values (177,130,'Testers','QLABND1','SIST 11') union all
insert into tblSources (Source_ID, Security_ID,Source_Parent,Source_Code,Source_Security_Name) values (370,84,'Testers','TR75','MTS 8.375 10') union all

insert into tblSources (Source_ID, Security_ID,Source_Parent,Source_Code,Source_Security_Name) values (517,84,'Testers','EUROVCSC45','MTS 10 $') union all

insert into tblSources (Source_ID, Security_ID,Source_Parent,Source_Code,Source_Security_Name) values (520,130,'Testers ','EUROVCSC45','SISTEMA 11 $') union

all

insert into tblSources (Source_ID, Security_ID,Source_Parent,Source_Code,Source_Security_Name) values (562,130,'Testers ','NFBC','SIST11') union all

insert into tblSources (Source_ID, Security_ID,Source_Parent,Source_Code,Source_Security_Name) values (623,84,'Testers','4GDC','MTS 10$') union all

insert into tblSources (Source_ID, Security_ID,Source_Parent,Source_Code,Source_Security_Name) values (635,130,'Testers','4GDC','SISTEMA 11$') union all

insert into tblSources (Source_ID, Security_ID,Source_Parent,Source_Code,Source_Security_Name) values (742,84,'Testers','TTFN01','MTS 10 $') union all

insert into tblSources (Source_ID, Security_ID,Source_Parent,Source_Code,Source_Security_Name) values (752,130,'Testers','TTFN01','SISTEMA 11$') union all

insert into tblSources (Source_ID, Security_ID,Source_Parent,Source_Code,Source_Security_Name) values (816,84,'Testers','TTFN06','MTS 10 $') union all

insert into tblSources (Source_ID, Security_ID,Source_Parent,Source_Code,Source_Security_Name) values (829,130,'Testers','TTFN06','SISTEMA 11$')

RESULT of the above inserts:

Source_ID Security_ID Source_Parent Source_Code Source_Security_Name
121 84 Testers GFI01 MTS 10
169 84 Testers QLABND1 MTS 10 .
177 130 Testers QLABND1 SIST 11
370 84 Testers TR75 MTS 8.375 10
405 130 Testers TR76 SIST 8.875 11
517 84 Testers EUROVCSC45 MTS 10 $
520 130 Testers EUROVCSC45 SISTEMA 11 $
562 130 Testers NFBC SIST11
623 84 Testers 4GDC MTS 10$
635 130 Testers 4GDC SISTEMA 11$
742 84 Testers TTFN01 MTS 10 $
752 130 Testers TTFN01 SISTEMA 11$
816 84 Testers TTFN06 MTS 10 $
829 130 Testers TTFN06 SISTEMA 11$

---------------------

my row_number() query

select
t.Security_ID,
s.Source_ID,
s.Source_Code,
max(h.Bid_Price),
max(h.Bid_Size),
max(h.Import_Date) as Import_Date
from
(
select
Security_ID,
Source_ID,
Source_Code,
null,
null
ROW_NUMBER() OVER (PARTITION BY Security_ID ORDER BY Import_Date DESC) AS RecID
from
tblSources
)
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,
s.Source_ID,
s.Source_Code,
h.Bid_Price,
h.Bid_Size
order by
Security_ID,
Import_Date desc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-21 : 09:56:44

18th time...

SELECT Security_ID, Source_Code, Bid_Price, Bid_Size, Import_Date FROM
(
SELECT Security_ID, Source_Code, Bid_Price, Bid_Size, Import_Date, ROW_NUMBER() OVER (PARTITION BY Security_ID ORDER BY Import_Date DESC) AS RecID
FROM tblTest
) 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 : 10:20:51

As I require data from other tables I have modified it as below.
Does it look ok to you? thanks
SELECT Security_ID, Source_Code, Bid_Price, Bid_Size, Import_Date FROM
(
SELECT t.Security_ID, s.Source_Code, h.Bid_Price, h.Bid_Size, h.Import_Date, ROW_NUMBER() OVER (PARTITION BY t.Security_ID ORDER BY Import_Date DESC) AS RecID
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
) AS d
WHERE RecID = 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-21 : 10:26:15

I can't tell. It depends on your business rules where to append the JOIN.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-21 : 10:27:09

Run the query. If the results look OK, the query is problably working.

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-01-21 : 10:28:55

Just ran it. Looks ok, need to run more tests.
If it is ok, I will expand the sql and ask you to have a look at it later.
Thanks

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-01-21 : 11:52:21

It appears there are records missing using this query:

SELECT
Security_ID, Bid_Price, Bid_Size, Source_Code, Import_Date FROM
(
SELECT
t.Security_ID,
case
when dbo.isreallynumeric(ltrim(rtrim(h.Bid_Price))) = 1 then convert(decimal(12, 8), ltrim(rtrim(h.Bid_Price)))
end as Bid_Price,
h.Bid_Size,
isnull(dbo.fnSourceShortCode(s.Source_Code),'') as Source_Code,
h.Import_Date,
ROW_NUMBER() OVER (PARTITION BY t.Security_ID ORDER BY Import_Date DESC) AS RecID
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
) AS d
WHERE
RecID = 1
and ltrim(rtrim(len(Bid_Price))) > 1

RESULT:

Security_ID Bid_Price Bid_Size Bid_Source Import_Date
22 182.62500000 2 G 2008-01-21 13:28:00
35 93.00000000 0.5 B 2008-01-21 10:50:00
51 98.50000000 0.5 T 2008-01-21 11:21:00
56 101.75000000 2 T 2008-01-21 11:59:00
75 91.50000000 0.5 T 2008-01-21 10:56:00
90 97.25000000 1 B 2008-01-21 10:49:00
94 97.75000000 0.5 B 2008-01-21 10:23:00
95 94.25000000 0.5 T 2008-01-21 14:29:00
96 95.75000000 1 G 2008-01-21 13:52:00
125 115.00000000 2 G 2008-01-21 14:06:00
135 94.00000000 0.5 B 2008-01-21 10:50:00
171 97.25000000 1 B 2008-01-21 10:49:00
176 101.25000000 1 G 2008-01-21 13:50:00
180 95.25000000 1 B 2008-01-21 10:49:00
188 93.50000000 0.5 T 2008-01-21 14:29:00
233 98.75000000 0.5 N 2008-01-21 11:33:00
255 103.62500000 0.5 T 2008-01-21 14:30:00
333 93.00000000 1 T 2008-01-21 14:23:00
341 97.50000000 0.5 T 2008-01-21 11:57:00
346 96.50000000 0.5 L 2008-01-21 13:48:00
355 95.00000000 0.5 L 2008-01-21 10:16:00
373 90.00000000 1 T 2008-01-21 14:33:00
381 155.00000000 2 B 2008-01-21 14:15:00

In case it helps, the query below returns all the Security_IDs that should be shown.

select t.Security_ID, Max(h.Import_Date) Import_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
group by t.Security_ID order by t.Security_ID

RESULT:

Security_ID Import_Date
22 2008-01-21 13:28:00
34 2008-01-21 10:50:00
35 2008-01-21 10:50:00
36 2008-01-21 13:52:00
51 2008-01-21 11:21:00
56 2008-01-21 11:59:00
60 2008-01-21 14:23:00
74 2008-01-21 13:52:00
75 2008-01-21 10:56:00
84 2008-01-21 14:29:00
90 2008-01-21 10:49:00
94 2008-01-21 10:23:00
95 2008-01-21 14:29:00
96 2008-01-21 13:52:00
125 2008-01-21 14:06:00
129 2008-01-21 14:23:00
130 2008-01-21 11:35:00
135 2008-01-21 10:50:00
171 2008-01-21 10:49:00
173 2008-01-21 14:31:00
176 2008-01-21 13:50:00
180 2008-01-21 10:49:00
188 2008-01-21 14:29:00
189 2008-01-21 14:23:00
221 2008-01-21 13:52:00
233 2008-01-21 11:33:00
249 2008-01-21 13:52:00
250 2008-01-21 14:30:00
255 2008-01-21 14:30:00
333 2008-01-21 14:23:00
341 2008-01-21 11:57:00
346 2008-01-21 13:48:00
355 2008-01-21 10:16:00
373 2008-01-21 14:33:00
381 2008-01-21 14:15:00

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-01-21 : 12:01:35

Altered the select as follows:
SELECT
Security_ID, Bid_Price, Bid_Size, Source_Code, Import_Date FROM
(
SELECT
t.Security_ID,
case
when dbo.isreallynumeric(ltrim(rtrim(h.Bid_Price))) = 1 then convert(decimal(12, 8), ltrim(rtrim(h.Bid_Price)))
end as Bid_Price,
h.Bid_Size,
isnull(dbo.fnSourceShortCode(s.Source_Code),'') as Source_Code,
h.Import_Date,
ROW_NUMBER() OVER (PARTITION BY t.Security_ID ORDER BY Import_Date DESC) AS RecID
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
ltrim(rtrim(len(h.Bid_Price))) > 1
) AS d
WHERE
RecID = 1
and ltrim(rtrim(len(Bid_Price))) > 1

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-01-21 : 12:29:40

It seems the results are coming out nicely so far. Still testing...
Do you know how I can join these two select queries? thanks

SELECT
Security_ID, Bid_Price, Bid_Size, Source_Code, Import_Date
FROM
(
SELECT
t.Security_ID,
case
when dbo.isreallynumeric(ltrim(rtrim(h.Bid_Price))) = 1 then convert(decimal(12, 8), ltrim(rtrim(h.Bid_Price)))
end as Bid_Price,
h.Bid_Size,
isnull(dbo.fnSourceShortCode(s.Source_Code),'') as Source_Code,
h.Import_Date,
ROW_NUMBER() OVER (PARTITION BY t.Security_ID ORDER BY Import_Date DESC) AS RecID
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
ltrim(rtrim(len(h.Bid_Price))) > 1
) AS d
WHERE
RecID = 1
and ltrim(rtrim(len(Bid_Price))) > 1

---------
SELECT
Security_ID, Ask_Price, Ask_Size, Source_Code, Import_Date
FROM
(
SELECT
t.Security_ID,
case
when dbo.isreallynumeric(ltrim(rtrim(h.Ask_Price))) = 1 then convert(decimal(12, 8), ltrim(rtrim(h.Ask_Price)))
end as Ask_Price,
h.Ask_Size,
isnull(dbo.fnSourceShortCode(s.Source_Code),'') as Source_Code,
h.Import_Date,
ROW_NUMBER() OVER (PARTITION BY t.Security_ID ORDER BY Import_Date DESC) AS RecID
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
ltrim(rtrim(len(h.Ask_Price))) > 1
) AS d
WHERE
RecID = 1
and ltrim(rtrim(len(Ask_Price))) > 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-21 : 16:19:01

The easiest thing is

select *
from (1st query here) as t1
inner join (2nd query here) as t2 on t2.security_id = t1.security_id

I wrote this to you for 4 months ago.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources