Select only one row while doing groupby in mysql

Question

T have the following table:

mid pid   price
1   100   10
1   200   10
1   300   10
1   400   10
2   500   20
2   600   30
2   700   20
3   800   40
3   900   50

I want to find the least priced pid for each mid.

for that I was querying this code.

SELECT t1.mid,t1.pid 
FROM tableName t1
JOIN (
  SELECT mid, min(price) as min_price
  FROM tableName
  GROUP BY mid
) as t2 on t1.mid = t2.mid and t1.price = t2.min_price;

Ideally it should give result, but in my case in each group there are multiple pid having same price. so it is printing all the result.

but I just want to limit 1 row for each mid.

Is there any way to do this? I have a sqlfiddle demo

since pid,mid combinations can be duplicated, you need to have an separate primary key in order to make this work — e4c5, Dec 28 '16 at 09:09
@e4c5 sir if i could understand you correctly, i should add a primary key while creating table itself, and if so on what column. Please see my SQLFIDDLE demo — Shubham R, Dec 28 '16 at 09:12
since there are multiple pids for price=10 which one do you want? — e4c5, Dec 28 '16 at 09:13
Is this like "SELECT mid, min(price) from tableName group by mid" — Rahul, Dec 28 '16 at 09:15
@e4c5 i want the first pid which has price= 10, for mid = 1, and similary for every other mid — Shubham R, Dec 28 '16 at 09:15
yes, I think this is what you want. SELECT mid,pid, min(price) from tableName group by mid — Saadi, Dec 28 '16 at 09:19
Check [this answer](http://stackoverflow.com/a/28090544/4265352) on a [similar question](http://stackoverflow.com/q/12102200/4265352). — axiac, Dec 28 '16 at 09:25
@VforVendetta @Saadi your suggestions are invalid SQL. Up to version 5.7.5, MySQL accepts them but they don't provide the expected results. What should they return as `pid` when `mid = 2`? (`500` or `700`?) MySQL reserves its right to pick any of these values, indeterminately. — axiac, Dec 28 '16 at 09:29
@dhruvjadia It's the same query as suggested by Saadi and V for Vendetta; see my comment above. If you insert the rows in the table in reverse order you get a different result on the `SELECT` query. — axiac, Dec 28 '16 at 09:34
@dhruvjadia Not working http://www.sqlfiddle.com/#!2/77de6/1 — Shubham R, Dec 28 '16 at 09:39
@SRingne: is it correct changed reverse order http://www.sqlfiddle.com/#!2/6b195/2/0 — dhruv jadia, Dec 28 '16 at 09:42
@dhruvjadia make the first row's price to 40 instead of 10 and run again — Shubham R, Dec 28 '16 at 09:52

score 1 · Accepted Answer · answered Dec 28 '16 at 10:00

1

select s.mid,s.pid,s.price
from
(
SELECT t1.mid,t1.pid,t1.price,
        if (t1.mid <> @p, @rn:=1,@rn:=@rn+1) rn,
        @p:=t1.mid p
FROM (select @rn:=0,@p:=0) rn,t t1 
order by t1.mid,t1.price,t1.pid
) s where s.rn = 1

answered Dec 28 '16 at 10:00

P.Salmon

17,104
2
12
19

table name not defined?? – Shubham R Dec 28 '16 at 10:21
Running your query gives `table 'db_2_36b118.t' doesn't exist` – Shubham R Dec 28 '16 at 10:23
Substitute my table name t with your table name. – P.Salmon Dec 28 '16 at 10:28

score 0 · Answer 2 · answered Dec 28 '16 at 09:51

0

TRY THIS: http://www.sqlfiddle.com/#!2/e2244c/71

SELECT t1.mid, MIN(DISTINCT t1.pid) AS Expr1
FROM            tableName as t1
INNER JOIN
tableName AS tableName_1 ON t1.mid = tableName_1.mid
GROUP BY t1.mid
ORDER BY MIN(DISTINCT t1.pid) DESC

answered Dec 28 '16 at 09:51

Edss

13
8

you changed your schema. I think you already resolved your question. good for you. – Edss Dec 28 '16 at 10:32

score 0 · Answer 3 · answered Dec 28 '16 at 12:21

Try this:

select t1.*
from tablename t1
inner join (select mid, min(pid) min_pid from
(select t1.mid,t1.pid
FROM tablename t1
inner join (select mid,
    min(price) as min_price
    FROM tablename
    GROUP BY mid) t2 on t1.mid = t2.mid
and t1.price = t2.min_price) t
group by mid) t2 on t1.mid = t2.mid
and t1.pid = t2.min_pid
;

It produces rows with minimum price and minimum pid.

Jiang · Answer 4 · 2016-12-28T11:51:03.753

-1

It's works:

SELECT t1.mid,t1.pid FROM tableName t1 
INNER JOIN ( 
     SELECT mid AS mid1, pid AS pid1, min(price) as min_price FROM  tableName GROUP BY mid 
) as t2
on t1.mid = t2.mid1 and t1.price = t2.min_price and t1.pid = t2.pid1;

edited Dec 28 '16 at 11:51

answered Dec 28 '16 at 09:35

Jiang

51
7

As mentioned several times in the comments, this works in mysql 5.6 and older version. It's not valid SQL – e4c5 Dec 28 '16 at 09:36
@Jiang Check your query on this http://www.sqlfiddle.com/#!2/77de6/2 not working – Shubham R Dec 28 '16 at 09:41
@SRingne I have modified, you can test – Jiang Dec 28 '16 at 11:52

Select only one row while doing groupby in mysql

4 Answers4