Jet Sql Statement - Select Distinct Multiple Columns 1

[Swi]

Is this the best way to select multiple columns from a table that has a distinct order ID field?

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

Thanks.

Swi

 

[Andrzejek]

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

 

[Swi]

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

 

[Andrzejek]

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

 

[Swi]

Thanks.

Swi