Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Getting Last Row in Table 1
- Thread starter skaskas
- Start date
-
1
- #2
Well, there is no LAST function, mostly because relational databases process data as 'sets', and there is no logical notion of the 'first' or the 'last' record'. They are all just part of the set.
However, what is your notion of 'last' as it relates to your current problem? Is it the last record entered, based on maybe either (a) a date field, or (b) the last IDENTITY value used in the table??
More often, when referring to the 'last' record, folks are thinking about the highest value in some particular sort sequence. If that's the case, the problem is usually handled with the TOP function, by ordering the rows in reverse (i.e. descending sequence).
So you might do:
Select TOP 1 LastName, FirstName, Address
From Clients
Order by LastName DESC
to get the "last" client name (maybe Dan ZANDER) in the table.
But ordering 24 million records will be a pretty intensive operation and might blow the resources available to the server.
However, what is your notion of 'last' as it relates to your current problem? Is it the last record entered, based on maybe either (a) a date field, or (b) the last IDENTITY value used in the table??
More often, when referring to the 'last' record, folks are thinking about the highest value in some particular sort sequence. If that's the case, the problem is usually handled with the TOP function, by ordering the rows in reverse (i.e. descending sequence).
So you might do:
Select TOP 1 LastName, FirstName, Address
From Clients
Order by LastName DESC
to get the "last" client name (maybe Dan ZANDER) in the table.
But ordering 24 million records will be a pretty intensive operation and might blow the resources available to the server.
tlbroadbent
MIS
If the column you that you order by - lastname in bperry's example - is indexed, SQL will traverse the index backward thus avoiding any sorting at all. Thus the query suggested by bperry will be very efficient at least on SQL 2000.
However, the following query can be even more efficient. At least it was in my testing. Selecting the Max value of an indexed column can also be done by traversing the index backward.
Select Top 1 LastName, FirstName, Address
From Clients
Where LastName=(Select Max(LastName) From Clients)
Order By FirstName Desc
If the column is not indexed the above query will also usually run faster than the Top 1, Order By query. Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.
However, the following query can be even more efficient. At least it was in my testing. Selecting the Max value of an indexed column can also be done by traversing the index backward.
Select Top 1 LastName, FirstName, Address
From Clients
Where LastName=(Select Max(LastName) From Clients)
Order By FirstName Desc
If the column is not indexed the above query will also usually run faster than the Top 1, Order By query. Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.
- Status
Similar threads
- Locked
- Question
- Locked
- Question