Resolved [SQL Help]

[GOBI]

I got another problem with mine sql query .I have 2 tables , table [person] and table [records]

[person] table
personid
personame

[records] table
no
personid
destination

inside [person] table there is 3 unique person
personid personame
0 James
1 David
2 Sean

inside [records] table
no personid destination
1 0 HK
2 0 RY
3 1 TW
4 0 AK
5 2 SK
6 1 MY
7 2 SK
8 0 AT
9 0 WX

the records i wish to retrieve would be getting the 3 distinct records of the 3 personnel in person table and the latest record in the [records] table .
Desired Results :
6 1 MY
7 2 SK
9 0 WX


What i have tried :
select records.no , personid , personame,destination from person left join records on person.personid=records.personid
The results i got was everything from records table ? wasn't left join joining data from the left only ?

Next i tried adding a distinct keyword , hoping to get unique records

select distinct records.no , personid , personame,destination from person left join records on person.personid=records.personid
still the results was not wat i want ?

So can someone point what is wrong or guide me along with the query ?

[szlamany]

There are several ways to do this - the first that comes to mind is:

Select RE.No, PR.PersonId, PR.PersonName
   ,RE.Destination
   From Person PR
   Left Join Records RE on RE.PersonId=PR.PersonId
   Where RE.No=(Select Max(RE2.No) From Records RE2
                          Where RE2.PersonId=RE.PersonId)

[szlamany]

Although I would go with my first suggestion - I believe this might work as well (I don't often do FROM-Derived Tables - but I believe this is the syntax)

Select RE.No, MaxTable.PersonId, PR.PersonName
   ,RE.Destination
   From (Select RE2.PersonId, Max(RE2.No) "No"
             From Records RE2 Group by RE2.PersonId) as MaxTable
   Left Join Person PR on PR.PersonId=MaxTable.PersonId
   Left Join Records RE on RE.PersonId=MaxTable.PersonId
              and RE.No=MaxTable.No

[GOBI]

i tried ur way and realise that the lastest record for each person was not retrieved .

select max(records.no) , personid , personame,destination from person
 left join records on person.personid=records.personid group by personid order 
by no

->this is what i came out with . but somehow i am not able to get the latest record but the latest records no .

[szlamany]

i tried ur way and realise that the lastest record for each person was not retrieved .

select max(records.no) , personid , personame,destination from person
 left join records on person.personid=records.personid group by personid order 
by no

->this is what i came out with . but somehow i am not able to get the latest record but the latest records no .

You tried the query I gave you in post #2?

And you tried the query in post #3?

Both of these did not work?

Can you post the results - I won't be at a location with SQL for testing for about an hour.

What is the query you posted just now? That query cannot work since you are not GROUP'ing by all the fields in the SELECT statement - right?

[GOBI]

You tried the query I gave you in post #2?

And you tried the query in post #3?

Both of these did not work?

Can you post the results - I won't be at a location with SQL for testing for about an hour.

What is the query you posted just now? That query cannot work since you are not GROUP'ing by all the fields in the SELECT statement - right?

Select RE.No, PR.PersonId, PR.PersonName
   ,RE.Destination
   From Person PR
   Left Join Records RE on RE.PersonId=PR.PersonId
   Where RE.no=(Select Max(RE2.no) From Records RE2
                          Where RE2.PersonId=RE.PersonId)

Thanks i finally got it after changing the destination field to no . i have some trouble understanding the query #3 u provided

[szlamany]

You used the query I would prefer (sorry for the typo!)...

The other query is what I've seen done by others here on the forum - seems to be common in the ACCESS world. The sub-query creates a derived table and gives it a name. Then you are basically SELECT/FROM that derived table.

The derived table filters out just the max values - so it can be used as a source of JOIN data.

Glad it worked for you!