Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2003
    Posts
    54

    Unanswered: Query to extract the most recent information - help please

    Hi all,

    I have a table, three records of which look like this:
    Code:
    ID        PersonID          FirstName          LastName      PostCode
    1         999               Barry              White         BW13 8GS
    2         999               <null>             <null>        BW13 9GS
    3         999               <null>             Whites        <null>
    Both these records refer to the same "person". The records with ID of 2 and 3 represent updates to the record with an ID of 1. The problem is, only the updated data (along with the personID) is represented in records 2 and 3. I need to write query that will return a single record that looks like this:
    Code:
    PersonID          FirstName          LastName      PostCode
    999               Barry              Whites        BW13 9GS
    in other words, the most recent information we have for that person.

    Does anyone have any ideas? I'd be very grateful as this is proving to be a real pain in the butt!

    Kind regards,

    macca

  2. #2
    Join Date
    Mar 2007
    Posts
    24
    PersonID FirstName LastName PostCode
    999 Barry Whites BW13 9GS

    Hi
    Try this:
    Code:
    SELECT PersonID, FirstName, LastName, PostCode FROM YourTable
    WHERE ID IN(SELECT MAX(ID) FROM YourTable WHERE PersonID = 999)

  3. #3
    Join Date
    Apr 2003
    Posts
    54
    Hi shaikh,

    Unfortunately, that would just return

    Code:
    ID        PersonID          FirstName          LastName      PostCode
    3         999               <null>             Whites        <null>
    as it is only selecting the most recent record (or the record with the highest ID).

    Thanks for posting though.

  4. #4
    Join Date
    Jan 2005
    Posts
    28
    Hi,

    You can use the following query, perhaps using CTE may be also solve the problem.

    declare @fn varchar(10), @ln varchar(10), @pc varchar(10)
    SELECT
    @fn = CASE WHEN firstname is not null THEN firstname ELSE @fn END,
    @ln = CASE WHEN lastname is not null THEN lastname ELSE @ln END,
    @pc = CASE WHEN postcode is not null THEN postcode ELSE @pc END
    from persons where personid = 999
    select @fn, @ln, @pc


    Eralper
    http://www.kodyaz.com

  5. #5
    Join Date
    Mar 2007
    Posts
    24
    Ohh sorry

    Try this. Put this code in stored procedure

    Code:
    SELECT TOP 1
    FirstName = (SELECT TOP 1 FirstName FROM Test WHERE FirstName IS NOT NULL ORDER BY [ID] DESC),
    LastName = (SELECT TOP 1 LastName FROM Test WHERE LastName IS NOT NULL ORDER BY [ID] DESC),
    PostCode = (SELECT TOP 1 PostCode FROM Test WHERE PostCode IS NOT NULL ORDER BY [ID] DESC)
    FROM Test WHERE PersonID = 999

  6. #6
    Join Date
    Apr 2003
    Posts
    54
    Thanks eralper,

    That's a smart solution and in testing it works like a dream. I'd love to understand how it works. Could you elaborate, just a little?

    Cheers

    Tim

  7. #7
    Join Date
    Jan 2005
    Posts
    28
    Hi macca,

    The query just updates the values of parameters while reading the selected rows.

    This method is also useful while updating data rows in a table.

    You can look at the article named "How to use SQL variables in an Update Statements Where Variable is also Updated for each row during the Update Process" at http://www.kodyaz.com/articles/SQL-V...tatements.aspx

    Eralper

  8. #8
    Join Date
    Apr 2003
    Posts
    54
    Thanks eralper, that's great. Shaikh, yours worked too so thanks for that.

  9. #9
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by eralper
    declare @fn varchar(10), @ln varchar(10), @pc varchar(10)
    SELECT
    @fn = CASE WHEN firstname is not null THEN firstname ELSE @fn END,
    @ln = CASE WHEN lastname is not null THEN lastname ELSE @ln END,
    @pc = CASE WHEN postcode is not null THEN postcode ELSE @pc END
    from persons where personid = 999
    select @fn, @ln, @pc
    That's interesting; I had never seen this construction before.

    Is it wise to add an 'order by ID' to make sure the rows are processed in the correct order?

  10. #10
    Join Date
    Apr 2003
    Posts
    54
    Hi Ivon,

    I've done loads of testing on this construct, rearranged my data and all sorts and it still gives me the right answer! I must say, I'm not entirely sure how but it's great!

    macca

  11. #11
    Join Date
    Jan 2005
    Posts
    28
    Hi,

    I agree that an ORDER BY clause will be better to ensure that the rows processed are in correct order.

    I believe that since the default order is same with the insert order of the rows, we get the desired result without an Order By.

    Eralper
    http://www.kodyaz.com

Posting Permissions

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