0

I am trying to select the first occurrence from many rows by two keys which are date and shelf.checkID in my query.

 SELECT product.productID
        , product.Name
        , product.date
        , product.status 
    FROM product 
        INNER JOIN shelf ON product.sheldID=shelf.shelfID 
    WHERE shelf.checkID = $ID 
        AND product.date < '$day' 
        OR (product.date = '$day' AND shelf.expire <= '$time' ) 
    ORDER BY concat(product.date,shelf.expire) DESC

So far i have tried using group by:

(Select *
from (SELECT product.productID
                , product.Name
                , product.date
                , product.status 
            FROM product 
                INNER JOIN shelf ON product.sheldID=shelf.shelfID 
            WHERE shelf.checkID = $ID 
                AND product.date < '$day' 
                OR (product.date = '$day' AND shelf.expire <= '$time' ) 
            ORDER BY concat(product.date,shelf.expire) DESC) x
group by attendance.date AND shelf.checkID)

This is the data returned by the first query.

ProductID   Date          shelfID
1           2017-03-27    2
2           2017-03-27    2
3           2017-03-26    1
4           2017-03-25    3
5           2017-03-25    3
6           2017-03-25    4
7           2017-03-26    1

This is the output am trying to get.

ProductID   Date          shelfID
1           2017-03-27    2
3           2017-03-26    1
4           2017-03-25    3
6           2017-03-25    4
Shadow
  • 33,525
  • 10
  • 51
  • 64
S.S
  • 101
  • 10
  • 1
    Try SELECT DISTINCT – suecarmol Mar 30 '17 at 23:13
  • @suecarmol thanks it worked but i cannot select any other columns, i have to use it like this. "SELECT DISTINCT attendance.date, class.classID FROM attendance" – S.S Mar 30 '17 at 23:32
  • 1
    why you cant select the other columns? – Vijunav Vastivch Mar 30 '17 at 23:43
  • Obviously, you meed to use min() instead of max(). – Shadow Mar 30 '17 at 23:44
  • @Shadow i am not sure what you mean i dont use min or max. – S.S Mar 30 '17 at 23:51
  • I gave you a duplicate link. I assumed you would have the common sense to check it out... – Shadow Mar 31 '17 at 00:11
  • @Shadow I did look through it, i dont select the greatest value or the minimum value. I select a date and id. There can be many dates and ids that are the same in the column but i only take one combination of each. E.g. (2017-03-27, 2),(2017-03-27,3). – S.S Mar 31 '17 at 00:38
  • What is wrong with the SELECT DISTINCT query? I don't see anything wrong with the query you posted in the comments. If there is something wrong, please edit your question to show what query you tried and what the results were. – suecarmol Mar 31 '17 at 01:14
  • You would like to select the **first** occurance based on a date value for a particular shelfID. The first occurance by date is the minimum date value by shelfID. – Shadow Mar 31 '17 at 05:28
  • Or alternatively, the the first occurance is the minimum of the id value per shelfID and date if the id us auto incremented. – Shadow Mar 31 '17 at 05:37

0 Answers0