-1

I just want 1 record of each class which is the youngest student?

select e.`cname`, s.`sname`
from `Nguy3524`.`enrolled` e, `Nguy3524`.`student` s
where e.`snum` = s.`snum`
and e.`snum` in (
                 Select g1.`snum`
                  from `Nguy3524`.`grade` g1
                  where g1.`score` in (select max(g2.`score`) 
                  from `Nguy3524`.`grade` g2 where g1.`cname` = g2.`cname` ))
order by s.`age`

right now it return cname with multiple sname, but I just one the first record of eacch group.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Thao N
  • 81
  • 1
  • 2
  • 8

1 Answers1

1

you can use row_number approach if you are using MySql 8

select * from (
    select e.`cname`, s.`sname`,
      row_number() over (partition by cname order by age) rn 
    from `Nguy3524`.`enrolled` e, `Nguy3524`.`student` s
    where e.`snum` = s.`snum`
    and e.`snum` in (
                     Select g1.`snum`
                      from `Nguy3524`.`grade` g1
                      where g1.`score` in (select max(g2.`score`) 
                      from `Nguy3524`.`grade` g2 where g1.`cname` = g2.`cname` ))
) t
where rn = 1
order by s.`age`
Derviş Kayımbaşıoğlu
  • 28,492
  • 4
  • 50
  • 72
  • what is rn? (row number?) why you put t and did not use it? @Simonare – Thao N Feb 26 '19 at 23:42
  • I just put select * from(.... orderby age) as X .It works and gives me just one record (the first one) even I do not group by cname. It is little weird for me. why does it work like that? – Thao N Feb 27 '19 at 00:11
  • T is just an alias for the inner query. RN is also alias for row_number. My query is saying that produce incremental row number partitioned by cname and ordered by age. And take only the first element in each group – Derviş Kayımbaşıoğlu Feb 28 '19 at 17:26