Skip to main content
T
trojnfn
Inspiring
May 18, 2009
Question

Query help - getting the last record

  • May 18, 2009
  • 3 replies
  • 1637 views

I have a history table that contains columns requestNo, status, and date. For example. for requestNo 1000, the first status is open and the date. The second record for reqeustNO 1000 might be review and the date, then status process and the date, and then status closed and the date. For requestNO 2000, the first record will be status open, then the next will be status reveiw.

How do I write a query to get the last record for each reqeustNO ? For example, the last record for requestNO 1000 will  be status closed and the date. For reqeustNO 2000, the last record is review, and the date, etc.

What I really need to do is to find all last records with status of closed, for each reqeustNO.

Thanks

    This topic has been closed for replies.

    3 replies

    T
    TLC-IT
    Inspiring
    May 18, 2009

    The answer to your quest is found in the very words that you use:  what does "the last record" actually mean to you?

    Conceptually, the rows in an SQL database have no inherent "order."  There is no express concept of a newly-inserted record being "anywhere in particular," nor does whatever physical record-position that the records may possess actually "mean anything."  (This was the dramatic improvement that SQL databases made over their linked-list predecessors such as IMAGE and IDMS.)

    Records are frequently filed under a primary-key that is an ascending integer, but this property should merely be regarded as an artifact, subject to change without notice.  A primary-key value contains no inherent information:  its numeric (or what-have-you) "value" means nothing.

    When you say "the last record," I think that you are saying, "the most recent one."  That is to say, the one(s) with the largest value for transaction-date. 

    You can accomplish this search with a subquery, which asks for the "MAX(transaction_date) GROUP BY customer_id" or what-have-you.  The record(s) that you are looking for, then, are those whose (transaction_date, customer_id) are "IN" that set of records.

    When SQL is presented with a query, of arbitrary complexity (including subqueries), it always evaluates the entire request and figures out the optimal way to accomplish it ... which it will tell you about in exhaustive detail by means of the EXPLAIN verb.

    A very nice recent treatment of this task can be found at: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

    D
    Dan_Bracuk
    Inspiring
    May 18, 2009

    If your db supports it, something like this:

    select requestno, coalesce(closestatus, reviewstatus, status)

    from your table

    left join (select requestno r1, status reviewstatus

    from yourtable

    where status = review

    and whatever ) x on requestno = r1

    left join (select requestno r2, status closestatus

    from yourtable

    where status = close

    and whatever) y on requestno = r2

    where whatever

    Michael Borbor
    Michael Borbor
    Inspiring
    May 18, 2009

    You could use query of queries. Or just a SELECT * FROM MyTable WHERE Status='close' ORDER BY Date DESC

    T
    trojnfnAuthor
    Inspiring
    May 18, 2009

    I guess I should have given a better example.

    What if the last record is not closed. If there is another record after close, say inspection, or something, then I would not want that closed record because it is not the last one. I need to get the last record for each request before doing the selection.

    I get the query of query mehtond, but how do I isolate the last record for each reqeust, before using qoq ?

    Michael Borbor
    Michael Borbor
    Inspiring
    May 18, 2009

    Is there some sort of ID in your table?

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices