0

I am using SQL Server 2014 and trying to get 10th record and wrote following query.

SELECT TOP 1 * 
FROM (SELECT TOP 10 * from Books) AS B 
ORDER BY ID DESC;

But it returns me the the last record. I figure out that it is using order by ID DESC from outer query.

Few more tries and its results are as below.

Query 1 Its working as expected.

SELECT *
FROM (SELECT TOP 10 * from Books) AS B

Result enter image description here

Query 2 Its not working as expected. It should be return records with ID from 10-1.

SELECT *
FROM (SELECT TOP 10 * from Books) AS B
ORDER BY ID DESC;

Result enter image description here

Query 3 If I'm using ORDER BY inside subquery then it's working as expected.

SELECT *
FROM (SELECT TOP 10 * from Books ORDER BY ID ASC) AS B
ORDER BY ID DESC;

Result enter image description here

Can anyone tell me what I'm doing wrong. Why it is necessary to use ORDER BY inside subquery?

If in Query 1 getting records from ID between 1-10 then why in Query 2 it returns last 10 records? I have just added ORDER BY DESC which should re-order fetched records from subquery only.?

Also tried on W3School Try it yourself link here with following query and its working properly.

SELECT TOP 1 * FROM (SELECT TOP 10 * from Customers) AS B ORDER BY CustomerId DESC;
Karan
  • 12,059
  • 3
  • 24
  • 40

2 Answers2

4

There's no sort in the subquery.

Try a CTE-

WITH CteBooks as (
SELECT TOP 10 * FROM Books ORDER BY ID ASC
)

SELECT TOP 1 * FROM CteBooks ORDER BY ID DESC;
OwlsSleeping
  • 1,487
  • 2
  • 11
  • 19
  • If I don't use `ORDER BY ID ASC` inside CTE then it's behaving same as I've mention in my question. – Karan Oct 23 '18 at 08:32
  • Exactly. Your query was getting the bottom 1 from a subquery that didn't necessarily have the top 10 ids. – OwlsSleeping Oct 23 '18 at 08:35
  • Thanks for your efforts. Can you tell me that from `Query 1` in my question if I'm getting records from ID between 1-10 then why in `Query 2` it returns last 10 records? I have just added ORDER BY DESC which should re-order fetched records from subquery only. – Karan Oct 23 '18 at 08:43
  • 1
    Tables aren't ordered. Unless you specify an order your results will be ordered at the whims of clustered indexes, execution plans, and the server engine. It just happened to work in query 1 by luck. – OwlsSleeping Oct 23 '18 at 09:04
2

What about OFFSET clause :

SELECT  CustomerID
FROM     Customers
ORDER BY CustomerID ASC 
OFFSET  10 ROWS 
FETCH NEXT 1 ROWS ONLY

Works on SQL Server 2012 and select the element at offset 10 and take only one row.

Ayak973
  • 468
  • 1
  • 6
  • 23
  • Thanks for your efforts. Can you tell me that from Query 1 in my question if I'm getting records from ID between 1-10 then why in Query 2 it returns last 10 records? I have just added ORDER BY DESC which should re-order fetched records from subquery only. – Karan Oct 23 '18 at 08:45
  • @Karan : if I remind correctly, the subquery is not ordered, so the RDMS must return any of the 10 results he have choose : [Link](https://stackoverflow.com/a/10064571/5847906) – Ayak973 Oct 23 '18 at 08:55
  • I'm completely agree with answer from link you have provided. But As in **Query 1** result is returning records with id 1-10 then outer query must be ordering those 10 records only right? which is not happening here. – Karan Oct 23 '18 at 08:56
  • If query 1 (no order clause, no where clause) returns ids from 1 to 10 (what if your first id began with 2?), it's just an happy coincidence, it can return results for id 1,8,50,35.... The only thing you are sure with this request is that it MUST return max 10 rows, not where or how the results are choosen – Ayak973 Oct 23 '18 at 09:02
  • I'm not concerned about Ids. If it return like 1,8,50,35... then without order by in outer query, then with order by in outer query it should be returning those same record but in specified order. – Karan Oct 23 '18 at 09:07
  • 1
    @OwlsSleeping says, and if you read carefully the link posted before, `It just happened to work in query 1 by luck` – Ayak973 Oct 23 '18 at 09:14