Select statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select statement

Author

Topic

soori457
Yak Posting Veteran

85 Posts

Posted - 2008-02-05 : 23:45:40

How to select last 10 rows from a table

Suresh Kumar

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-05 : 23:47:27

SELECT TOP 10 *
FROM YourTable
ORDER BY YourSortColumn DESC

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

soori457
Yak Posting Veteran

85 Posts

Posted - 2008-02-06 : 00:03:37

TableName
Name Age
A 17
A 17
B 14
C 16
D 16

This is Table and its data. Now, I want to select bottom two rows.
If I write query like this

select top 2 * from tablename order by Age desc , then the o/p is

Name Age
A 17
A 17

But, I want the o/p as

Name Age
C 16
D 16

Is there any way to select last two rows
Any one help me..

Thanks in Advance

Suresh Kumar

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-06 : 00:05:34

Then order it by your name column instead: ORDER BY [Name] DESC

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

soori457
Yak Posting Veteran

85 Posts

Posted - 2008-02-06 : 00:12:02

Thanks for ur reply. Its working. But it is displaying as

D 16
C 16

But I want the o/p as

C 16
D 16

Suresh Kumar

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-02-06 : 00:21:56

Try this

declare @Table1 table
(
Nam varchar(10),
Age int
)
Insert @Table1

select 'A', 17 union all
select 'A', 17 union all
select 'B', 14 union all
select 'C', 16 union all
select 'D', 16

select * from (select TOP 2 * from @Table1 order by nam desc)t order by nam asc

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-06 : 00:58:57

quote:
Originally posted by soori457

Thanks for ur reply. Its working. But it is displaying as

D 16
C 16

But I want the o/p as

C 16
D 16

Suresh Kumar

What do you want if sample data are as follows?

Name Age
A 17
A 17
B 14
C 16
D 16
A 19

Madhivanan

Failing to plan is Planning to fail

soori457
Yak Posting Veteran

85 Posts

Posted - 2008-02-06 : 05:01:35

Hai Madhivanan

I want to retrieve only last two rows. i.e., the the o/p should be

D 16
A 19

Suresh Kumar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-06 : 05:18:22

You dnt have a PK in your table?

soori457
Yak Posting Veteran

85 Posts

Posted - 2008-02-06 : 05:36:25

I dnt have PK in my table

Suresh Kumar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-06 : 05:59:39

[code]declare @table table--your table
(
[Name] char(1),
Age int
)

INSERT INTO @table ([Name],Age) VALUES('A',17)
INSERT INTO @table ([Name],Age) VALUES('A', 17)
INSERT INTO @table ([Name],Age) VALUES('B', 14)
INSERT INTO @table ([Name],Age) VALUES('C', 16)
INSERT INTO @table ([Name],Age) VALUES('D', 16)
INSERT INTO @table ([Name],Age) VALUES('A', 19)
select * from @table --view your table

declare @temp table--a temporary table with PK col
(
ID int IDENTITY(1,1) PRIMARY KEY,
[Name] char(1),
Age int
)
Insert @temp ([Name],Age)--populate temp with data from your table
SELECT [Name],Age
FROM @table
;
With Order_CTE (RowNo,Name,Age) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY ID) RowNo,
[Name],Age
FROM
@temp
)

SELECT t1.[Name],t1.Age
FROM Order_CTE t1
CROSS JOIN (SELECT MAX(RowNo)AS MaxRow FROM Order_CTE)t2
WHERE t2.MaxRow-t1.RowNo + 1<=N

output
-----------
yourtable
------------
Name Age
---- -----------
A 17
A 17
B 14
C 16
D 16
A 19

result (for N=2)
-----------
Name Age
---- -----------
D 16
A 19[/code]

you can pass a value for N to get last N records

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-06 : 06:00:40

Do you have ANYTHING else that defines some kind of order? A datetime, a sequence number, anything?
If not, you can't do this. This is NOT Microsoft Access.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources