Need a SQL Query to find Last/Latest Child for Each Parent item in a table

**NeoPa** · May 8 '12, 02:31 PM

The meaning of the question is quite clear, but you've left out one important detail, which is what identifies an individual record uniquely in your table? It's clearly not a single field of those shown, so it's probably a number of fields taken together or a separate field not included. Please clarify in order that someone can help you.

**NarenKeer** · May 8 '12, 03:15 PM

I am sorry if i have confused you with that sample data, The Unique field is the SrvcID. The logic is that, the car CAB123 has got three records and OWN321 has four in the table, however i want to fetch the one latest record written in the table for each car.

I must use SrvcID numbers to find out the latest record for a car, that is the largest SrvcID for a car would be the latest SrvcID for the same. Similarly I have to get largest SrvcIDs for each car in the table.

**NeoPa** · May 8 '12, 03:43 PM

Originally posted by NarenKeer

NarenKeer:
The Unique field is the SrvcID.

Are you sure. They seem to be date related so surely multiple vehicles could have the same [SrvcID] value?

**zmbd** · May 8 '12, 09:39 PM

Here's a nice trick I use

I have a nice history table that I use to track repair data just like this:

Using your sample data:
Added primary key [ID] as autonumber
ASSUMING that the [Service DT] is the date of service for each event.

Really should have a unique key in the table this is why I added the [ID] field.

So

Code:

Table Named: tbl_servicehistory
[ID]-pk-autonumber
[CarNo]-text
[SrvcID]-text
[ServiceDT]-date(dd/mm/yyyy)

Then the following will group your records down to the [Car.No] and then pull the oldest record by the [Service DT]

...

Code:

SELECT tbl_servicehistory.ID,tbl_servicehistory.CarNo, tbl_servicehistory.SrvcID, tbl_servicehistory.ServiceDT
FROM tbl_servicehistory 
INNER JOIN 
(SELECT Max( tbl_servicehistory.ServiceDT) AS MaxOfDate 
FROM tbl_servicehistory 
GROUP BY tbl_servicehistory.CarNo) AS zzz_Query1 
ON tbl_servicehistory.ServiceDT = zzz_Query1.MaxOfDate;

...

This will pull records [ID] = 5 and 7 if the data is entered into tbl_servicehist roy as shown in your post.

It should be a simple matter to pull the desired information from the resulting recordset.

IMHO
It would be a best practice if you would consider not using spaces, periods, and other non-alphanumeric charactors in your table and field names. With the one exception being the underscore.
My reasoning behind this is two fold - I am on old school programer and more importaintly, spaces, periods, and other non-alphanumeric charactors can and do cause issues with running code, SQL parsers, and make queries harder to write.

z

**NeoPa** · May 9 '12, 12:13 AM

@zmbd You're keeping me busy tonight :-)

As formatting is something you've put effort into and are doing quite well, I will make some comments that I hope will be helpful :

Good use of the highlighter for the name.
Generally, only quote directly relevant snippets from a post. Multiple quotes is fine if called for, but the responses should deal with the quoted text only.
Keep quoting to a minimum. There is no need to quote the first post. The thread should all be about the first post and it's at the top of the page anyway.
Your post is almost perfectly formatted anyway, but more than two new lines together is never required. It makes it hard to see all the text on a page.
A code block finishes with [/CODE]. If this is on a new line then the post will include an empty code line below the code. Your second block is done well.

Moving on to the SQL :
Unfortunately it doesn't quite work. It may do with the small sample of data provided, but it relies on the [ServiceDT] field being unique across the groupings, which cannot be guaranteed. This is a very difficult area to get one's head around, but relies on producing an aggregating subquery which identifies a single record within the grouping, uniquely.

If one assumes that [tbl_ServiceHist ory] is indeed as you've defined it (It's a shame more members don't follow your example and lay the information out as clearly for us to work on), and that the [CarNo] and [ServiceDt] fields together identify a record uniquely (which makes sense but I'm still trying to clarify with the OP), then something like the following would be required :

Code:

SELECT   tSH.ID
       , tSH.CarNo
       , tSH.SrvcID
       , tSH.ServiceDT
FROM     [tbl_ServiceHistory] AS [tSH]
         INNER JOIN
    (SELECT   [CarNo]
            , Max([ServiceDT]) AS [MaxServDt]
     FROM     [tbl_ServiceHistory]
     GROUP BY [CarNo]) AS subQ
  ON     (tSH.CarNo = subQ.CarNo)
 AND     (tSH.ServiceDT = subQ.MaxServDt)

I almost forgot to mention, your IMHO paragraph is also very good advice, and well expressed. Those who use other characters invariably end up regretting it sooner or later.

**zmbd** · May 9 '12, 04:56 AM

@NeoPa

Thank you for the formatting feed back. Trying to do this by hand and between tasks... some of the tests we do have quite the lag time.

ANYWAY:

I am missing the difference between the query you wrote and the one I suggested... could be just that is is late. :)

I did make a few assumptions: namely, that [CarNo] should be treated as a foreign key and that [ServiceDT] would not appear more than once for a given [CarNo].value for given date. I would suppose that [CarNo].value could have multiple [SrvceID].value for the same [ServiceDT].value... that would break the query I wrote. Certainly more information would be helpful...

**NeoPa** · May 9 '12, 01:23 PM

Originally posted by zmbd

zmbd:
I am missing the difference between the query you wrote and the one I suggested... could be just that is is late. :)

That's partly down to my getting it wrong in post #6 :-D

The important part is that the subquery needs to return, and be linked to on, both the [MaxServDt] field as well as the [CarNo] field. See lines #7 and (after the update) #11.

I will update my earlier post now that you've helped me find the error, as I don't want to be giving out any misleading information (which would not be good for anyone reading it, and nor would it be for me).

**NarenKeer** · May 9 '12, 03:55 PM

It worked :D. Thanks a tonne to you guyz.

The scenario is like this, the fields

Car.No SrvcID Service Dt

are all from three different tables, and along with these fields I have like another 10 fields to populate from 4 different tables.

I have created the relationships carefully and wrote first query to Join these tables and populates all required fields

Then I have created another query(which acts like the sub query in your example) that takes MAX of SrvcID field which is Unique.

Now i created the a third querry and used Inner Join like how you mentioed in your example:

Code:

SELECT * 

FROM Query1 

INNER JOIN Query2 

ON Query1.SrvcID = Query2.SrvcID

and it gave me the expected result.

Thanks again for you help.

Also I will keep in mind about the best practice and formattings next time when I put any post.

**NeoPa** · May 9 '12, 10:47 PM

Originally posted by NarenKeer

NarenKeer:
... that takes MAX of SrvcID field which is Unique.

You made that statement before, but when I queried it you failed to respond. I don't believe that this field can be unique. It clearly seems to be built from the date of the service. It would be a strange set of data indeed, where a service for any individual date could only reflect a single vehicle. Theoretically it's possible, but very unlikely. That's why I asked you for clarification in post #4. A request that has so far been ignored. A response to this would prove helpful, and may help you to avoid an error in your logic. Testing code with data doesn't indicate the code is good just because the results are as expected. Bad code often passes tests. That doesn't make it good code, just code that hasn't failed yet.

Originally posted by NarenKeer

NarenKeer:
Also I will keep in mind about the best practice and formattings next time when I put any post.

That is never a bad thing to keep in mind, but I directed those comments towards zmbd, who is clearly a more advanced poster in this respect, and was making recognisable attempts to work in a better way. It wouldn't be a problem you taking these ideas on board, but no-one would expect you to.

**NarenKeer** · May 10 '12, 07:16 AM

Hi NeoPa,

A request that has so far been ignored.

Apologies for this, I have given a bad example data for SrvcID.

That was just example data, SrvcID is a "Autogenera ted" number which is unique.

Thanks,
Narendran S

**zmbd** · May 10 '12, 01:55 PM

A possible PK for the query

@NarenKeer
Given then that [SrvcID] being an Autogenerated number such as with "autonumber " then [SrvcID] might also serve as your table's primary key.

So Long as [SrvcID] increments with every new record, is not duplicated, and is numeric then:

Code:

SELECT tsH.ID
     , tsH.CarNo
     , tsH.SrvcID
     , tsH.ServiceDT 
FROM tbl_servicehistory AS [tsH] 
     INNER JOIN  
(SELECT Max(tbl_servicehistory.SrvcID) AS MaxOfSrvcID  
FROM tbl_servicehistory  
GROUP BY tbl_servicehistory.CarNo) AS subQ  
ON tbl_servicehistory.SrvcID = subQ.MaxOfSrvcID;

(NOTE: I haven't proofed the above)

**NeoPa** · May 10 '12, 01:57 PM

My mistake. I said "ignored" when I really should have said "overlooked ". I very much doubt it was done consciously. These things are easy to miss when there are multiple responses to go through and respond to. Thank you for clarifying anyway.

It can get a little more complicated than this (SQL from post #6) if there is a requirement to identify the record by the PK, but it doesn't seem necessary for these requirements.

**NeoPa** · May 10 '12, 02:08 PM

Originally posted by zmbd

zmbd:
So Long as [SrvcID] increments with every new record, is not duplicated, and is numeric then:

That is not a 'safe' assumption. Although it appears that MS use a procedure that provides that with 'AutoNumber', it is certainly not documented as such, and relying on that behaviour is dubious to say the least. Even if the software doesn't let you down you're also assuming that all records will be entered in the same order as the services are performed. This is not a safe assumption in various circumstances, the first of which that springs to mind is that data from different service stations may be loaded at different times via some sort of batch process. With such a long delay between services this may well work reliably for 99.99% of occasions in real-life situations but the logic is poor and on the one occasion that it does go wrong will be even more difficult to identify. It is never recommended to rely on anything about an 'AutoNumber' field other than its uniqueness.

**NarenKeer** · May 10 '12, 02:48 PM

Hi NeoPa and zmbd,

These tables I use are actually linked tables. They are originally from Sybase. I just use them to give some custom reports to the local team. I had a meeting with the Sybase guy as well. He confirmed that both the Car Number and Service IDs are primary keys in their respective tables.

It is really appriciatable that you guyz are so careful and didn't want one to get misguided or miss any important aspects.

Thanks,
Naren

Need a SQL Query to find Last/Latest Child for Each Parent item in a table

Need a SQL Query to find Last/Latest Child for Each Parent item in a table

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment