Fetch Last four Records without using Order by in SQL

Fetch Last four Records without using Order by in SQL

Yesterday one of my Friend asked me How to fetch last four Record without using Order by and second thing was the table doesnt have any primary key or AutoIncreament Column .

I like the question so thought to have blog on this.

Even though with order by the solution is easy but  little diffcult without Order by.

Here is the actual question

Column1 column2 5 s 4 d 8 f 9 r 6 t 8 y 7 h 3 u 6 i OUTPUT: Column1 Column2 8 y 7 h 3 u 6 i

Fetch last 4 records with out using ORDER BY

In order to get last four records without using order by we will create a table Test with above data

CREATE TABLE [dbo].[test]( [column1] [nvarchar](50) NULL, [column2] [nvarchar](50) NULL ) ON [PRIMARY] GO INSERT [dbo].[test] ([column1], [column2]) VALUES (N'5', N's') INSERT [dbo].[test] ([column1], [column2]) VALUES (N'4', N'd') INSERT [dbo].[test] ([column1], [column2]) VALUES (N'8', N'f') INSERT [dbo].[test] ([column1], [column2]) VALUES (N'9', N'r') INSERT [dbo].[test] ([column1], [column2]) VALUES (N'6', N't') INSERT [dbo].[test] ([column1], [column2]) VALUES (N'8', N'y') INSERT [dbo].[test] ([column1], [column2]) VALUES (N'7', N'h') INSERT [dbo].[test] ([column1], [column2]) VALUES (N'3', N'u') INSERT [dbo].[test] ([column1], [column2]) VALUES (N'6', N'i')

Output Required

Column1 Column2 8 y 7 h 3 u 6 i

First Solution using cursor

--Declare 8 variable in order to get data in same order as output

DECLARE @var1 varchar(50),@var2 varchar(50),@var3 varchar(50),@var4 varchar(50),@var5 varchar(50),@var6 varchar(50),@var7 varchar(50),@var8 varchar(50)

declare @test table (column1 nvarchar(50), column2 nvarchar(50) ) DECLARE MYCURSOR CURSOR

DYNAMIC FOR

SELECT column1,column2 FROM test OPEN MYCURSOR

FETCH LAST FROM MYCURSOR INTO @var1,@var2 -- Fetch the row immediately prior to the current row in the cursor. FETCH PRIOR FROM MYCURSOR INTO @var3,@var4 FETCH PRIOR FROM MYCURSOR INTO @var5,@var6 FETCH PRIOR FROM MYCURSOR INTO @var7,@var8

insert into @test values(@var7,@var8),(@var5,@var6),(@var3,@var4),(@var1,@var2) select * from @test

CLOSE MYCURSOR DEALLOCATE MYCURSOR

Column1 Column2 8 y 7 h 3 u 6 i

Solution with Order by inside a function

with test1 as ( select row_number()over( order by @@rowcount)rownum,* from test ) select column1,column2 from test1 where rownum>5

Another Solution with Order by inside a function

with test1 as ( select row_number()over( order by (select 0))rownum,* from test ) select column1,column2 from test1 where rownum>5

Another Solution without Order by -Fetching last four rows but only if you dont have any duplicate value in column2 like we have in our table

select * from test where column2 not in ( select top ((select COUNT(*) from test)-(4)) column2 from test )

All solutions will give you last four Records.