CODEWITHSUNDEEP
sql
Alan3212
Alan3212

Reputation: 1

Distinct after query as result

Distinct values

Hi, I have table ITEM_PRICE, I'd like to get distinct item_id and then use WHERE price = 10

For now I have query

Select distinct item_id from item where price = 10

In result I get DISTINCT item_id where price = 10

I'd like to have first result of Distinct and then apply my Where. Because I need ITEM_ID which is only visible one time in DB and after find item with specific price.

Upvotes: 0

Views: 140

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Because I need ITEM_ID which is only visible one time in DB and after find item with specific price.

This sounds like you want GROUP BY and HAVING. The following returns items that appear exactly once with a price of 10:

select item_id
from item 
group by item_id
having count(*) = 1 and min(price) = 10;

If you wanted items that have only one price but could have multiple rows (with that price), you could tweak this to:

select item_id
from item 
group by item_id
having max(price) = min(price) and min(price) = 10

Upvotes: 1

Related Questions