Get the maximum amount for each record in sql query

Question

Get the maximum amount for each record in sql query

Navigation

#1 by (6 votes)
#2 by (2 votes)

4

I work in sql server 2000 and I'm doing a query to a "people" table, that has many duplicate records, does not have a primary key, that is, each time a person enters the system they have a record with a different age (the one that was recorded at the time of registration), I want to obtain only one record for each person, the last you have, that is, the oldest, but when using MAX, take the maximum of the entire table not from each person:

select distinct nombre,apellido,max(edad) from persona

that's what I have.

sql

asked by MIKE.- 09.06.2016 в 20:02

source

2 answers

Apply CSS styles to Light DOM (shadow DOM) in custom element HTML5 Error android.os.NetworkOnMainThreadException in using Android HttpURLConnection

score 6 · Answer 1

I would advise you to read the documentation of the aggregation functions in SQL Server (like MAX , for example). In any case, what you need is:

SELECT nombre, apellido, MAX(edad) edad
FROM persona
GROUP BY nombre, apellido;

score 2 · Answer 2

I want to get only one record for each person, the last one have, that is, the oldest one

I think it's enough using the MAX () :

function

SELECT nombre, apellido, MAX(edad) edad FROM persona GROUP BY nombre, apellido;

and you need GROUP BY nombre, apellido; to be able to do it, otherwise you would get the following error:

is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

(as I had it when I did not add it in my initial response = ().