Deduplicate Statement in SQL

[roninpl]

Hi:

Is there a way to run a query that dedupicate the values in the table?

For example:
Table: Order
Columns: Company, Item, Date,

Company Item Date
A Apple 07/16/06
B Berry 07/17/06
C Cherry 07/18/06
A Apple 07/20/06

Notice that Company A is duplicate and I would like the query with the following return:

Company Item Date
A Apple 07/16/06
B Berry 07/17/06
C Cherry 07/18/06

I have try the Distinct statement but only Company column is returned in the query. (I need the first instance of company A with Item and Date listed.). Thanks.

Roninpl

 

[PHV]

SELECT A.Company, A.Item, A.Date
FROM Order A INNER JOIN (
SELECT Company, MIN(Date) FirstDate FROM Order GROUP BY Company
) B ON A.Company = B.Company AND A.Date = B.FirstDate

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

 

[roninpl]

Thanks...Now what if I added an Amount column and a new row?

Company Item Date Amount
A Apple 07/16/06 $2
B Berry 07/17/06 $3
C Cherry 07/18/06 $4
A Apple 07/20/06 $2
B Berry 07/21/06 $3

I still would like my result to display the following.
Company Item Date Amount
A Apple 07/16/06 $2
B Berry 07/17/06 $3
C Cherry 07/18/06 $4

 

[PHV]

Where are you stuck ?
Simply add A.Amount in the SELECT list.