Print all column values for a record that has a mi

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

General SQL Server Forums

New to SQL Server Programming

Print all column values for a record that has a mi

Author

Topic

sangeeta
Starting Member

16 Posts

Posted - 2007-05-10 : 12:34:10

Hi All -

I have been killing myself over this -

For the attached table,I want to retrieve records that have a minimum Price for all the groups of Category, Symbol, Date

Category Symbol Date Price Coupon
1 IBM 01/05/2006 100 5
1 IBM 01/05/2006 99 5.8
1 IBM 01/06/2006 100 5
1 IBM 01/06/2006 96 10
2 IBM 01/05/2006 102 6
2 IBM 01/05/2006 105 5.7

For a given symbol, date and Category, I want to pull in records with the min(Price). My problem is that if I say,

Select min(Price)
,symbol
,date
,Category from TEST group by symbol, date, Category

I lose the coupon information that is attached to the record that has minimum price and maximum price within the group of symbol, date and Category

My results from the above table should be –
Category Symbol Date Price Coupon
1 IBM 01/05/2006 99 5.8
1 IBM 01/06/2006 96 10
2 IBM 01/05/2006 102 6

Thanks for all your help.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-10 : 12:51:31

[code]
Declare @t table (Category int, Symbol varchar(5), Date datetime, Price decimal(10,2), Coupon decimal(10,2))
insert into @t
select 1, 'IBM', '01/05/2006', 100, 5 union all
select 1, 'IBM', '01/05/2006', 99, 5.8 union all
select 1, 'IBM', '01/06/2006', 100, 5 union all
select 1, 'IBM', '01/06/2006', 96, 10 union all
select 2, 'IBM', '01/05/2006', 102, 6 union all
select 2, 'IBM', '01/05/2006', 105, 5.7

Select t1.*, t2.coupon
from (
select Category, symbol, date, min(price) as minprice
from @t t
group by category, symbol, date
) t1
join @t t2
on t1.Category = t2.category and t1.Symbol = t2.symbol and t1.Date = t2.date and t1.minPrice = t2.price

[/code]

EDIT: Removed the Left join. The code will work just fine with INNER JOIN.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

nr
SQLTeam MVY

12543 Posts

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

select t.*
from TEST t
join (Select Price = min(Price)
,symbol
,date
,Category from TEST group by symbol, date, Category) t2
on t.Price = t2.Price
and t.symbol = t2.symbol
and t.date = t2.date
and t.Category = t2.Category

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sangeeta
Starting Member

16 Posts

Posted - 2007-05-10 : 13:32:53

[Thanks a lot]Originally posted by sangeeta

Hi All -

I have been killing myself over this -

For the attached table,I want to retrieve records that have a minimum Price for all the groups of Category, Symbol, Date

Category Symbol Date Price Coupon
1 IBM 01/05/2006 100 5
1 IBM 01/05/2006 99 5.8
1 IBM 01/06/2006 100 5
1 IBM 01/06/2006 96 10
2 IBM 01/05/2006 102 6
2 IBM 01/05/2006 105 5.7

For a given symbol, date and Category, I want to pull in records with the min(Price). My problem is that if I say,

Select min(Price)
,symbol
,date
,Category from TEST group by symbol, date, Category

I lose the coupon information that is attached to the record that has minimum price and maximum price within the group of symbol, date and Category

My results from the above table should be –
Category Symbol Date Price Coupon
1 IBM 01/05/2006 99 5.8
1 IBM 01/06/2006 96 10
2 IBM 01/05/2006 102 6

Thanks for all your help.

[/quote]

sangeeta
Starting Member

16 Posts

Posted - 2007-05-10 : 13:33:36

Thanks for all the help.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-10 : 13:56:35

well..now you can live happily...

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources