Query that will require self joins

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 2000 Forums

Transact-SQL (2000)

Query that will require self joins

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-12-22 : 08:16:24

Dustin Michaels writes "Hello friends.

I have the table (tbCoupon)with the following three colums.

tbCoupon.intCouponID
tbCoupon.intItemID,
tbCoupon.mnyDiscountAmount

I need a query that returns the intCouponID that has the greatest mnyDiscountAmount which is grouped by intItemID.

So if the coupon table has 10 distinct intItemID values this result set should include 10 intCouponID's

I'm guessing that some self joins would be required but I can't figure out the correct procedure!

Dustin Michaels"

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-12-22 : 09:02:04

[code]
SELECT intCouponID, intItemID FROM
tbCoupon INNER JOIN
(
SELECT intItemID, MAX(mnyDiscountAmount) AS MaxDiscount
FROM tbCoupon
GROUP BY intItemID) A
ON A.intItemID = tbCoupon.ItemID
AND A.MaxDiscount = tbCoupon.mnyDiscountAmount
[/code]

This might return duplicates, how you handle them is upto you.

Owais

We make a living out of what we get, but we make a life out of what we give.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-22 : 10:10:16

Here is another option which might work for you:

select c.* from tbCoupon c
join 
(
	select top 1 intCouponID 
	from tbCoupon
	order by mnyDiscountAmount desc
) a
on a.intCouponID = c.intCouponID

EDIT: Changed order by clause

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources