Get Last Five Record From Table

Question

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

CREATE TABLE [dbo].[Student] (
    [Student_Name] [VARCHAR](50) NULL
)

INSERT INTO Student
VALUES
      ('Mukesh')
    , ('Vinod')
    , ('Mukesh')
    , (NULL)
    , ('Shree')
    , ('Raj')
    , (NULL)
    , ('Abhijit')
    , ('Raju')
    , ('Sharon')
    , ('Ashok')
    , ('Meena')
    , ('Mukesh')
    , (NULL)

SELECT * FROM Student

Note: I want Last Five Record From Above Table

Result like that:

Without an explicit `ORDER BY` clause, the order of the result set will not be guaranteed. So unless you specify which column define the _last records_, the answers you'll be getting here will not be accurate. — Felix Pamittan, Mar 14 '16 at 07:29
I suggest you read [this](http://stackoverflow.com/questions/26236352/default-row-order-in-select-query-sql-server-2008-vs-sql-2012). — Felix Pamittan, Mar 14 '16 at 07:31
Why do you want to do this? Why is the logic behind your desired result? — FLICKER, Mar 14 '16 at 07:31
This can't be done. without a column that specifies the order of inserted values (like an identity column or a create date column) there is no possible way to guarantee the order of the select statement will be the same order the records was inserted to the database. — Zohar Peled, Mar 14 '16 at 07:33
suppose i have 120 record in student table, now i want last 5 recently inserted record in student table. I don't have any student id or serial no column which can be describe order of data — Surendra, Mar 14 '16 at 07:37
Well then I guess you sool. Unless you can add a column to specify the order of inserts (like identity or create date) it simply can't be done. — Zohar Peled, Mar 14 '16 at 07:39
You can try Devart's answer, but it will not return the same set of data everytime. — Felix Pamittan, Mar 14 '16 at 07:40

score 1 · Accepted Answer · edited Mar 14 '16 at 07:58

1

DECLARE @Student TABLE (Student_Name VARCHAR(50)) 

INSERT INTO @Student
VALUES
      ('Mukesh'), ('Vinod'), ('Mukesh'), (NULL)
    , ('Shree'), ('Raj'), (NULL), ('Abhijit'), ('Raju')
    , ('Sharon'), ('Ashok'), ('Meena'), ('Mukesh'), (NULL)

SELECT TOP(5) Student_Name
FROM (
    SELECT *, rn = ROW_NUMBER() OVER (ORDER BY 1/0)
    FROM @Student
) t
ORDER BY rn DESC

edited Mar 14 '16 at 07:58

user3977281

79
3

answered Mar 14 '16 at 07:25

Devart

119,203
23
166
186

3

This doesn't guarantee to return the same result all the time. – FLICKER Mar 14 '16 at 07:30
i like this query, but why an extra Column is coming rn, i want only student_Name column – Surendra Mar 14 '16 at 07:53
@Surendra you are welcome :) BTW please add a PK for your table – Devart Mar 14 '16 at 08:22

ThanhPT · Answer 2 · 2016-03-14T08:28:03.360

0

declare @c int
set @c = (select COUNT(*) from Student)
select *
from
(select ROW_NUMBER() over (order by student_id) as row, *
from Student) t
where t.row between (@c-5) and @c

Try this one.

Update:

declare @c int
set @c = (select COUNT(*) from Student)
select *
from
(select ROW_NUMBER() over(order by (select 1)) as row, *
from Student) t
where t.row between (@c-5) and @c

edited Mar 14 '16 at 08:28

answered Mar 14 '16 at 08:10

ThanhPT

57
7

Not Working. I don't have student_id Column in student table – Surendra Mar 14 '16 at 08:18

score -4 · Answer 3 · edited Mar 14 '16 at 07:28

-4

Use DISTINCT to remove duplicates from the result set. example:

SELECT DISTINCT expressions
FROM tables
[WHERE conditions];

and use LIMIT to limit the result count

edited Mar 14 '16 at 07:28

marc_s

732,580
175
1,330
1,459

answered Mar 14 '16 at 07:23

Tika Farhika

1
1

5

`LIMIT` is **not** a valid keword/operation in **Microsoft SQL Server** or standard ANSI / ISO SQL - – marc_s Mar 14 '16 at 07:28

Get Last Five Record From Table

3 Answers3