First and Last records - 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

SQL Server 2005 Forums

Transact-SQL (2005)

First and Last records

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-12-12 : 09:00:51

Gurus,

How to get first and last records from a table.I need sql query for this scenario. No cursor please!

Thanks
Krishna

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2007-12-12 : 09:02:55

mmmmmm.....
The first and last records issue again?

First and last record based on what?

Duane.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-12 : 09:03:18

use min() and max() on the column


select *
from   yourtable
where  id = (select min(id) from yourtable)

KH
[spoiler]Time is always against us[/spoiler]

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-12-12 : 09:04:47

Thanks Khtan

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-12 : 09:05:15

[code]select * from (Select Top 1 * from table order by somecol asc) t1
union all
select * from (Select Top 1 * from table order by somecol desc) t2[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-12 : 09:21:19

quote:
Originally posted by CSK

Gurus,

How to get first and last records from a table.I need sql query for this scenario. No cursor please!

Thanks
Krishna

There is no such thing in a table
SQL Server doesnt have First and Last functions

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-12 : 09:22:44

quote:
Originally posted by harsh_athalye

select * from (Select Top 1 * from table order by somecol asc) t1
union all
select * from (Select Top 1 * from table order by somecol desc) t2

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

or
select *
from yourtable
where id in
(select min(id) from yourtable union all select max(id) from yourtable)
order by id

Madhivanan

Failing to plan is Planning to fail

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-12-12 : 10:02:03

quote:

select *
from yourtable
where id in
(select min(id) from yourtable union all select max(id) from yourtable)
order by id

Madhivanan

Failing to plan is Planning to fail

This is Khtan's answer madhi.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-13 : 01:32:23

quote:
Originally posted by CSK

quote:

select *
from yourtable
where id in
(select min(id) from yourtable union all select max(id) from yourtable)
order by id

Madhivanan

Failing to plan is Planning to fail

This is Khtan's answer madhi.

Read it again. Thats not the same

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources