1

Today I have posted a question and got a good answer: Stuck in building mysql query.

I though it helped me, but I've discovered that it returns wrong data. So I'm reposting the question here, with an answer I received, as well I will explain the problem why it is not working for me.

Example of data:

id | item_id | user_id | bid_price
----------------------------------
 1 |    1    |   11    |     1
 2 |    1    |   12    |     2
 3 |    1    |   13    |     3
 4 |    1    |   14    |     1
 5 |    1    |   15    |     4
 6 |    2    |   16    |     2
 7 |    2    |   17    |     1
 8 |    3    |   18    |     2
 9 |    3    |   19    |     3
10 |    3    |   18    |     2

Expected result:

id | item_id | user_id | bid_price
----------------------------------
 1 |    1    |   11    |     1
 7 |    2    |   17    |     1
 8 |    3    |   18    |     2

Offered solution:

select m.id, m.item_id, m.user_id, m.bid_price
from my_table m 
inner join ( 
select item_id, min(id) min_id,  min(bid_price) min_price
from my_table 
where   item_id IN (1,2,3)
group by item_id 
) t on t.item_id = m.item_id 
   and t.min_price= m.bid_price
   and t.min_id = m.id

The problem: In the sub query the minimum ID is selected entire the group by (item_id) statement and doesn't reflects according to minimum bid_price. In other words, the minimum id is selected not depending on the price field at all. So, in the result I will get minimum price and minimum id of the group, but this will not be the same row! The id can be related to the row with another bet_price value.

How this query can be adjusted? Thank you in advance!

Sergej
  • 2,030
  • 1
  • 18
  • 28
  • please show me the actual result .. – ScaisEdge Mar 26 '18 at 12:53
  • 6
    Please [edit] your question title to something meaningful.This is your third question today. Neither *Stuck with building mysql query* or *stuck with mysql query* are going to be of any use to future readers here who see them in a list of search results. Your title should describe a clear problem or question, and neither of those describes anything that has meaning. – Ken White Mar 26 '18 at 12:56
  • Done. Thank you for hints! – Sergej Mar 26 '18 at 13:13

2 Answers2

2
SELECT min(m.id) AS  id, m.item_id, m.user_id, m.bid_price
FROM my_table m 
INNER JOIN ( 
SELECT item_id,  min(bid_price) AS min_price
FROM my_table 
GROUP BY item_id 
) t ON t.item_id = m.item_id 
   AND t.min_price= m.bid_price
   GROUP BY item_id

Output

id  item_id user_id bid_price
1   1       11      1
7   2       17      1
8   3       18      2

Live Demo

http://sqlfiddle.com/#!9/a52dc6/13

Jay Shankar Gupta
  • 5,918
  • 1
  • 10
  • 27
  • Works like a charm, thank you. I'll add the WHERE condition with item_ids - not a big deal. – Sergej Mar 26 '18 at 13:07
  • Hi, I am having a similar issue, thanks for the query. Could you explain why you have used min(m.id) also why groupby(item_id) at the end ? – joby george May 31 '23 at 11:50
0
SELECT DISTINCT
  t1.item_id,
  t1.bid_price
FROM tab1 t1
WHERE NOT exists(SELECT 1
                 FROM tab1 t2
                 WHERE t2.item_id = t1.item_id
                       AND t2.bid_price < t1.bid_price)
      AND t1.item_id IN (1, 2, 3);

http://sqlfiddle.com/#!9/615e0a/5

Mark
  • 418
  • 3
  • 12