473,883 Members | 2,601 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 27143
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
29061
by: John A Grandy | last post by:
does .NET have a Max() function ? something like MyMaxValue = Max(Value1,Value2,Value3,Value4)
1
2980
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
2231
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
3722
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
3273
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
1776
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
2786
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
8204
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
3611
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
6629
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
11125
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10836
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10407
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7962
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
7114
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
5794
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
5982
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4607
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
3
3230
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.