Selecting last row in a table - 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

Selecting last row in a table

Author

Topic

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-31 : 08:23:54

Hi, just a quick one,

I would like to be able to select the last record in a table. I am using sql express, using SELECT TOP order by desc is not appropriate as the rows are already sorted by another column, so it would muck that up I think, SQL express doesn't appear to suport LAST.

Any ideas?

Thamks in advance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-31 : 08:24:57

Post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-10-31 : 08:40:41

you should be able to use ORDER BY. "Last" does not (should not) mean anything in your database. It can only be "last" if there is some type of order.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-31 : 08:41:42

quote:
Originally posted by madhivanan

Post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

Age Firstname Lastname Gender
20 John Smith Male
21 Chris Jones Male
Sorted in ascending order

I want to select the line of Chris Jones as it is the latest in the table.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-31 : 08:47:56

How do you that is the latest?

Madhivanan

Failing to plan is Planning to fail

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-31 : 08:56:47

quote:
Originally posted by madhivanan

How do you that is the latest?

Madhivanan

Failing to plan is Planning to fail

Every time the table is updated, it is updated with three new rows which are sorted in ascending order, when the table is updated again, another three rows are added which are sorting by ascendinging but only in that group of three, not the entire table. I just want to select that bottom row of the latest update, which will be the last entry of the entire table. For example red=past update blue=latest update, So for example I would be looking to select only the row with the age of 30, the last row.

Age Firstname Lastname
20
21
25
19
22
30

Hope you understand

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-31 : 09:18:04

Do you have a column that identifies the past updates and latest updates?

Madhivanan

Failing to plan is Planning to fail

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-31 : 09:23:41

quote:
Originally posted by madhivanan

Do you have a column that identifies the past updates and latest updates?

Madhivanan

Failing to plan is Planning to fail

I do actually come to think of it, would it be something like
Select Top 1 ........
from table3
order by identifier desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 11:52:40

do you have an identity column in your table?

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-31 : 12:00:44

quote:
Originally posted by visakh16

do you have an identity column in your table?

Yes I do.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 12:14:58

then just use SCOPE_IDENTITY() or IDENT_CURRENT('YourTableName Here') to get the last inserted record's identity value after each 3 record inserts. then use below to get last inserted record


DECLARE @LastID int
SET @LastID=SCOPE_IDENTITY()

select * from yourtable where identityfield=@LastID

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-31 : 12:31:30

quote:
Originally posted by visakh16

then just use SCOPE_IDENTITY() or IDENT_CURRENT('YourTableName Here') to get the last inserted record's identity value after each 3 record inserts. then use below to get last inserted record

DECLARE @LastID int
SET @LastID=SCOPE_IDENTITY()

select * from yourtable where identityfield=@LastID

brill. once again many thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 12:35:21

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources