SQL Server RANK : get first row from a set of rows

Question

I am using Microsoft SQL Server. I have the following query, which returns, as expected, the selected columns, ranked, and ordered by end_date. end_date is of type datetime.

select 
    rank() over (order by end_date desc) rownum, 
    salary, name, end_date
from
    employee
order by 
    end_date desc

How do I modify and/or add to this to extract just the first returned row? Can it be done with a modification to the above query, or do I have to add additional logic?

What does `endmonth` in an employee table mean? The last month the employee got a salary? Then we would expect *all* employees that still work there to be ranked first. Do you want to restrict your query to show all those employees or do you want to pick one, and if the latter: just any or a particular one? — Thorsten Kettner, Jan 26 '19 at 17:30
Your query doesn't actually order rows, as there is no `ORDER BY` clause. You may happen to get the row(s) with the lowest rank first, but this is in no way guaranteed. And you are talking about the one first row, but `RANK` allows for ties; with two rows in the last endmonth, you'd get them both ranked first. As to SQL questions in general: Please tag your request with the DBMS you are using. SQL answers may differ very much from one DBMS to another. — Thorsten Kettner, Jan 26 '19 at 17:34

score 6 · Answer 1 · edited Jan 27 '19 at 11:35

6

Using subquery:

SELECT *
FROM (select rank() over (order by endmonth desc) rownum,
             salary, name, endmonth
      from employee e) s
WHERE rownum = 1;

Or WITH TIES option(SQL Server specific - may generate worse execution plan):

select TOP 1 WITH TIES rank() over (order by endmonth desc) rownum,
                 salary, name, endmonth, name 
from employee e
order by rownum

edited Jan 27 '19 at 11:35

Scott

2,456
3
32
54

answered Jan 26 '19 at 16:37

Lukasz Szozda

162,964
23
234
275

score 3 · Accepted Answer · answered Jan 26 '19 at 17:05

Typically, the fastest method (with the right index) is a correlated subquery:

select 1 as rownum, salary, name, endmonth, name 
from employee e
where e.endmonth = (select max(e2.endmonth)
                    from employee e2
                   );

The index you want is on employee(endmonth).

If you know there will be one row, then order by with fetch first (or your databases equivalent) is the best approach:

select 1 as rownum, salary, name, endmonth, name 
from employee e
order by e.endmonth desc
fetch first one row only;

If your database supports with ties, then you can use that. For instance, in SQL Server:

select top (1) with ties 1 as rownum, salary, name, endmonth, name 
from employee e
order by e.endmonth desc;

score 0 · Answer 3 · answered Jan 26 '19 at 16:51

0

Its like Lukasz said but you can also use ROW_NUMBER it does not return duplicate values like RANK so you can get one line especifically first.

see it: SQL RANK() versus ROW_NUMBER()

SELECT *
FROM (select ROW_NUMBER() over (order by endmonth desc) rownum,
             salary, name, endmonth, name 
      from employee e) s
WHERE rownum = 1;

answered Jan 26 '19 at 16:51

Fernando dos Santos

141
4

Indeed. Thanks, Fernando. – Scott Jan 26 '19 at 18:01

score 0 · Answer 4 · answered Jan 26 '19 at 17:00

0

select rank() over (order by endmonth desc) rownum, salary, name, 
endmonth, name 
from employee e
limit 1;

Hope this helps

answered Jan 26 '19 at 17:00

Sanyam Churiwala

1
1

`LIMIT` is DBMS specific. In standard SQL it's `FETCH FIRST` and in T-SQL it's `TOP` for instance. Anyway, they all have in common that they don't make much sense without an `ORDER BY` clause. As your query doesn't have an `ORDER BY` clause (which would come between the `FROM` and the `LIMIT` clause), you'd pick one row arbitrarily. – Thorsten Kettner Jan 26 '19 at 17:38
Yes, I know for instance that MySql uses limit. – Scott Jan 26 '19 at 17:56

SQL Server RANK : get first row from a set of rows

4 Answers4