First and Last row in the table

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 2000 Forums

SQL Server Development (2000)

First and Last row in the table

Author

Topic

veparala
Starting Member

30 Posts

Posted - 2008-02-18 : 15:08:00

Hi

I need to get first and last rows from the table. Can you please provide me the SQL for this?

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-18 : 15:21:20

First Row:
SELECT TOP 1 * FROM YourTable
ORDER BY ID ASC
Last Row
SELECT TOP 1 * FROM YourTable
ORDER BY ID DESC

veparala
Starting Member

30 Posts

Posted - 2008-02-18 : 15:27:33

Thanks for your reply. I have one more question. How do i select particular row in table. Lets assume i have one table which has 100 rows. I need to select 50th records. How to do that?.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-18 : 15:46:36

Thats going to be a bit tricky using sql server 2000. If you were using 2005 you could do this using row_number(). One way to do this in 2000 would be to declare a variable table or create a temp table with an identity column (i), insert the contents of your original table into the new table and then select the row from the new table where the identity column equals 50 (i = 50).

Or, if you know for sure that there are at least as many rows in your table as the row you wish to select, you may be able to do something like this where n = the row you wish to select:
select top 1 * from (
select top n * from YourTable
order by id asc) a
order by id desc

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-19 : 01:31:19

Do google search on Pagination

Madhivanan

Failing to plan is Planning to fail

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-02-19 : 04:51:01

To find row 50 (note: I've got it starting from 0 )

CREATE TABLE #temp
(ID INT,
someText VARCHAR(20)
)

INSERT INTO #temp
SELECT 100,'aaa'
UNION
SELECT 101,'bbb'
UNION
SELECT 102,'ccc'
UNION
SELECT 103,'ddd'
UNION
SELECT 104,'eee'

SELECT ID,someText,
(select count(*) from #temp where id <> t.ID and ID<t.ID) as Nu_order
FROM #temp t
where (select count(*) from #temp where id <> t.ID and ID<t.ID) =50

DROP TABLE #temp

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources