470,581 Members | 2,460 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,581 developers. It's quick & easy.

Is GROUP_BY best for this?

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
3 1812
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
:)
Jul 19 '05 #3
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.

Similar topics

7 posts views Thread by Gemini | last post: by
18 posts views Thread by Roman Suzi | last post: by
24 posts views Thread by wm2004 | last post: by
15 posts views Thread by John J | last post: by
136 posts views Thread by Matt Kruse | last post: by
5 posts views Thread by l.woods | last post: by
1 post views Thread by livre | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.