Get Last Five Record From Table

Question

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

Rajeev Punhani · 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.

Kunal Patel · Answer

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.

Surendra Kumar · Answer

Thank you Rajeev Punhani. This is working.

Surendra Kumar · Answer

Thank you Rajeev Punhani. This is working.

Kunal Patel · Answer

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;

Surendra Kumar · Answer

Hello Ali tuncer do not add any extra column

ali tuncer · Answer

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

Surendra Kumar · Answer

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 .

Surendra Kumar · Answer

someone please give me the solution for the same ....

Surendra Kumar · Answer

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 :

Pradip Pandey · Answer

Hi Surendra, Select top 5 from order by desc

Get Last Five Record From Table

Answers (11)