SQL query for the latest grade of employees which each employee have several records having same id

Question

How to find the latest grade of an employee has the same id. my table is like:

+----+-------+-------+
| ID |  NAME | GRADE | 
| 1  |  ANN  |  A0   | 
| 1  |  ANN  |  A3   |  
| 2  |  JOE  |  B1   | 
| 3  | KIM   |  B3   | 
| 2  | JOE   |  B2   | 
| 3  | KIM   |  C1   | 
| 3  | KIM   |  C3   | 
+----+-------+-------+

How to find the latest grade of ann, Kim, and joe

my output is like:

name latestgrade
ann  A3
joe  B2
kim  C3

Duplicated? https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group — Yu-Lin Chen, Dec 11 '17 at 06:09

Jayasurya Satheesh · Answer 1 · 2017-12-11T10:03:53.313

0

try row_number

;with cte 
as
(
 SELECT
   SeqNo = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY 
SUBSTRING(GRADE,1,1) desc,CAST(SUBSTRING(GRADE,2,LEN(GRADE)) AS INT) DESC),
       *
       FROM YourTable
    )
    SELECT
    * 
    FROM CTE
    WHERE SeqNo = 1

If you wish to insert the above result into a new table, just write a insert into before the select

;with cte 
    as
    (
     SELECT
       SeqNo = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY 
    SUBSTRING(GRADE,1,1) desc,CAST(SUBSTRING(GRADE,2,LEN(GRADE)) AS INT) DESC),
           *
           FROM YourTable
        )
insert into MyNewTable
        SELECT
        * 
        FROM CTE
        WHERE SeqNo = 1

edited Dec 11 '17 at 10:03

answered Dec 11 '17 at 06:13

Jayasurya Satheesh

7,826
3
22
39

If `A10` & `A9` are there as `Grade` values, your code will select `A9`. Hope it should select `A10`. – Ullas Dec 11 '17 at 06:41
it should select A10 – Anu Antony Dec 11 '17 at 06:43
@AnuAntony : Which rdbms are you using? [Check my answer](https://stackoverflow.com/questions/47747295/sql-query-for-the-latest-grade-of-employees-which-each-employee-have-several-rec#47747358) – Ullas Dec 11 '17 at 06:44
@JayasuryaSatheesh : Still it's not correct dude. Now `A9` and `B9` then? – Ullas Dec 11 '17 at 06:45
It work's for me @Wanderer. check this http://sqlfiddle.com/#!6/415fe/1 – Jayasurya Satheesh Dec 11 '17 at 06:48
@JayasuryaSatheesh then it should select B9 – Anu Antony Dec 11 '17 at 06:51
@JayasuryaSatheesh :`B1` and `A10`, it should select `B1`. http://sqlfiddle.com/#!6/8a281/1 – Ullas Dec 11 '17 at 06:53
@JayasuryaSatheesh i want the output as a new table – Anu Antony Dec 11 '17 at 09:26
@AnuAntony cte acts as a virtual table and if you want those records to a new table, then you can directly insert those records to your new table from cte – Jayasurya Satheesh Dec 11 '17 at 09:54
now you can write a insert into just before the select * from cte and insert the results to your table – Jayasurya Satheesh Dec 11 '17 at 10:02
@JayasuryaSatheesh yeah created a new table which has employee id and latest grade as the fields. but I have another question when the latest grade of ann is changed A3 To B1. THEN HOW TO AUTOMATICALLY UPDATE THE NEW TABLE? – Anu Antony Dec 11 '17 at 10:06
You will have to write a trigger to update based on the above Query – Jayasurya Satheesh Dec 11 '17 at 10:07

Ullas · Answer 2 · 2017-12-11T06:34:26.103

Give a row number group by ID and order by the descending order of Grade

Sql Server Query

;with cte as(
    select [rn] = row_number() over(
        partition by [ID]
        order by len([GRADE]) desc, [GRADE] desc
    ), *
    from [your_table_name]
)
select [ID], [NAME], [GRADE]
from cte
where [rn] = 1;

demo

MySql Query

select t1.`ID`, t1.`NAMME`, t1.`GRADE` from (
    select `ID`, `NAME`, `GRADE`, (
        case `ID` when @curA 
        then @curRow := @curRow + 1 
        else @curRow := 1 and @curA := `ID` end 
    ) as `rn` 
    from `your_table_name` t, 
    (select @curRow := 0, @curA := '') r 
    order by `ID`, length(`GRADE`) desc, `GRADE` desc
)t1 
where t1.`rn` = 1;

demo

SQL query for the latest grade of employees which each employee have several records having same id

2 Answers2