Apply DISTINCT to only one column, not to the rest

[sbayter]

Hi,
I have access db and I'm trying to display a distinct item_ID, the primaryKey, and price for the corresponding record.
This is the query I'm using.

SELECT DISTINCT item_ID, primaryKey, min(price) FROM products WHERE status = 'NEW' GROUP BY item_ID

The problem is that is gives me a huge result and it should only give me one. Since the primaryKey is unique it is printing all of them, i just want the DISTINCT to apply to the item_ID and not to the price or primaryKey.
If I take these 2 out, it works.
Any ideas.
Thanks,

sbayter

 

[ralphtrent]

can you try
SELECT DISTINCT(item_ID), primaryKey, min(price) FROM products WHERE status = 'NEW' GROUP BY item_ID

 

[shaddow]

That wont work in MSAccess, not shure if works in SQL also.
But the thing is that what sbayter wanna do is not actually corect or maybe i misunderstood.

If you wanna knw the item_ID wich could be same for more records and the primaryKey wich is diferent for each record... then if you want to display just 1 item_ID why you would need the primaryKey for? cuz you wont knw wich item_ID would be in the thousands you have unless you gonna display all of them withc theyr coresponding primaryKey.

This is kinda weird what you wanna do, or maybe you didnt explained better.

________
George, M