3

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
Parveez Ahmed
  • 1,325
  • 4
  • 17
  • 28

4 Answers4

2

Get the highest price of each group, you could do like below:

SELECT T1.*
FROM (
    SELECT
     MAX(price) AS max_price,
     sender_id
    FROM items
    GROUP BY sender_id
) AS T2
INNER JOIN items T1 ON T1.sender_id = T2.sender_id AND T1.price = T2.max_price
WHERE T1.receiver_id=1 
ORDER BY T1.price
xdazz
  • 158,678
  • 38
  • 247
  • 274
1

Try this: 

SELECT i.sender_id, i.goods_id 
FROM items i 
INNER JOIN (SELECT i.sender_id, MAX(i.price) AS maxPrice
            FROM items i WHERE i.receiver_id=1 
            GROUP BY i.sender_id
           ) AS A ON i.sender_id = A.sender_id AND i.price = A.maxPrice
WHERE i.receiver_id=1

OR

SELECT i.sender_id, i.goods_id 
FROM (SELECT i.sender_id, i.goods_id 
      FROM (SELECT i.sender_id, i.goods_id 
            FROM items i WHERE i.receiver_id=1 
            ORDER BY i.sender_id, i.price DESC
           ) AS i 
      GROUP BY i.sender_id
     ) AS i
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
  • Second part is giving the output,but first part gives an error,thanks anyway with my vote for this! – Parveez Ahmed Jan 09 '14 at 05:35
  • @rosemary You're most welcome... Check updated answer. Now both query will work. I had added `comma` after `MAX(price) AS maxPrice,`, it was a typo error. I had corrected it. I prefer use First methoid – Saharsh Shah Jan 09 '14 at 05:38
  • Thanks!It was corrected,but the first part doesn't give the expected output. Anyway,which method is better in terms of speed? @Saharsh Shah – Parveez Ahmed Jan 09 '14 at 05:43
  • @rosemary As per me with `JOIN` is better method than any other. I would prefer First Method using `JOIN` – Saharsh Shah Jan 09 '14 at 05:47
0
select distinct (sender_id,goods_id) from items where receiver_id=1 order by price desc;

you can use like this.

Muhammad Essa
  • 142
  • 1
  • 10
0

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
Tin Tran
  • 6,194
  • 3
  • 19
  • 34
  • I don't understand the use of 1 in (select 1 from items...). Could you have some tips for me? @tin tran – Parveez Ahmed Jan 09 '14 at 06:00
  • you can select anything there. Because exists checks if that select returns any rows at all. Not Exists makes sure there's no rows in that subquery where the ids are the same as t1 and the price is higher. You can select 1 or select 2 or select 0 or select sender_id anything you like. So the query is basically saying where there doesn't exists a row (with same ids and a higher price). – Tin Tran Jan 09 '14 at 06:47
  • well... @tin tran we can also use * instead of 1 or 0 or 2..? but then it will just be an overhead, right? – Parveez Ahmed Jan 09 '14 at 08:03