Fetch Records alternatively from database

Question

I have following table where each record indicates either start time or end time of a shift for staff.

The first record is for start time, and the next immediate record is for end time.

SELECT * 
FROM  `recorddata` 
ORDER BY  `recorddata`.`Date_Time` ASC 
LIMIT 0 , 30

What i want is all start and end time together as result of query.

Answer 1

Without considering performance, it is possible to solve by using one query with 3 subquery inside.

SELECT * FROM 
   (SELECT * FROM 
       (SELECT id, Date_Time, MOD(@no := @no + 1, 2) AS even 
        FROM recorddata  ORDER BY Date_Time ASC) l
    WHERE even = 0) a, 
   (SELECT id, Date_Time FROM recorddata ORDER BY Date_Time ASC) b
WHERE a.Date_Time < b.Date_Time
GROUP BY a.id

Answer 2

I don't see a good method to do your query. Your data seems to violate two important rules in (My)SQL:

1. in MySQL you can't rely on the ordering of your rows. In your example it's not possible to sort by any column.

2. Your table really should have two fields in one row: "start_datetime" and "end_datetime" instead of two rows for saving start and end.