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 will notice that the result is a table. is the time wrong?
have I changed another path with ASC / DESC and still showing the last id_timeslot?

EXPECTED RESULT:

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

Answer 1

Your GROUP BY bookslot.b_refgroups the entries, so you only see 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

Answer 2

Although your SQL is syntactically correct, it will produce unexpected results.

, SELECT GROUP BY . MySQL , GROUP BY. ORDER BY , GROUP BY. :

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

Answer 3

Since the goal is to collect the earliest time intervals for each book cover, then narrowing the results required 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