Query inside a query SQL-CodePudding

so I am trying to do a query where I need to select the top 5 product with the highest price, and then select the top 3 from those top 5 with the lowest unit price.

I currently have the top 5 with this query

select top 5 * from Products order by UnitPrice desc

but I cant figure out how to do another select in order to select the 3 with the lowest unit price, any tips? I have tried looking for a select inside a select or a subquery but it didn't help, thanks in advance.

CodePudding user response：

If you want rows 3-5 ordered by UnitPrice, you can use offset/fetch:

select p.*
from products p
order by UnitPrice desc
offset 2 rows fetch first 3 rows only;

This is Standard SQL, which SQL Server also supports (SQL Server being the most common database that uses top).

CodePudding user response：

Try nested queries.

select top 3 *
from (
  select top 5 * from Products order by UnitPrice desc
) a
order by UnitPrice;

The result will come out in the reverse order so, if the output order is a requirement, than a second nested statement is needed.