Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2009
    Posts
    8

    Unanswered: Querying latest record

    Hey all,

    I am currently writing a script for MS SQL Server 2005, and I'm trying to incorporate a query that will allow me to pull the latest record for multiple items.

    Not sure of exactly how to do it....

    Say I have to following table for example

    ID Item Status Date
    1 a Processing 09/30/2009
    2 b Processing 10/15/2009
    3 c Processing 10/16/2009
    4 d Processing 10/16/2009
    5 b Registering 10/25/2009
    6 c Registering 10/25/2009
    7 c Complete 10/27/2009
    8 a Registering 10/30/2009

    I need to pull the records where the current status is "Registering"

    Can anyone help?

    Thanks in advance

  2. #2
    Join Date
    Feb 2004
    Posts
    88
    I can't speak for anyone else, but I'd only be able to help if you could clarify the question a bit more...most importantly, what do you mean by 'latest'?

    - the row with the greatest date in the date column?
    - the row most recently entered in the table ?

    If either of those is the answer, why do you say 'I need to pull the records where the current status is "Registering"' ? surely the latest row may not be "Registering"...

    If you're going to use the date column to obtain the latest row, what do you want to do if two rows have the same date?

    Is the id column an IDENTITY column ?

  3. #3
    Join Date
    Jul 2009
    Posts
    8
    My apologies, I need to pull those records whose status currently says "Registering", if they aren't currently in the "Registering" phase then I don't need them.

    So ultimately I want to pull records 5, 6, and 8...

    Yes it is an identity column

  4. #4
    Join Date
    Feb 2004
    Posts
    88
    Errrrm....if thats your requirement then perhaps

    select * from table where status = 'Registering'

    *might* just do the trick for you. Or am I missing something ?

  5. #5
    Join Date
    Jul 2009
    Posts
    8
    Oh I'm having **slow moments** right...not 5, 6, and 8...but records 5 and 8. I don't want to pull item c bc C is currently in the status of "Complete"...

    You follow me?

  6. #6
    Join Date
    Feb 2004
    Posts
    88
    OK - now we're getting somewhere. so your question really is:

    how do I retrieve the records where "registering" is the latest state" ?

    so back to the definition of 'latest'. Are we going to use the date column to determine the latest row? If so, what if two or more rows exist with the same date (i.e. there were two or more state changes for the item on the same day) ?

    Is there really a time portion to that date. If so, are you happy to use it ? If not are you happy to use the order that the rows entered the table as a differentiator ?

  7. #7
    Join Date
    Jul 2009
    Posts
    8
    Yes that's the question !

    Well the date column will help determine when the "latest" entry was made. If two of more rows exist with the same date, then yes time will have to be considered too.

    But yes, I would be content in using the order that the rows entered into the table as the determining factor.

  8. #8
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    select a.id, a.item, a.Status from table a
    where a.status = 'Registering'
    and not exists (select item from table b where a.item = b.item and status='complete')
    Last edited by PMASchmed; 10-30-09 at 17:10.

  9. #9
    Join Date
    Feb 2004
    Posts
    88
    Hi,

    Back after the weekend - hope it's not too late!

    I think this little problem is tailor made for using "common table expressions".
    When using these you really have to break down the problem, and how you would solve it in logical terms.

    So...

    First, you want to get the 'latest' (by date) row for each item.
    You can do this, as I'm sure you understand, by the following:

    SELECT item, MAX(StatusDate) AS ItemMaxDate
    FROM <table>
    GROUP BY item

    next, in case there are two rows for the same item, with the same StatusDate,
    you want to get the row with the greatest "Id" - the identity column - for each of rows
    returned by the first query, sort of like this:

    SELECT b.item, MAX(Id) AS ItemMaxId
    FROM <list returned from first query> a, <table> b
    WHERE b.item = a.item
    AND b.StatusDate = a.ItemMaxDate
    GROUP BY b.item

    Now this list is going to give you for each item, the unique Id that is the 'latest' update for that item.
    Using that, you could go and find all the data from the table, and filter on the status column, sort of like this:

    SELECT *
    FROM <table> f,
    <list returned from second query> c
    WHERE f.Id = c.ItemMaxId
    AND f.Status = 'Registering'

    Well that's it, logically at least...

    Now...how do we do this in one SQL statement?

    By using common table expressions for the intermediate processing stages...
    like this:

    WITH imd AS ( SELECT item, MAX(StatusDate) AS ItemMaxDate FROM <table> GROUP BY item ),
    imi AS ( SELECT b.item, MAX(Id)AS ItemMaxId
    FROM imd, <table> b
    WHERE b.item = imd.item
    AND b.StatusDate = imd.ItemMaxDate
    GROUP BY b.item )
    SELECT * FROM <table> f, imi
    WHERE f.Id = imi.ItemMaxId
    AND f.Status = 'Registering'

    Hope that helped,

    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •