ACCESS DUPLICATES

[mp3er3000]

I need to use Distinct to return unique records by order number but I also need to return the rest of fields that are not part of the Distinct. For example:

SELECT Num, Type, Customer FROM Order_Table

Num Type Customer
------ ------ ------------
1 YY 1001 ABC
1 AA 1001 ABC
2 SD 2001 FFF
3 CC 5000 DDD
3 BB 5000 DDD


I would like to use Select Distinct by number but return more than just the number field. Type and Customer also need to be returned like below example:

Num Type Customer
------ ------ ------------
1 YY 1001 ABC
2 SD 2001 FFF
3 CC 5000 DDD

Is there a way to solve this? Thank you in advance!!

 

[PHV]

If you don't care which type is returned:
SELECT Num, Max(Type) AS SomeType, Max(Customer) AS SomeCustomer
FROM Order_Table
GROUP BY Num;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mp3er3000]

PHV,
can you please explain how does this work and can i have more than 4 colums? perhaps, 40 colums???
thanks alot !

 

[LittleSmudge]

SQL is very LOGICAL and it will do just what you tell it to do. Which is not always the same as what you WANT it to do.

If you have
[tt]
Num Type Customer
--- ---- --------
1 YY 1001 ABC
1 AA 1001 ABC[/tt]

and you want to get
[tt]
Num Type Customer
--- ---- --------
1 YY 1001 ABC[/tt]

SQL insists that you tell it EXPLICITLY how to decide between YY and AA

The various option include Min(), Max(), First(), Last() or for numeric fields you could consider Avg() StDev(), Var()

PHV has chosen Max() which is, I admit, the most likely choice.

But once you've found the Max([Type]) then it is no longer valid to consider it a [Type] so it gets renamed using the AS clause.

Just keep repeating
Max(field5) As MaxOf5, Max(field6) As MaxOf6, etc.. .. ..


'ope-that-'elps.


'ope-that-'elps

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.