-2

Given an example of table:

id | item_id | user_id | bid_price
----------------------------------

The task is to select rows with minimum bid_price for each item_id in the provided set.

For example: item_id = [1, 2, 3] - so I need to select up to three (3) rows, having a minimum bid_price.

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

Actually, I'm using Symfony/Docine DQL, but it will be enough with a plain SQL example.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Sergej
  • 2,030
  • 1
  • 18
  • 28
  • Possible duplicate to https://stackoverflow.com/questions/13357144/mysql-how-to-select-data-where-a-field-has-a-min-value – Florian Mar 26 '18 at 11:26
  • 1. In my case the bid_price field can be the same, so need to select only one. 2. I want to select a minimum only within item_id, but not absolutely. – Sergej Mar 26 '18 at 11:28

3 Answers3

2

For the all the columns in the rows you could use a inner join on subselect for min bid price 

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

or .. if you have some float data type you could use a acst for unsigned

  select m.id, m.item_id, m.user_id, cast(m.bid_price as UNSIGNED) 
  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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • This query doesn't fit my needs. It selects more data than needed. If I provice 10 items ids, it should return maximum 10 rows. – Sergej Mar 26 '18 at 11:33
  • See, I am working with application, that allows to make the same bids, so in practice - there can be similar rows, where only the ID is different. I think your query will select all of them, but I need only one. And why did you add a limit? I don't need any limits, I am having a set of item_ids. for example you can use item_id IN (1,2,3) - will be ok. – Sergej Mar 26 '18 at 11:42
  • I have update the answer using in and not limit .. hope is what you are looking for ..otherwise update your question add a clear data sample and the expected result – ScaisEdge Mar 26 '18 at 11:46
  • Added an example in question. Thanks, your answer is closer to that what I need, but I also want to get only ONE row for item_id. Because the price can be the same for many rows. Thank you in advance! – Sergej Mar 26 '18 at 12:04
  • looking to your data seems you need also a min id (alias just one for each item_id) answer updated – ScaisEdge Mar 26 '18 at 12:06
  • I need only one for each item_id. The user must only be related to the minimum bet_price in the result. The minimum can be selected as MIN(ID) if there are several results. (in practice - there will be a date field for that, of course). – Sergej Mar 26 '18 at 12:12
  • I use min(id) for obtain a single rows when there is more than one min_price for user_id. .. i have update the answer with min_id – ScaisEdge Mar 26 '18 at 12:16
  • I think your query is working for me for 99%. There is a type problem now. The price in the row has a value, for example: 10000. but when you select min(bid_price) it returns 10000.00, and fails because of that. The price column type is float with 2 decimal points. I try to change and FORMAT(t.min_price, 2) = FORMAT(m.PRICE, 2) but this doesn't help me. – Sergej Mar 26 '18 at 12:20
  • i don't manage the format for the column so .im'not changing the content of the data .. – ScaisEdge Mar 26 '18 at 12:24
  • Please, check: https://stackoverflow.com/questions/49491597/mysql-float-type-issue – Sergej Mar 26 '18 at 12:33
1

You can use MIN() with GROUP BY in the query:

SELECT id, item_id, MIN(bid_price) AS min_bid, user_id 
FROM your_tbl 
GROUP BY item_id 
HAVING item_id in(1, 2, 3);
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
B. Desai
  • 16,414
  • 5
  • 26
  • 47
  • I need to select entire rows. This will not work using your example. – Sergej Mar 26 '18 at 11:29
  • Your query doesn't return the data I want to get. Yes, it returns the id, itemd_id, user_id and MIN(bid_price), but the MIN(bid_price) is not related to other row data. For example: I know that minimum price is in row with ID: 10, in your case it will have ID: 5, because it have the same item_id, but it doesn't relate with the MIN(). – Sergej Mar 26 '18 at 11:39
  • 1
    Will you post your data with expected output? – B. Desai Mar 26 '18 at 11:42
  • I will try to, bet your example still returns wrong data. It does not binds other fields with MIN(). IT takes the first ones, as I explained: id = 5, item_id = 1, price = 100 AND id = 10, items_id = 1, price = 50 - it will return id = 5, item_id = 1, MIN(price) = 50 - understand? expected id in this case is: 10 – Sergej Mar 26 '18 at 11:52
  • If `item_id=1` for both then my query definitely return row with minimum price. As I have already explained. You have to check your data once. Also its good if you provide your data and desired result – B. Desai Mar 26 '18 at 11:56
  • Added an example in my question. Your query WILL return the minimum price, but it WILL return a WRONG ID. if you will select a row with that ID then you will see that it WILL NOT HAVE minimum price! – Sergej Mar 26 '18 at 12:02
0

Use this query:

SELECT id, item_id, user_id, min(bid_price) as bid_price 
FROM YOUR_TABLE_NAME 
GROUP BY item_id;
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Abhishek Agarwal
  • 63
  • 9