Query needed to fetch the field value corresponding to Max

Transact-SQL
1

Friends,

I have few records like thiis

**empNum SlNo CODE

4001 1 SRM

4002 2 SRD

4003 3 SRA

4004 4 SRQ

4000 5 SRQ

4001 6 SRQ

4002 7 SRM

4003 8 SRM

4004 9 SRM

4000 10 SRP

4001 11 SRX

4002 12 SRD

4003 13 SRQ

4004 14 SRA

I want to make some queries whose result will satisfy these criteria.

Query 1.

i want to get distinct employee codes
whose "CODE" value = search criteria value i Will give
and
whose SL.NO is the max of the SL.no value for that particulat SL. no

i.e fetch the record whose SL.No is maximum when grouped by the empnum and whose code is what we specifed

Query 2
Another ouput should be
get the empcode, the maximum value of Sl. no for it and the code corresponding to that SL.no

2

Any updates? can any help?

3

Is this a school assignment?

  1. select the empnum field and use the aggregate function max on the slno field from you table, filtering the code field by the value you want, then grouping by the empnum field

  2. select empnum, slno and code fields using the top 1 with ties from your table, sorting by the row_number function using the over clause with the sort set to reversed slno

4

Consider creating a table function: EmployeesLastSlNumber or similar.