469,612 Members | 1,674 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,612 developers. It's quick & easy.

Usage of e.g. Max(Date) aggregat function

Hi, Iīve searching for an reuseable SQL-Statement for follwoing
situation:

Having e.g. a table tblAdress with some fields like persons full name
(as Name), City, Birthdate, Street and so on.

Now I will find e.g. all oldest persons for every City. No problem
with:

select name, city, max(birthdate) group by name, city

But if I also want to have the streetname for the oldest person, I
have a problem cause I canīt include the street into the select
statement like this:

select name, streetname, country, max(birthdate)
group by name, country

This statement is not correct cause streetname is not included in
group by. If I include the street, Iīve got the oldest persons per
city and street. This is not what I want to have. Remember I want the
oldest person in City and after that therefore the street ... and this
in a single sql statement. Itīs only an example, but I think itīs a
standard sql statement to retrieve information like this one for
included aggregate functions. I spend hours on internet searching and
many self made test ... without any solution. Does anyone has a
standard solution for this situation ?

regards
Henri
Jul 20 '05 #1
5 26958
The oldest would actually be those with the minimum birthdate not the
maximum (I'm assuming that your table only includes living people). What if
the oldest person has the same birthday as another person in the same city?
Can I assume that you would want to see all of those with the earliest
birthday in any city?

SELECT name, streetname, city, country, birthdate
FROM SomeTable AS S
WHERE birthdate =
(SELECT MIN(birthdate)
FROM SomeTable
WHERE streetname = S.streetname
AND city = S.city
AND country = S.country)

--
David Portas
SQL Server MVP
--
Jul 20 '05 #2
"Henri" <no****@nuklide.de> wrote in message news:b5**************************@posting.google.c om...
Hi, Iīve searching for an reuseable SQL-Statement for follwoing
situation:

Having e.g. a table tblAdress with some fields like persons full name
(as Name), City, Birthdate, Street and so on.

Now I will find e.g. all oldest persons for every City. No problem
with:

select name, city, max(birthdate) group by name, city

But if I also want to have the streetname for the oldest person, I
have a problem cause I canīt include the street into the select
statement like this:

select name, streetname, country, max(birthdate)
group by name, country

This statement is not correct cause streetname is not included in
group by. If I include the street, Iīve got the oldest persons per
city and street. This is not what I want to have. Remember I want the
oldest person in City and after that therefore the street ... and this
in a single sql statement. Itīs only an example, but I think itīs a
standard sql statement to retrieve information like this one for
included aggregate functions. I spend hours on internet searching and
many self made test ... without any solution. Does anyone has a
standard solution for this situation ?

regards
Henri


CREATE TABLE PersonalInfo
(
first_name VARCHAR(25) NOT NULL,
last_name VARCHAR(25) NOT NULL,
birth_date DATETIME NOT NULL,
address VARCHAR(25) NOT NULL,
city VARCHAR(25) NOT NULL,
state CHAR(2) NOT NULL,
country CHAR(2) NOT NULL,
PRIMARY KEY (last_name, first_name) -- that's right, everyone's name is unique!
)

One way your specific problem can be solved is

SELECT P.*
FROM (SELECT country, state, city, MIN(birth_date) AS birth_date
FROM PersonalInfo
GROUP BY country, state, city) AS O
INNER JOIN
PersonalInfo AS P
ON P.country = O.country AND
P.state = O.state AND
P.city = O.city AND
P.birth_date = O.birth_date

This "template" works well when returning complete rows of data after
applying one level of aggregation.

However, let's say we have two levels of aggregation. Say we want all
the personal info for all the oldest persons in each city whose last names
alphabetically are first, e.g., if Smith and Jones are both the oldest in
Otumwa, IA, then Jones will be returned. Using the pattern above we
could write

SELECT P.*
FROM (SELECT P.country, P.state, P.city, P.birth_date,
MIN(P.last_name) AS last_name
FROM (SELECT country, state, city, MIN(birth_date) AS birth_date
FROM PersonalInfo
GROUP BY country, state, city) AS O
INNER JOIN
PersonalInfo AS P
ON P.country = O.country AND
P.state = O.state AND
P.city = O.city AND
P.birth_date = O.birth_date
GROUP BY P.country, P.state, P.city, P.birth_date) AS O
INNER JOIN
PersonalInfo AS P
ON P.country = O.country AND
P.state = O.state AND
P.city = O.city AND
P.birth_date = O.birth_date AND
P.last_name = O.last_name

Structurally, the "template" is consistent but different. Another approach that
does remain structurally the same is the following. To find the oldest person(s)
in each city:

SELECT P1.*
FROM PersonalInfo AS P1
LEFT OUTER JOIN
PersonalInfo AS P2
ON P1.country = P2.country AND
P1.state = P2.state AND
P1.city = P2.city AND
P2.birth_date < P1.birth_date
WHERE P2.last_name IS NULL

To find the oldest person(s) in each city whose last name is alphabetically
first:

SELECT P1.*
FROM PersonalInfo AS P1
LEFT OUTER JOIN
PersonalInfo AS P2
ON P1.country = P2.country AND
P1.state = P2.state AND
P1.city = P2.city AND
(P2.birth_date < P1.birth_date OR
(P2.birth_date = P1.birth_date AND
P2.last_name < P1.last_name))
WHERE P2.last_name IS NULL

Regards,
jag
Jul 20 '05 #3
"David Portas" <RE****************************@acm.org> wrote in message
news:Ys********************@giganews.com...
The oldest would actually be those with the minimum birthdate not the
maximum (I'm assuming that your table only includes living people). What if
the oldest person has the same birthday as another person in the same city?
Can I assume that you would want to see all of those with the earliest
birthday in any city?

SELECT name, streetname, city, country, birthdate
FROM SomeTable AS S
WHERE birthdate =
(SELECT MIN(birthdate)
FROM SomeTable
WHERE streetname = S.streetname
AND city = S.city
AND country = S.country)
David, I believe he wanted the oldest person per city, not per street, so the
streetname restriction should be removed from the subquery.

Regards,
jag

P.S. Congratulations on your well-deserved MVP selection!
--
David Portas
SQL Server MVP
--

Jul 20 '05 #4
> streetname restriction should be removed from the subquery.

You're right. Thanks.
P.S. Congratulations on your well-deserved MVP selection!


Thanks a lot. Actually, it takes some getting used to the idea that I share
that label with some of the people who write the books and magazines that
sit on my desk - people who have done much more community stuff than I can
hope to. Pretty humbling really.

--
David Portas
SQL Server MVP
--
Jul 20 '05 #5
Hi guys, you are great !!!

Itīs the kind of template I need. I do some false field selections in
the first select statement. Now it workīs.

Shure, the oldest persons will be that with the lowest birthdate :)
I just translate my special question to a common one.

Thanks again
regards
Henri
Jul 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by John A Grandy | last post: by
1 post views Thread by Freddie | last post: by
1 post views Thread by Matik | last post: by
1 post views Thread by abetancur | last post: by
8 posts views Thread by blachca | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.