Query to extract the most recent information - help please

Hi all,

I have a table, three records of which look like this:

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:

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,

maccaPersonID FirstName LastName PostCode
999 Barry Whites BW13 9GS

Hi
Try this:

SELECT PersonID, FirstName, LastName, PostCode FROM YourTable
WHERE ID IN(SELECT MAX(ID) FROM YourTable WHERE PersonID = 999)|||Hi shaikh,

Unfortunately, that would just return

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.|||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|||Ohh sorry

Try this. Put this code in stored procedure

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|||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|||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-Variables-In-Update-Statements.aspx

Eralper|||Thanks eralper, that's great. Shaikh, yours worked too so thanks for that.|||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?|||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|||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