I need to display non duplicate data from ONE table

[metaman]

I have on table with the following data:
Item Qty
Pencil 12
Eraser 3
Pencil 5
Pencil 4
Pen 3
Paint 3
Pen 3

I want to list out both Item and Qty but only the rows where the Item is not duplicated. The desired result will be:

Pencil 12
Eraser 3
Pen 3
Paint 3

 

[PHV]

Are you sure uou don't wanted this ?
SELECT Item, SUM(Qty) FROM yourTable GROUP BY Item

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

 

[metaman]

No this is not what I wanted but thanks for the effort. I want something like:

SELECT Item, Qty
FROM MyTable
WHERE Item = (SELECT DISTINCT Item FROM MyTable)

but this does not work

 

[PHV]

And which Qty field you want ?

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

 

[metaman]

It does not mater which qty field, the first occurance is good enough

 

[PHV]

SELECT Item, Min(Qty) FROM yourTable GROUP BY Item

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

 

[metaman]

This Is just what I wanted thanks a lot, m8