1

This table stores data for who bought which book and when. I want to know which books were bought first by USERID #1. SQL fiddle

That would result in

USERID  BOOKID  DATE_PURCHASE
1       2       2014-03-22 (purchased only by USERID #1)
1       5       2014-03-29 (purchased earlier than USERID #2)
1       6       2014-03-28 (purchased earlier than USERID #3)
1       7       2014-03-26 (purchased earlier than USERID #3)

It doesn't show BOOKID #3 because that book was purchased first by USERID #2.

Please note the table may consist tens of thousands of rows so I need an efficient solution.

If had to do it manually I would do this:

1. Check what books did USERID #1 buy
2. Go through all rows with those books (BOOKID) and check the MIN(DATE_PURCHASE) for each row
3. Write out those rows only where the USERID = #1
erdomester
  • 11,789
  • 32
  • 132
  • 234
  • I don't understand what you want to achieve. By `this` and `this` is too broad, pls specify your need. – Mr.Web Apr 05 '15 at 08:57
  • What if user #1 bought the book on the same day as user #2 (and nobody bought it earlier than that)? – eggyal Apr 05 '15 at 09:00

1 Answers1

0

You want to filter a group-wise minimum, which itself is formulated using a self-join (in order to find the date of the first purchase of only those books bought by the desired user, irrespective of the purchaser):

SELECT * FROM docs NATURAL JOIN (
  SELECT   BOOKID, MIN(d2.DATE_PURCHASE) DATE_PURCHASE
  FROM     docs d1 JOIN docs d2 USING (BOOKID)
  WHERE    d1.USERID = 1
  GROUP BY BOOKID
) t WHERE USERID = 1

See it on sqlfiddle.

eggyal
  • 122,705
  • 18
  • 212
  • 237