473,324 Members | 2,257 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Need help understanding Max function in SQL

Can you help me understand why the following doesn't produce a recordset with just one record per Name ID? And also show me how to change it so it does? Thanks.
Expand|Select|Wrap|Line Numbers
  1. SELECT [MyTable].[Name ID], Max([MyTable].Code) AS MaxOfCode, [MyTable].[Active Date]
  2. FROM [MyTable]
  3. GROUP BY [MyTable].[Name ID], [MyTable].[Active Date];
I've researched this quite extensively and am just not "getting it". :-( Note: DISTINCT doesn't help. Here is a snippet of the resulting recordset. I expect there should only be 4 records shown, not 6.
Expand|Select|Wrap|Line Numbers
  1. Name ID   MaxOfCode  Active Date
  2. AMADNI01  CT-005     03/22/99
  3. ANANJA01  CT-005     09/01/92
  4. ANANJA01  CT-007     05/22/94
  5. ANDRDJ01  CT-005     08/01/92
  6. ANDRDJ01  CT-006     11/11/94
  7. AQUIDE01  CT-005     05/03/93
May 6 '08 #1
6 1545
FishVal
2,653 Expert 2GB
Hello, Dave.

Could you please post a desired recordset you want the above recordset to transform to?

Kind regards,
Fish.
May 6 '08 #2
Cool. No Problem. Thanks Fish! There are two separate issues I'm trying to tackle. Here is a sample raw recordset, followed by the desired recordset:
Expand|Select|Wrap|Line Numbers
  1. Name ID   MaxOfCode  Active Date
  2. AMADNI01  CT-005     03/22/99
  3. ANANJA01  CT-005     09/01/92
  4. ANANJA01  CT-007     05/22/03   (that is 2003)
  5. ANDRDJ01  CT-005     08/01/92
  6. ANDRDJ01  CT-006     11/11/94
  7. AQUIDE01  CT-005     05/03/93
  8. AQUIDE01  CT-005     07/07/03   (that is 2003)
The recordset below shows both the "Max(Code)" and the "Max(Active Date) for the Max(Code)" for each person. My two separate needs are:
1) To just find the Max(Code) for each person without regard to the Active Date
2) To find the record with Max(Code) and Max(Active Date) for the Max(Code) for each person.
I state these separately only because I suspect 2) is a more complicated query. If 2) is super hard, then I might have the data integrity to key off of Max(Active Date) ignoring Code all together, but I'll have to look into that. Any help you can offer for either 1) or 2) is greatly appreciated!!!!
Expand|Select|Wrap|Line Numbers
  1. Name ID   MaxOfCode  Active Date
  2. AMADNI01  CT-005     03/22/99
  3. ANANJA01  CT-007     05/22/03
  4. ANDRDJ01  CT-005     08/01/92
  5. AQUIDE01  CT-005     07/07/03
May 6 '08 #3
FishVal
2,653 Expert 2GB
Ok, Dave.

If I've understood your correctly a record returning by the query should contain:
  • unique [Name ID]
  • Max([MaxOfCode]) associated with [Name ID]
  • Max([Active Date]) associated with [NameID] and Max([MaxOfCode])

I would suggest you the following:

First query returns recordset of [Name ID] and Max([MaxOfCode])
qryMaxCode
Expand|Select|Wrap|Line Numbers
  1. SELECT [MyTable].[Name ID], Max([MyTable].Code) AS MaxOfCode
  2. FROM [MyTable]
  3. GROUP BY [MyTable].[Name ID];
  4.  

Second query joins the first one with [MyTable] on [NameID] and [code] and groups records to return Max([Active Date])

Expand|Select|Wrap|Line Numbers
  1. SELECT [MyTable].[Name ID], [qryMaxCode].[MaxOfCode], Max([MyTable].[Active Date]) AS MaxDate
  2. FROM [MyTable] INNER JOIN [qryMaxCode] ON [qryMaxCode].[Name ID]=[MyTable].[Name ID] AND [qryMaxCode].[MaxOfCode]=[MyTable].Code
  3. GROUP BY [MyTable].[Name ID], [qryMaxCode].[MaxOfCode];
  4.  
Regards,
Fish

P.S. The code was not tested.
May 6 '08 #4
Thank You!!! I was thinking it could all be accomplished in one query. The solution you offer works wonderfully. I appreciate the assistance. :-)
Thanks,
Dave
May 6 '08 #5
FishVal
2,653 Expert 2GB
You are quite welcome.
May 6 '08 #6
NeoPa
32,556 Expert Mod 16PB
For your interest, and to avoid having to save the subquery separately, this can also be done using subqueries (See Subqueries in SQL).
[code=SQL]SELECT tMT.[Name ID],
subQ.MaxOfCode,
Max(tMT.[Active Date]) AS MaxDate
FROM MyTable AS tMT INNER JOIN
(SELECT [Name ID],
Max(
Expand|Select|Wrap|Line Numbers
  1. ) AS MaxOfCode
  2.       FROM [MyTable]
  3.       GROUP BY [Name ID]) AS subQ
  4.   ON tMT.[Name ID]=subQ.[Name ID]
  5.  AND tMT.Code=subQ.MaxOfCode
  6. GROUP BY tMT.[Name ID],
  7.          tMT.Code
May 7 '08 #7

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

Similar topics

2
by: lawrence | last post by:
I've been bad about documentation so far but I'm going to try to be better. I've mostly worked alone so I'm the only one, so far, who's suffered from my bad habits. But I'd like other programmers...
10
by: Jeff Wagner | last post by:
I am in the process of learning Python (obsessively so). I've been through a few tutorials and read a Python book that was lent to me. I am now trying to put what I've learned to use by rewriting...
1
by: lawrence | last post by:
I'm trying to gain a better understanding of javascript by studying examples. I noticed this in an online tutorial. I don't get the use of "this". >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> You might want...
19
by: James Fortune | last post by:
I have a lot of respect for David Fenton and Allen Browne, but I don't understand why people who know how to write code to completely replace a front end do not write something that will automate...
26
by: Bail | last post by:
I will have a exam on the oncoming friday, my professor told us that it will base upon this program. i am having troubles understanding this program, for example what if i want to add all the...
2
by: Polyhedron_12 | last post by:
I am having problems calling functions in general in VB. I keep getting alot of errors. Can anybody help me out with this? I put the error message on the same line that it says it is at. I believe...
3
by: Miguel | last post by:
Hi, I'm new to .NET and am using VB .NET as I am from a VB background. I am having difficulty understanding the way .NET handles, passes and uses objects. I have had a look at the Micrsoft Data...
7
by: Buck Rogers | last post by:
Hi all! Newbie here. Below is an example from Teach Yourself C in 21 Days. My apologies if it is a bit long. What I don't understand is how the "get_data" function can call the...
13
by: Sri Harsha Dandibhotla | last post by:
Hello all. I recently came across a function declaration as : char(*(*x()))(); This was not in some code but it was in a C questions thread on some group. I tried to decipher what it returns but...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

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.