Get Last Five Record From Table

Surendra Kumar

10y1.3k

I want Last Five Record from table. Condition is Column contains duplicate and null values.

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajeev Punhani10y ago
Accepted answer
Hi Surendra,
Please try this
select [Stu_Id],[Student_Name] ,[City] ,[Registered], RowNum = row_number() OVER (ORDER BY (SELECT 0)) from student ORDER BY RowNum desc
If the above solution is helpful then,accept the answer.

+1
Surendra Kumar10y ago
Thank you Rajeev Punhani. This is working.
0
Surendra Kumar10y ago
Thank you Rajeev Punhani. This is working.
0
Kunal Patel10y ago
I have used common table expression here.

WITH Student_CTE (Id,Stu_Id, Student_Name, City,Registered)
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS Id,Stu_Id, Student_Name, City,Registered FROM Student
)

SELECT TOP(5) Id,Stu_Id, Student_Name, City,Registered FROM Student_CTE ORDER BY Student_CTE.Id desc;
0
Surendra Kumar10y ago
Hello Ali tuncer

do not add any extra column
0
ali tuncer10y ago
if you add primary key(ID) and set auto-increment, you can write like this :
Select s.Stu_Id,s.Student_Name,s.City
from
(
Select top 5 Stu_Id,Student_Name,City,ID
from dbo.Student
order by ID desc) as s
order by s.ID

0
Surendra Kumar10y ago
Hello Kunal

It is working with if i add column CreatedDate.
But i do not want add any extra column.

Primary Key will not work without ordering .
0
Kunal Patel10y ago
Hi Surendra

I think you need to add one more column like CreatedDate.

and then you get last five record from your table.

Otherwise without primary key it's not possible.
+1
Surendra Kumar10y ago
someone please give me the solution for the same ....
0
Surendra Kumar10y ago
Hello Pradip !

Above Solution not fulfilling my requirement.
-----------Here is my table ----------

CREATE TABLE [dbo].[Student](
[Stu_Id] [int] NULL,
[Student_Name] [varchar](50) NULL,
[City] [varchar](50) NULL,
[Registered] [nvarchar](50) NULL
)
insert into Student values(1, 'Mukesh', 'Delhi', 'Yes')
insert into Student values(2, 'Vinod', 'Mumbai', 'Yes')
insert into Student values(3, 'Mukesh', 'Faridabad', 'Yes')
insert into Student(Stu_Id, City, Registered)values(4,'Delhi', 'No')
insert into Student values(6, 'Shree', 'Kanpur', 'Yes')
insert into Student values(5, 'Raj', 'Delhi', 'No')
insert into Student(Registered) values('No')
insert into Student values(6, 'Abhijit', 'Noida', 'Yes')
insert into Student values(4, 'Raju', 'Delhi', 'Yes')
insert into Student values(1, 'Sharon', 'Chennai', 'No')
insert into Student values(1, 'Ashok', 'Delhi', 'Yes')
insert into Student(Student_Name, City, Registered) values('Meena', 'Lucknow', 'Yes')
insert into Student(Stu_Id, Student_Name, City) values(5, 'Mukesh', 'Jammu')

select * from student

Note : I want Last Five Record From Above Table

Result Should be like that :

0
Pradip Pandey10y ago
Hi Surendra,

Select top 5 from order by desc
0