0

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

enter image description here

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

enter image description here

Improve this question
3
  • Don't know if its possible in MySQL, but how about using a ROWNUMBER() over Date_Time , join the CTE with itself, with the join condition as rownumber+1 and a where condition to filter out records where the left rownumber is even or the right rownumber is odd Commented Jan 15, 2013 at 8:23
  • @Akash i want to consider StafferId also.. can you provide sql statement.. Commented Jan 15, 2013 at 12:06
  • hmm.. apparently theres no straightforward way to do it in MySQL since it doesn't have the equivalents of a CTE or RowNumber, so any method I suggest would be hacky. Probably try out Neo's answer if it works for you (and take ahofmann's advise for the future) Commented Jan 15, 2013 at 13:00

2 Answers 2

Reset to default
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
Improve this answer
1

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.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.