473,241 Members | 1,769 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,241 software developers and data experts.

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 1867
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Gemini | last post by:
Hello I am looking for the best content/article manager software, perferred open source, php, mysql backend.. can anyone recommend me one? I think that the best discussion is phpbb, the...
18
by: Roman Suzi | last post by:
;-) Just type into google "best programming language" and press (I am lucky) Sincerely yours, Roman Suzi -- rnd@onego.ru =\= My AI powered by GNU/Linux RedHat 7.3
24
by: wm2004 | last post by:
Which is the best C++ Compiler? Get An Online Business and Make Money! Learn the secrets of many ordinary people who quit their day jobs to pursue an online business. There are many affiliate...
15
by: John J | last post by:
I've written the following code into a class to search for and display the results of all races entered (The complete code is in a previous thread). I wish to amend the code so as to display the...
136
by: Matt Kruse | last post by:
http://www.JavascriptToolbox.com/bestpractices/ I started writing this up as a guide for some people who were looking for general tips on how to do things the 'right way' with Javascript. Their...
3
by: Irene | last post by:
Hi all, I have set up a simple VB program (and later on an ASP interface) to manage an Athletics database. I'm using Access 2000. To simplify, I have the Athlets, the Competitions and the...
5
by: l.woods | last post by:
I want your recommendation on which ASP.NET Shopping Cart software I should buy? Best code Best documentation Best support (if needed. I will buying source code, if possible) TIA, Larry...
24
by: Earl | last post by:
I have all of my data operations in a separate library, so I'm looking for what might be termed "best practices" on a return type from those classes. For example, let's say I send an update from...
9
by: =?Utf-8?B?QW1tZXI=?= | last post by:
I've read many incomplete opinions about the "Best Practice" for securely accessing SQL but what I really need to find the "Best Practice" that fits my applications needs. Currently (alpha...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.