By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,057 Members | 1,249 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,057 IT Pros & Developers. It's quick & easy.

Querying Most recent for different categories of object.

P: 24
Hi,

I'm having a bit of trouble visualising how I would go about constructing a query that does the following:

I have a table 'passports' and a table 'persons', where each person can have more than one passport, as you would expect. The persons I am dealing with mainly have dual nationality. Obviously it is very easy to display, say, all of the US passports or all of the passports for a particular person.

What I want to be able to do is to display the most recent passport issued for a person by each country that has ever issued that person a passport, which is proving logically frustrating for me (as you can probably tell by my garbled language).

If anyone could point me in the correct direction, I would be very grateful.

Many thanks.
Dec 31 '07 #1
Share this Question
Share on Google+
5 Replies


jaxjagfan
Expert 100+
P: 254
Hi,

I'm having a bit of trouble visualising how I would go about constructing a query that does the following:

I have a table 'passports' and a table 'persons', where each person can have more than one passport, as you would expect. The persons I am dealing with mainly have dual nationality. Obviously it is very easy to display, say, all of the US passports or all of the passports for a particular person.

What I want to be able to do is to display the most recent passport issued for a person by each country that has ever issued that person a passport, which is proving logically frustrating for me (as you can probably tell by my garbled language).

If anyone could point me in the correct direction, I would be very grateful.

Many thanks.
Your query should look similar to this:

Select Person, Country, Max(IssueDate) as MaxDate
From "yourtables_and_joins"
Group By Person, Country, Max(IssueDate)
Dec 31 '07 #2

P: 24
Many thanks - this worked very well.
Jan 11 '08 #3

P: 24
How about if I wanted the five most recent for each person?... This seems a little trickier.
Feb 7 '08 #4

jaxjagfan
Expert 100+
P: 254
How about if I wanted the five most recent for each person?... This seems a little trickier.
Select Top 5 Person, Country, Max(IssueDate) as MaxDate
From "yourtables_and_joins"
Group By Person, Country

Would give you just the first 5 records. Select Top 5 Percent .... Basically counts the records in the result and returns first 5 percent of the records.

You really need to define "5 most recent WHAT". If you are looking at Sales Counts by month and you want the last 5 months

Select Person, Country, Month(IssueDate) as Month, Count(Sales) as SalesCnt
From "yourtables_and_joins"
Where IssueDate >= DateAdd("m",-5,IssueDate)
Group By Person, Country, Month(IssueDate)

HTH :D
Feb 7 '08 #5

P: 24
Many thanks... in the end I actually found another way of doing this with a bit of lateral thinking. Can't remember it offhand right now as I'm at a different computer.
Feb 14 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.