Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Jet Sql Statement - Select Distinct Multiple Columns 1

Status
Not open for further replies.

Swi

Programmer
Feb 4, 2002
1,963
US
Is this the best way to select multiple columns from a table that has a distinct order ID field?

Code:
SELECT Master.[OrderID], Max(Master.[customer]) AS MaxOfcustomer, Max(Master.[quotedPrice]) AS MaxOfquotedPrice
FROM Master
GROUP BY Master.[OrderID];

Thanks.

Swi
 
If you have 'a distinct order ID field', then your data looks something like:

[pre]
OrderID customer quotedPrice
1 23 43
2 23 21
3 23 67
4 45 53
5 76 75
...
[/pre]
and your Select statement does not make sense since you will get all records back.
There will be no MAX() when you GROUP BY OrderID, since OrderID is district/unique

If your OrderID is NOT unique, then this data:

[pre]
OrderID customer quotedPrice[blue]
1 23 43
1 33 21
1 23 67[/blue]
4 45 53
5 76 75
...[/pre]

would at least return:
[pre][blue]
1 33 67[/blue]
[/pre]

when GROUP BY OrderID

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Hi,

Correct, my OrderID is NOT unique. Is there a more optimized way to do the query I posted or would you think this is a good approach?

Thanks.

Swi
 
If that's what you need, and it is working the way you want, then 'this is a good approach'

I would just do this a lot shorter (eliminate superfluous pieces):
[tt]
SELECT OrderID, Max(customer) AS MaxOfcustomer, Max(quotedPrice) AS MaxOfquotedPrice
FROM Master
GROUP BY OrderID; [/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top