Suppose,I have a table named items:
sender_id receiver_id goods_id price
2 1 a1 1000
3 1 b2 2000
2 1 c1 5000
4 1 d1 700
2 1 b1 500
Here I want to select the sender_id,goods_id in descending order of price from the items table such that no row appears more than once which contains the same sender_id value (here sender_id 2). I used the following query,but was in vain:
select distinct sender_id,goods_id from items where receiver_id=1 order by price desc
The result shows all the five tuples(records) with the tuples containing sender_id 2 thrice in descending order of time.But what I want is to display only three records one of them having sender_id of 2 with only the highest price of 5000. What should I do? My expected output is:
sender_id goods_id
2 c1
3 b2
4 d1
please try this
select sender_id,goods_id from items t1
where not exists (select 1 from items t2
where t2.sender_id = t1.sender_id
and t2.receiver_id = t1.receiver_id
and t2.price > t1.price)
and receiver_id = 1
order by price desc
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments