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

[polaryeti]

 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);

 

[gizmola]

Limit is relative to the ordered result set, so you don't need to try and manipulate the result from a row count.

ORDER BY salary 
LIMIT 0,1 
//would be the lowest salary

ORDER BY salary
LIMIT 3,1 
// would be the 4th lowest

ORDER BY salary DESC
LIMIT 0,1 
//would be the highest

 

The other thing to be aware of in a question like this, is  -- are they trying to find the 4th lowest salary irrespective of employee, because there could be multiple employees wih the same salary.  The question might require you to use a GROUP BY to eliminate duplicates, and can become more complicated from there, if for example, there are 3 employees who are tied for the actual 4th lowest salary.  

Your current queries do not actually find the 4th lowest (whichever), but find the 4th row in the result set.  Depending on the requirements of the question, your initial answer (aside from the unnecessary attempt to make an offset relative to the count of rows) might be factually incorrect.

[polaryeti]

I think you didn't get my question. I specifically want to use DESC in order by and find the fourth lowest salary. (I realize possibility of same salary but that's my next question).

[kicken]

20 minutes ago, polaryeti said:

I specifically want to use DESC in order by and find the fourth lowest salary.

Why?  It can be done in a round about and terrible way, if your version of MySQL is new enough. 

select *
from (
	select *, row_number() over (order by salary desc) as rowNumber, count(*) over () as totalRows
	from employee
) r
where r.rowNumber = r.totalRows-3

 

It'd be far more efficient and easier to read just doing and ASC sort and taking the 4th row.  You can't do a simple limit expression with a single query because the numbers in the limit cannot be column references.  You have to instead find a creative way to count the rows then select the one you need based on that count.

 

[requinix]

3 hours ago, polaryeti said:

I think you didn't get my question. I specifically want to use DESC in order by and find the fourth lowest salary.

You're finding the one record that is the fourth lowest, right? If you're only finding one then it doesn't matter how you sort...

But if you want to consider ties then you'll need a ranking-type solution anyways.