Mysql select min in group of rows where userid=? -

Mysql select min in group of rows where userid=? -

this table stores info bought book , when. want know books bought first userid #1. sql fiddle

that result in

userid bookid date_purchase 1 2 2014-03-22 (purchased userid #1) 1 5 2014-03-29 (purchased before userid #2) 1 6 2014-03-28 (purchased before userid #3) 1 7 2014-03-26 (purchased before userid #3)

it doesn't show bookid #3 because book purchased first userid #2.

please note table may consist tens of thousands of rows need efficient solution.

if had manually this:

1. check books did userid #1 purchase 2. go through rows books (bookid) , check min(date_purchase) each row 3. write out rows userid = #1

you want filter group-wise minimum, formulated using self-join (in order find date of first purchase of books bought desired user, irrespective of purchaser):

select * docs natural bring together ( select bookid, min(d2.date_purchase) date_purchase docs d1 bring together docs d2 using (bookid) d1.userid = 1 grouping bookid ) t userid = 1

see on sqlfiddle.

mysql greatest-n-per-group