sql server - How to select columns that dont need grouping by -


i want select students highest grades compared brothers/sisters sql keeps saying need group firstname.

create table t (   id int,   fname  varchar(30),   lname  varchar(30),   grade int ); insert t values (3,'peter','yakobo',33), (2,'ara','yakobo',21), (1,'war','jones',45), (0,'ororo','jones',46);  select fname,lname,max(grade) t group lname 

like example: in yakobo family, peter has highest grade, , in jones family ororo has highest grade

use row_number

with cte as(     select *,         rn = row_number() over(partition lname order grade desc)     t ) select     id, fname, grade cte rn = 1 

as commented zlk:

you want use rank(), not row_number(), in case there duplicates.


Comments

Popular posts from this blog

java - nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet Hibernate+SpringMVC -

sql - Postgresql tables exists, but getting "relation does not exist" when querying -

asp.net mvc - breakpoint on javascript in CSHTML? -