[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • Last Modified:

SQL to find max value in each group

I have data like this .

Name    Value
A       12
B       30
A       15
B       45

I would like to query max value in each
name. The answer is A 15 and B 45.
Could you give me a SQL for this question

Thank you,
0
eci
Asked:
eci
1 Solution
 
mrstickCommented:
This should do it:

SELECT TempTable.Name, Max(TempTable.Value) AS MaxOfValue FROM TempTable GROUP BY TempTable.Name;

Assuming that your table is called TempTable.

HTH

Carl
0
 
eciAuthor Commented:
Thank you,

I have another tabel like this.

Name    Value  Adress
A       12     Cat
B       30     Rat
A       15     Bat
B       45     Dog


Can i get this answer?

A       15     Bat
B       45     Dog
0
 
mrstickCommented:
Hi,

I'm not actually that sure how to do it, the following works, but I suspect that there is an easier way:

SELECT TempTable.Name, TempTable.Value, TempTable.Address FROM Query3 INNER JOIN TempTable ON (Query3.MaxOfValue = TempTable.Value) AND (Query3.Name = TempTable.Name);

Query3 is basically the query from my first response.

HTH

Carl
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
tiendhCommented:
SQL in Oracle
select name, max(value), address
from table
group by name, address
0
 
mrstickCommented:
I tried that but couldn't get it to work in Access, but then again what can you get to work in Access..?!? ;o)

C
0
 
tomArghhCommented:
tiendh is quite right, for Access though, the last line needs to read;

order by name, address
0
 
MBroCommented:
here is in Access :
-------------------

SELECT [myTable].[name], table2.maxValue, [myTable].[address]
FROM myTable, [SELECT name, MAX(value) AS maxValue FROM myTable GROUP BY name]. AS table2
WHERE ([myTable].[name]=table2.name) AND
([myTable].[value]=table2.maxValue);
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now