Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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 –  Akash Jan 15 '13 at 8:23
    
@Akash i want to consider StafferId also.. can you provide sql statement.. –  Rajeev Vyas Jan 15 '13 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) –  Akash Jan 15 '13 at 13:00
add comment

2 Answers 2

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
share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.