Feeds:
Posts
Comments

Posts Tagged ‘Bottom 10’

Quick one today:

Every few days, we run into a situation, where just adding an ORDER BY clause does not solve the problem of retrieving records in a particular fashion.

ORDER BY is beneficial, if you want to order the records and retrieve them in a defined pattern. Adding TOP 10 (TOP 100, etc) to it makes it easy to query the ones you want. But our situation we needed to order the records in ascending order, but we only want the bottom 10 records.

Drum roll !! Enter the all purpose CTE to rescue!!

--
-- Create a temp table and store records in some random order
--
CREATE TABLE #OrderTest (
	  ID	INT			NOT NULL
	, Name	VARCHAR(10)	NOT NULL
	, Flag	TINYINT
)
GO

INSERT INTO #OrderTest (ID, Name, Flag)
VALUES (2, 'Test Name1', 1)
	, (4, 'TestName2', 0)
	, (1, 'TestName3', 7)
	, (3, 'TestName4', 2)
	, (9, 'TestName5', 5)
	, (8, 'TestName6', 1)
	, (5, 'TestName7', 3)
	, (6, 'TestName8', 0)
GO

SELECT * FROM #OrderTest
GO
Initial order of records (Insert order)

Initial order of records (Insert order)

Now, when we query TOP 10 using ORDER BY ID ASC, we’ll get records in the expected ascending order of the ID column. But the requirement is to retrieve BOTTOM 5 records, with the same ascending order on ID column. Like TOP 10 clause, there is no BOTTOM 10 clause. See the image below for clearer understanding:

Records we need to query

Records we need to query

So we use CTE to circumvent that.

Usually ORDER BY clause is not allowed in CTE’s; Except when TOP clause is used.

--
-- Use CTE to pick the records we want
--
; WITH Bottom5 (ID, Name)
AS
	(
		SELECT TOP 5 ID, Name
		FROM #OrderTest
		ORDER BY ID DESC
	)
SELECT *
FROM Bottom5
ORDER BY ID ASC
GO

Desired Result

Desired Result

Hope this helps,
_Sqltimes
Advertisements

Read Full Post »