473,605 Members | 2,531 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 27129
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.goo gle.com...
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******** ************@gi ganews.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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
29036
by: John A Grandy | last post by:
does .NET have a Max() function ? something like MyMaxValue = Max(Value1,Value2,Value3,Value4)
1
2969
by: NBaker | last post by:
I have 1 table 'Data' and i need to select the max date. The table contains multiple rows per URN so I need all the fields where the date is most recent. URN £ Date Gift Type Frequency 164287 £5.00 19/05/1988 Recurring Gift Monthly 164287 £5.00 02/10/1992 Recurring Gift Monthly 164287 £4.00 01/04/2001 Recurring Gift Monthly Therefore i only need 164287 where the date is highest at 01/04/2001. i have...
1
2222
by: Freddie | last post by:
Hello everyone: i am trying to compare a MAX(DATE) from one table that would be greater than a date in another. first_table compname MYDATE abc comp 2006-09-26 09:19:43.250
1
3712
by: Matik | last post by:
Hello Everybody, I have a problem, with select stmt: SELECT TOP 15 * FROM oaVIEW_MainData AS TOP_VIEW, oaLanguageData_TAB AS RwQualifierJoin with (nolock) WHERE (c_dateTime>='2007.01.10 00:00:00' AND c_dateTime<='2007.01.10 23:59:59') AND RwQualifierJoin.text_id = c_cfgRegPoint
7
3262
by: gnortenjones | last post by:
I have a problem I'm trying to solve, but for the life of me, cannot figure out how to get it to work. I have two tables, each with a "Last Update Date" field. The query I want to create would match each "Last Update Date" field for table one, with the most recent date prior to that from table two. For example. Table one would have records for 5/1/07, I would want the most recent date prior to that from table two (which aren't...
1
1766
by: abetancur | last post by:
I have the following code: SELECT MAX(INVENTORY_TRANSACTION_HIST.DATE_APPLIED) AS LAST_TRANS, INVENTORY_TRANSACTION_HIST.PART_NO FROM INVENTORY_TRANSACTION_HIST INNER JOIN INVENTORY_PART_IN_STOCK ON INVENTORY_TRANSACTION_HIST.PART_NO = INVENTORY_PART_IN_STOCK.PART_NO WHERE (INVENTORY_TRANSACTION_HIST.TRANSACTION_CODE IN ('PICK-IN', 'PICK-OUT', 'NREC', 'ARRIVAL')) AND ...
3
2773
Inbaraj
by: Inbaraj | last post by:
Hi... I have a Table in that i have inserted the DATE as VARCHAR now in that i Want to get the Max Date. I am new to MySQL Plz help me how to find the max date. with regard Inbaraj.D
8
8198
by: blachca | last post by:
Hello I was wondering if there was a function that I could use that would select the maximum date out of an array of date entries. Could you use the javascript Math.max function and put in a date instead of a number? Just wondering
5
3602
by: sbettadpur | last post by:
hello Let me explain first the table structure. table - > pds fields - >District_ID, W_ID, Commodity_ID, Distribution_Cat_ID, Date, OB, Closing_Stock here i want the Closing_Stock value of particular District_ID and Commodity_ID and Distribution_Cat_ID and for particular month where Date=MAX(date)
10
6605
by: janieavis | last post by:
If I run this query I can see there are 2 records with the dates 10 August 2009 and 24 August 2009: SELECT dbo_JobMain.BpaMainPKey, dbo_JobMain.JobDefinitionPKey, dbo_JobMain.Value, dbo_JobMain.VisitDate FROM dbo_JobMain WHERE (((dbo_JobMain.BpaMainPKey)="000000000040abeb") AND ((dbo_JobMain.JobDefinitionPKey)="001000000043pobf") AND ((dbo_JobMain.VisitDate) Between #8/1/2009# And #8/31/2009#)) ORDER BY dbo_JobMain.JobDefinitionPKey; But...
0
8004
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
7934
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
6743
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
5886
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5445
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
3912
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
3958
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2438
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1541
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.