SQL - Select only the row with the oldest age.

dakan

Hi Folks

I'm doing a SQL query to pick the person who is the oldest. I can get it that it shows only the oldest age but not the rest of the record or I can get all the records ordered oldest to youngest but I can't seem to just get the record with the oldest person.

SELECT MAX(age(spdob))
FROM public."salespeople";

This gets me simply the oldest age but no other details like the person's name.
If I do;

SELECT *, MAX(age(spdob))
FROM public."salespeople";

I get ERROR: column "salespeople.spid" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT *, MAX(age(spdob))

If I do

SELECT *, MAX(age(spdob)) AS "Age" 
FROM public."salespeople"
ORDER BY "Age" ASC ;

I get all the records ranked by age. I'm using pgAdmin 4. I Googled and checked Stack Overflow but can't get this working. Thanks.

ELM327

SELECT top 1 *, MAX(age(spdob)) AS "Age"
FROM public."salespeople"
ORDER BY "Age" ASC ;

igCorcaigh

ELM327 wrote: »

SELECT top 1 *, MAX(age(spdob)) AS "Age"
FROM public."salespeople"
ORDER BY "Age" ASC ;

DESC?

ELM327

igCorcaigh wrote: »

DESC?

Yes of course, didnt notice that , haha, would give the youngest and not the oldest :cool:

dakan

Thanks ELM327 and igCorcaigh,

It looks like postgreSQL doesn't do TOP but that pointed me in the right direction. I got it working by doing;

SELECT MAX(age(spdob)) AS "Age", * 
FROM public."salespeople"
GROUP BY spid 
ORDER BY "Age" DESC 
LIMIT 1
;

Thanks for the help.

SilverSideUp

Select * from public."salespeople" order by age desc limit 1;

igCorcaigh

dakan wrote: »

Thanks ELM327 and igCorcaigh,

It looks like postgreSQL doesn't do TOP but that pointed me in the right direction. I got it working by doing;

SELECT MAX(age(spdob)) AS "Age", * 
FROM public."salespeople"
GROUP BY spid 
ORDER BY "Age" DESC 
LIMIT 1
;

Thanks for the help.

Don't think you need the GROUP BY

cython

igCorcaigh wrote: »

Don't think you need the GROUP BY

You might as long as you use the MAX() function, but equally you don't need to use MAX() here either, as others have said.