MySQL Query 1

[Wullie]

Hi all,

Not been programming for a few years now and starting to lose my mind with this one. It should be easy but I just can't get my head round it.

The table consists of a number of fields with the following being the only ones relating to this issue:

id  -   multipleslot   -    currentstatus   -    daysuntilflag

01  -   123456789      -    JobAdded        -       2
02  -   123456789      -    JobBooked       -       2
03  -   567890123      -    JobBooked       -       1
04  -   123456789      -    JobCompleted    -       2
05  -   567890123      -    PartsOrdered    -       1

What I basically want to do is return the last record inserted for each multipleslot.

So in the above example, I am trying to return:

123456789 - JobCompleted - 2
567890123 - PartsOrderd - 1

I've tried playing about with GROUP and I just can't get it to return the last inserted query for each.

Any ideas? Thanks in advance.

Wullie

The pessimist complains about the wind. The optimist expects it to change. The leader adjusts the sails. - John Maxwell

 

[Wullie]

I think I have finially figured this out.

SELECT currentstatus, daysuntilflag, multipleslot
FROM jobs AS Main
WHERE id = (SELECT MAX(Sub.ID) FROM jobs AS Sub WHERE Sub.multiplejob = Main.multiplejob)

If anyone knows a less intensive way, feel free to point it out.

Wullie

The pessimist complains about the wind. The optimist expects it to change. The leader adjusts the sails. - John Maxwell

 

[r937]

relying on an auto_increment id to provide sequence of rows is risky

by "less intensive" are you suggesting that your query is slow?

try this --

SELECT j.currentstatus
     , j.daysuntilflag
     , j.multipleslot
  FROM ( SELECT multiplejob
              , MAX(id) AS latest_id
           FROM jobs 
         GROUP
             BY multiplejob ) AS m
INNER
  JOIN jobs AS j
    ON j.multiplejob = m.multiplejob
   AND j.id = m.latest_id

also, may i enquire as to why this was posted in the coldfusion forum?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Wullie]

Hi Rudy,

I posted in the Coldfusion forum because I had a few windows open and chose the wrong one to start the thread. TT doesn't give me an option to move the thread so it's stuck here unfortunately.

I'll give that query a try later and see how it fairs. Thanks.


Wullie

The pessimist complains about the wind. The optimist expects it to change. The leader adjusts the sails. - John Maxwell