How to count the number of rows of a table used in the query?

[shivajikobardan]

I've this database.

https://brucebauer.info/assets/ITEC3610/Northwind/Northwind-Sample-Database-Diagram.pdf

I want to find fourth lowest salary. 

 

SELECT 
    *
FROM
    employees
ORDER BY salary DESC
LIMIT 1 OFFSET (Number_of_rows_of_table-3);

I can solve this by using order by ASC pretty easily with LIMIT 1 OFFSET 3. I'm wondering if there's an way to solve it via order by DESC?

Here's my try which didn't work.

 

SELECT 
count(*) as no_of_rows_of_table, salary
FROM
    employees
ORDER BY salary DESC
LIMIT 1 OFFSET (no_of_rows_of_table-3);

 

[wanderingfool2]

I know not an answer, but why?

 

Using DESC just needlessly would complicate things and would start getting into a terrible habit to learn.  Ultimately it should be followed to keep it simple, especially when learning, unless you are trying to make a super efficient query...but it fits neither case.

 

It would be different if you weren't allowed using LIMIT or OFFSET (as not everything supports that).

 

I mean if you really wanted to you could do something like

SELECT 
    *
FROM
    employees
ORDER BY salary DESC
LIMIT 1 OFFSET (SELECT count(*) FROM employees) - 3;

The above though, if I were to be marking someone on it, I would give them a failing grade because while technically doing the job it's by no means something that should be considered to be done.

[Franck]

What is the reason that you want to use precious server resource to do something inefficient ?

 

If indexing is properly done the most efficient is 

Quote

SELECT salary

FROM employees

ORDER BY salary ASC

OFFSET 3 ROWS

FETCH NEXT 1 ROWS ONLY

 

[shivajikobardan]

Thank you all for your answers.