php mysql asc/desc order

Question

TABLE:

**timeslot**:
----------
id_timeslot   times
1             09:00
2             09:30
3             10:00
4             10:30
5             11:00

**bookslot**
id   id_timeslot     date        b_ref
-------------------------------------------
1    2               2010-02-22  001 
2    3               2010-02-22  001
3    4               2010-02-22  001
4    5               2010-02-22  001
5    2               2010-02-25  002
6    3               2010-02-27  003
7    4               2010-02-27  003
8    5               2010-02-27  003

PHP

$q = $mysqli->query("SELECT * FROM bookslot  
LEFT JOIN timeslot ON bookslot.id_timeslot = timeslot.id_timeslot   
WHERE bookslot.status = 1 
GROUP BY bookslot.b_ref  
ORDER BY bookslot.date ASC, bookslot.id_timeslot ASC LIMIT 20");

HTML RESULT:

DATE         TIMES  
2010-02-22   10:30
2010-02-25   09:30
2010-02-27   11:00

anyone notice that on the table result. the times is incorrect order?
i changed another way round with ASC / DESC, and still the times showing the last id_timeslot?

EXPECTED RESULT:

DATE         TIMES  
2010-02-22   09:30
2010-02-25   09:30
2010-02-27   10:00

Solution 1

Your GROUP BY bookslot.b_ref is grouping the records, so you're only seeing the last time in each case.

Try using

SELECT date, time, MIN(bookslot.id_timeslot)
FROM bookslot  
LEFT JOIN timeslot ON bookslot.id_timeslot = timeslot.id_timeslot   
WHERE bookslot.status = 1 
GROUP BY bookslot.b_ref  
ORDER BY bookslot.date ASC, bookslot.id_timeslot ASC LIMIT 20

Solution 2

Since the goal appears to be about collecting the earliest timeslots for each bookslot then it's required to narrow the results with MIN

SELECT b.id, b.id_timeslot, b.date, MIN(`date`) , t.times
FROM bookslot b
LEFT JOIN timeslot t ON b.id_timeslot = t.id_timeslot
GROUP BY b.b_ref

Solution 3

While your SQL is syntactically correct but it will produce unexpected results.

Normally, the columns that you SELECT must be specified in the GROUP BY clause or should be enclosed inside an aggregate function. Otherwise, MySQL will determine, at its own discretion, which records to eliminate in the GROUP BY operation. The ORDER BY does not matter because it is applied after the GROUP BY operation. You should better revise your query like this:

SELECT b_ref, MIN(ADDTIME(date, times)) AS complete_datetime
FROM bookslot
LEFT JOIN timeslot ON bookslot.id_timeslot = timeslot.id_timeslot
WHERE bookslot.status = 1
GROUP BY bookslot.b_ref
ORDER BY bookslot.date, bookslot.id_timeslot