CODEWITHSUNDEEP
c#sqlsql-serversql-server-2008c#-4.0
Hamidreza Mollaebrahimi
Hamidreza Mollaebrahimi

Reputation: 141

Select TOP row in SQL and get 5 rows of end table

Mytable:

Acode | Aname 
1       ll
2       bb
3       aa
4       zz
5       ee
6       tt
7       gg
8       ss

SELECT TOP(3) * from mytable order by acode desc

Result:

Acode | Aname 
8       ss
7       gg
6       tt

But I need the output:

Acode | Aname 
6       tt
7       gg
8       ss

SQL code?

Upvotes: 1

Views: 2100

Answers (3)

candentira
candentira

Reputation: 26

try

SELECT TOP(3) * from mytable order by acode asc

or

SELECT TOP(3) * from mytable order by acode

because ORDER BY clause default orders in ascending mode and that's right what you need.

Upvotes: 1

Fas
Fas

Reputation: 1

If the [Accode] field is a UNIQUE KEY, this should work:

SELECT * FROM mytable
WHERE Acode > (SELECT MAX(Acode) - 5 FROM mytable)

Upvotes: 0

bansi
bansi

Reputation: 57002

Don't know if this is the best way, you may have to do a nested select.

SELECT * FROM (
    SELECT TOP(3) * from mytable order by acode desc
) a ORDER BY accode

Upvotes: 5

Related Questions