Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

MySQL Query 1

Status
Not open for further replies.

Wullie

Programmer
Mar 17, 2001
3,674
GB
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:

Code:
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
 
I think I have finially figured this out.

Code:
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
 
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 --
Code:
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top