CODEWITHSUNDEEP
oracle-database
bry888
bry888

Reputation: 297

oracle select rows where one column depends on two values in second column

I have data with users bidding for items (lets say sorted by time asc):

item_id, win, user_id
------------------
1, 0, 1
1, 0, 2
1, 1, 1
2, 0, 3
2, 0, 4
2, 1, 3

I need to select only these users, who lost in bidding for an item. So for the item_id = 1: user nr 1 lost because user nr 2 bidded higher and then user nr 2 lost too because user nr 1 bidded higher. For this item SELECT should return user_id = 2.

How can I select all such users in a smart way?

Upvotes: 0

Views: 763

Answers (2)

MT0
MT0

Reputation: 168623

SELECT   item_id, user_id
FROM     your_table
GROUP BY item_id, user_id
HAVING   MAX( win ) = 0;

Upvotes: 2

Oto Shavadze
Oto Shavadze

Reputation: 42853

Change here "bids" with your table name

select  t.item_id, t.user_id  from (
    select item_id, user_id 
    from bids
    group by item_id, user_id 
) t
left join (select item_id, user_id from   bids   where win = 1 ) winners
ON
t.item_id = winners.item_id
AND
t.user_id = winners.user_id
WHERE 
winners.item_id IS NULL

Upvotes: 0

Related Questions