By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,781 Members | 1,255 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,781 IT Pros & Developers. It's quick & easy.

Is GROUP_BY best for this?

P: n/a
Given table all_prices:

product_id | company_id | price
-----------+------------+------
1 0 1
1 6 2
2 0 3
4 6 4
5 0 5
5 6 6

I would like to display the following...

product_id | price
-----------+------
1 2
2 3
4 4
5 6

I have looked at using GROUP BY, but don't know how to specify that I
want to return the price for company_id=6, or failing that the price for
company_id = 0.

How would you structure this query?

John
Jul 19 '05 #1
Share this Question
Share on Google+
3 Replies

P: n/a
I think, you need to perform a FULL JOIN for this table on itself,
requiring the same product and different companies.

So you get all possible pairs of rows with the same product and different
companies.
Also, if the product is missing in one of the companies than the
corresponding fields are NULL.

SELECT *
FROM all_prices p1 LEFT JOIN all_prices p1 ON (p1.product_id=p2.product_id)

Then you need to get rid of duplicate rows.

SELECT p1.product_id,p1.price
FROM all_prices p1 LEFT JOIN all_prices p1 ON (p1.product_id=p2.product_id)
WHERE p1.company_id>p2.company_id OR p2.company_id is NULL

I think this should work, but I haven't tried it.
Also, you might wanna think it over if there are more than two companies....

"John" <no@email> wrote in message
news:42***********************@news.zen.co.uk...
Given table all_prices:

product_id | company_id | price
-----------+------------+------
1 0 1
1 6 2
2 0 3
4 6 4
5 0 5
5 6 6

I would like to display the following...

product_id | price
-----------+------
1 2
2 3
4 4
5 6

I have looked at using GROUP BY, but don't know how to specify that I want
to return the price for company_id=6, or failing that the price for
company_id = 0.

How would you structure this query?

John


Jul 19 '05 #2

P: n/a
:)
Jul 19 '05 #3

P: n/a
Hi John,

I'm not positive that grouping is what you need here. All of the grouping
examples I've ever seen (which isn't that many) were usually used in
conjunction with some aggregate operator.

Based on your desired output, I "think" you might be able to use a
correlyated nested query maybe like:

SELECT ap1.product_id, ap1,price
FROM all_prices ap1
WHERE ap1.price = (
SELECT max(ap2.price)
FROM all_prices ap2
WHERE ap2.product_id = ap1.product_id
)

I'm just rattling that off without actually checking it myself, but I
"think" the solution you need may be similar to that.

Hope this helps.

w.k.
On Fri, 25 Feb 2005, John wrote:
Given table all_prices:

product_id | company_id | price
-----------+------------+------
1 0 1
1 6 2
2 0 3
4 6 4
5 0 5
5 6 6

I would like to display the following...

product_id | price
-----------+------
1 2
2 3
4 4
5 6

I have looked at using GROUP BY, but don't know how to specify that I
want to return the price for company_id=6, or failing that the price for
company_id = 0.

How would you structure this query?

John

Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.