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

Query to extract number of duplicates

This query is driving me insane.
I need to extract the number of rows in tblUsers where the first 3
characters of "fldSurname" are the same and they where born "fldDOB" on the
same year and month.
Can anyone help ?Poppy,
Try this link from MS!
I had to use it the other w to remove 150,000 duplicate rows from a live
database and it worked a treat.
You need to modify the SQL slightly to accomodate your data, but the logic
is good and should help you out.
Thanks Microsoft! :)
Immy
"poppy" <poppy@.discussions.microsoft.com> wrote in message
news:F7BE4539-D96B-4FDF-AD48-188649EC4CF9@.microsoft.com...
> This query is driving me insane.
> I need to extract the number of rows in tblUsers where the first 3
> characters of "fldSurname" are the same and they where born "fldDOB" on
> the
> same year and month.
> Can anyone help ?|||if all you need is the count:
select left(fldSurname,3) as surname_start, fldDOB, count(*) as rows
from tblUsers
group by left(fldSurname,3), fldDOB
poppy wrote:
> This query is driving me insane.
> I need to extract the number of rows in tblUsers where the first 3
> characters of "fldSurname" are the same and they where born "fldDOB" on th
e
> same year and month.
> Can anyone help ?|||What link from MS '
"Immy" wrote:

> Poppy,
> Try this link from MS!
> I had to use it the other w to remove 150,000 duplicate rows from a liv
e
> database and it worked a treat.
> You need to modify the SQL slightly to accomodate your data, but the logi
c
> is good and should help you out.
> Thanks Microsoft! :)
> Immy
> "poppy" <poppy@.discussions.microsoft.com> wrote in message
> news:F7BE4539-D96B-4FDF-AD48-188649EC4CF9@.microsoft.com...
>
>|||DOH!!!! God damn CTRL+C! :)
Here it is...
Just note that this will help to remove the records also...
If you want to just see how many you have then a simple count as suggested
by Trey will show the results.
http://support.microsoft.com/defaul...kb;en-us;139444
Regards
Immy
"poppy" <poppy@.discussions.microsoft.com> wrote in message
news:AFCD89C6-2862-4304-B554-0581E085E78C@.microsoft.com...
> What link from MS '
> "Immy" wrote:
>