best algorithm to do this query using mysql

Question

Iam looking for best algorithm to do this query using mysql.

SELECT room_no,fname,lname,title,gender,age,text
FROM travelers WHERE reservation_id='9911'
ORDER BY joinnum ASC, id ASC

Result

room_no
1       infant  tess    Mr  M   0   
1       infant2 te1ss   Mr  M   0   
2       tes3    test66  Mr  M   0   
2       tes3    test    Mr  M   0   
2       ss33    test66  Mr  M   0

I want to add another column using sql capabilities which give me an index for each traveler depending on room_no for example

traveler_order  room_no
0                 1  infant     tess    Mr  M   0   
1                 1  infant2    te1ss   Mr  M   0   
0                 2  tes3       test66  Mr  M   0   
1                 2  tes3       test    Mr  M   0   
2                 2  ss33       test66  Mr  M   0

Which mean give an index foreach row according to the room i.e. room 1 has 2 traveler the select statment will give an index 0 for row 0 and 1 for row 1 but row 2 start new room therefore it will give row 2 index 0 the 1 the 2 ... etc. I hope to help me to solve this problem.

More description

What i need is give an order foreach traveler according to the room number for example room 1 have 2 travelers therefore row 1(traveler 1 ) will take index 0 the traveler 2 will take (index 1) then start in room 2 have (3 travelers ) the order will be traveler 1 in room two take index 0 and traveler 2 in room 2 will take index 1 and traveler 3 in room 2 will take index 2 like the second table above

If you use `ctrl-K` you can format your text as code. Or just add 4 space to each line — Juan Carlos Oropeza, Nov 03 '15 at 16:38
It sounds like you need a counter. But your question is unclear, try re-wording so we can better understand the requirements. — htm11h, Nov 03 '15 at 16:42
You `order by` has `joinnum` and `id` which one tell you the order when room was booked? — Juan Carlos Oropeza, Nov 03 '15 at 16:43
This way to give an order foreach row in the table but what i need is give an order foreach traveler according to the room number for example room 1 have 2 travelers therefore row 1(traveler 1 ) will take index 0 the traveler 2 will take (index 1) then start in room 2 have (3 travelers ) the order will be traveler 1 in room two take index 0 and traveler 2 in room 2 will take index 1 and traveler 3 in room 2 will take index 2 like the second table above — M Muneer, Nov 03 '15 at 17:00
I believe that he wants row numbering that resets when the room number changes. (In SQL Server that would be `ROW_NUMBER OVER`. But that won't work for MySQL.) Try checking out [this SO question](http://stackoverflow.com/questions/1895110/row-number-in-mysql). (I think the accepted answer is not what you're looking for, but the next one should be.) — DeadZone, Nov 03 '15 at 18:03

score 1 · Accepted Answer · answered Nov 28 '15 at 15:17

SELECT  @seq := @seq + 1 AS traveler_order,
        room_no, fname, lname, title, gender, age, text
    FROM travelers
    JOIN ( SELECT @seq := -1 ) AS initialize
    WHERE reservation_id='9911'
    ORDER BY joinnum ASC, id ASC

Also,

INDEX(reservation_id, joinnum, id)

is desirable for performance.

best algorithm to do this query using mysql

1 Answers1