"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
