complex sql server 2005 query

Hi,
A sql server table is populated with records every 2 minutes. See below sample table
In the table, the Import_Date is a datetime field.

create table tblData
(
ID int identity(1, 1),
SourceID int,
SourceCode varchar(255)
Security varchar(255),
Bprice decimal(12, 8),
Aprice decimal(12, 8),
ImportDate datetime
)

Here is a populated table.
I have left gaps for better visual checks for you.

ID SourceID SourceCode Security Bprice BpriceSize Aprice ApriceSize ImportDate

1 1 sourceA SecA 100.2 2 99.12 1 2007-11-07 16:24:31.297
2 2 sourceW SecH 95.7 89.43 2007-11-07 16:24:31.297
3 3 SourceX SecS 50.56 1 76.44 4 2007-11-07 16:24:31.297
4 4 SourceQ SecZ 87.98 2007-11-07 16:24:31.297
5 5 SourceJ SecH 100.2 99.12 2 2007-11-07 16:24:31.297
6 6 SourceK SecU 2007-11-07 16:24:31.297
7 7 SourceT SecA 50.56 3 87.11 2007-11-07 16:24:31.297

8 1 sourceA SecA 100.2 6 99.12 2 2007-11-07 16:26:15.123
9 2 sourceW SecH 99.54 4 89.43 2007-11-07 16:26:15.123
10 3 SourceX SecS 50.56 2 19.33 2007-11-07 16:26:15.123
11 4 SourceQ SecZ 16.98 87.98 2007-11-07 16:26:15.123
12 5 SourceJ SecH 100.2 1 99.12 2 2007-11-07 16:26:15.123
13 6 SourceK SecU 2007-11-07 16:26:15.123
14 7 SourceT SecA 50.56 2 87.11 1 2007-11-07 16:26:15.123

15 1 sourceA SecA 100.2 1 87.11 1 2007-11-07 16:26:15.123
16 2 sourceW SecH 99.66 89.43 2 2007-11-07 16:26:15.123
17 3 SourceX SecS 50.56 2 19.33 2007-11-07 16:26:15.123
18 4 SourceQ SecZ 16.98 3 87.98 3 2007-11-07 16:26:15.123
19 5 SourceJ SecH 100.2 3 99.12 3 2007-11-07 16:26:15.123
20 6 SourceK SecU 2007-11-07 16:26:15.123
21 7 SourceT SecA 101.32 5 87.11 3 2007-11-07 16:26:15.123
...

I am trying to build a sql query to show which source is offering the max(Bprice) and who is offering the min(Aprice).
In addition if more than one sources are offering the same prices then they should be shown as shown below in the first record i.e. (SourceA, SourceT) --> 3 + 1 = 4
This is what I would like to see:

Security Max_Bprice Bprice_Size Bprice_SourceCode Min_Aprice Aprice_Size Aprice_SourceCode

SecA 101.32 5 SourceT 87.11 4 SourceA, SourceT
SecH 100.2 3 SourceJ 89.43 2 SourceW
SecS 50.56 2 SourceX 19.33 SourceX
SecZ 16.98 3 SourceQ 87.98 3 SourceQ

What is the sql query to do this please?

This is what I have started with but it is not correct...

select
Security,
max(Bprice) as 'Max_Bprice',
SourceCode as 'Bprice_SourceCode',
min(Aprice) as 'Min_Aprice',
SourceCode as 'Aprice_SourceCode'
from
tblData
group by
Security,
SourceCodeHi

You are almost there but not quite. Could you provide your sample data as point 3 here please:
http://www.dbforums.com/showthread.php?t=1196943

Also, your DDL does not match the data you have supplied.

Not really related, but there looks to be a third normal form issue here.

Cheers|||http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=92298|||Here it is.
Thanks

DECLARE @.Sample TABLE (ID INT, SourceID INT, SourceCode VARCHAR(20), Security VARCHAR(20), Bprice MONEY, BpriceSize INT, Aprice MONEY, ApriceSize INT, ImportDate DATETIME)

INSERT @.Sample
SELECT 1, 1, 'sourceA', 'SecA', 100.2 , 2, 99.12, 1, '2007-11-07 16:24:31.297' UNION ALL
SELECT 2, 2, 'sourceW', 'SecH', 95.7 , NULL, 89.43, NULL, '2007-11-07 16:24:31.297' UNION ALL
SELECT 3, 3, 'SourceX', 'SecS', 50.56, 1, 76.44, 4, '2007-11-07 16:24:31.297' UNION ALL
SELECT 4, 4, 'SourceQ', 'SecZ', 87.98, NULL, NULL, NULL, '2007-11-07 16:24:31.297' UNION ALL
SELECT 5, 5, 'SourceJ', 'SecH', 100.2 , NULL, 99.12, 2, '2007-11-07 16:24:31.297' UNION ALL
SELECT 6, 6, 'SourceK', 'SecU', NULL, NULL, NULL, NULL, '2007-11-07 16:24:31.297' UNION ALL
SELECT 7, 7, 'SourceT', 'SecA', 50.56, 3, 87.11, NULL, '2007-11-07 16:24:31.297' UNION ALL
SELECT 8, 1, 'sourceA', 'SecA', 100.2 , 6, 99.12, 2, '2007-11-07 16:26:15.123' UNION ALL
SELECT 9, 2, 'sourceW', 'SecH', 99.54, 4, 89.43, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 10, 3, 'SourceX', 'SecS', 50.56, 2, 19.33, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 11, 4, 'SourceQ', 'SecZ', 16.98, NULL, 87.98, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 12, 5, 'SourceJ', 'SecH', 100.2 , 1, 99.12, 2, '2007-11-07 16:26:15.123' UNION ALL
SELECT 13, 6, 'SourceK', 'SecU', NULL, NULL, NULL, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 14, 7, 'SourceT', 'SecA', 50.56, 2, 87.11, 1, '2007-11-07 16:26:15.123' UNION ALL
SELECT 15, 1, 'sourceA', 'SecA', 100.2 , 1, 87.11, 1, '2007-11-07 16:26:15.123' UNION ALL
SELECT 16, 2, 'sourceW', 'SecH', 99.66, NULL, 89.43, 2, '2007-11-07 16:26:15.123' UNION ALL
SELECT 17, 3, 'SourceX', 'SecS', 50.56, 2, 19.33, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 18, 4, 'SourceQ', 'SecZ', 16.98, 3, 87.98, 3, '2007-11-07 16:26:15.123' UNION ALL
SELECT 19, 5, 'SourceJ', 'SecH', 100.2 , 3, 99.12, 3, '2007-11-07 16:26:15.123' UNION ALL
SELECT 20, 6, 'SourceK', 'SecU', NULL , NULL, NULL, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 21, 7, 'SourceT', 'SecA', 101.32, 5, 87.11, 3, '2007-11-07 16:26:15.123'|||I'll leave it to Peso. He'll get it soon enough I would imagine.|||Thanks anyway|||I suggest that you break it up

Do one, then the other, then combine them

I think you can do a union or a join of 2 derived table.

Since each derived table is going to be a single row, you wont have to worry about a cartesian product