max - Extracting maximum SQL -

max - Extracting maximum SQL -

query:

select c1.cname, c2.sumlength totallength country c1 bring together (select country, sum(length) sumlength neighbour grouping country order sum(length)) c2 on c1.cid = c2.country;

output:

cname | totallength -------------+------------- san marino | 39 republic of albania | 605 kingdom of lesotho | 909 transitional islamic state of afghanistan | 5529 people's democratic republic of algeria | 6343

i want extract maximum of total length , cname.

desired output:

cname | max -------------+------------- people's democratic republic of algeria | 6343

i tried executing:

select c1.cname, max(c2.sumlength) totallength country c1 bring together (select country, sum(length) sumlength neighbour grouping country order sum(length)) c2 on c1.cid = c2.country;

but maintain encountering error:

error: column "c1.cname" must appear in grouping clause or used in aggregate function

any help appreciated.

try this.

;with cte (select row_number() over(order c2.sumlength desc) rn, c1.cname, c2.sumlength totallength country c1 bring together neighbour c2 on c1.cid = c2.country bring together (select country, sum(length) sumlength neighbour grouping country) c2 on c1.cid = c2.country) select cname, totallength cte rn = 1

sample

create table #temp (cname varchar(50),totallength int ) insert #temp values ('san marino',39),('albania',605), ('lesotho',909),('afghanistan',5529), ('algeria',6343); cte (select row_number() over(order totallength desc) rn, cname, totallength #temp) select cname, totallength cte rn = 1

sql max