Last Function in SQL Server ?

27 May 2005 3:18 PM

Oded Dror

Hi there,

--Access Query that Doesn't work in SQL
SELECT Last([FirstName]) AS First_Name, Last([LastName]) AS Last_Name
FROM Employees

--Access Query that work with SQL if we have an EmployeeID
SELECT TOP 1 FirstName,LastName
FROM Employees
ORDER BY EmployeeID DESC

--SQL only if we don't have an EmployeeID
Declare @FirstName varchar(20)
Declare @LastName varchar(20)
SELECT @FirstName = FirstName, @LastName = LastName
from Employees
SELECT @FirstName, @LastName

--Test1 to make sure that we looking for last record
Select * FROM Employees

What is the equivalent Last access function in T-SQL

Thanks
Oded Dror
Email: odedd***@cox.net

27 May 2005 3:40 PM

Alejandro Mesa

First or Last concept can be applied just to an ordered set.

-- first
select top 1 @fn = firstname, @ln = lastname
from dbo.employees
order by employeeid

-- last
select top 1 @fn = firstname, @ln = lastname
from dbo.employees
order by employeeid desc

-- first
select top 1 @fn = firstname, @ln = lastname
from dbo.employees
order by firstname, lastname

-- last
select top 1 @fn = firstname, @ln = lastname
from dbo.employees
order by firstname desc, lastname desc

AMB

Show quoteHide quote

"Oded Dror" wrote:

> Hi there,
>
> --Access Query that Doesn't work in SQL
> SELECT Last([FirstName]) AS First_Name, Last([LastName]) AS Last_Name
> FROM Employees
>
> --Access Query that work with SQL if we have an EmployeeID
> SELECT TOP 1 FirstName,LastName
> FROM Employees
> ORDER BY EmployeeID DESC
>
> --SQL only if we don't have an EmployeeID
> Declare @FirstName varchar(20)
> Declare @LastName varchar(20)
> SELECT @FirstName = FirstName, @LastName = LastName
> from Employees
> SELECT @FirstName, @LastName
>
> --Test1 to make sure that we looking for last record
> Select * FROM Employees
>
> What is the equivalent Last access function in T-SQL
>
> Thanks
> Oded Dror
> Email: odedd***@cox.net
>
>
>

27 May 2005 3:40 PM

David Portas

A table in SQL has no inherent logical order so there is no "first" or
"last" row. How do you want to define which row should be the last?

I believe that if you don't specify ORDER BY then Access just picks the
last record based on insertion order. However, SQL Server doesn't
preserve the information about insertion order unless you explicitly
create a column or columns to record that information.

For example, if you have a modification_date in your table you could
take the latest date:

SELECT first_name, last_name
FROM Employees
WHERE modification_date =
(SELECT MAX(modification_date)
FROM Employees)

--
David Portas
SQL Server MVP
--

--
David Portas
SQL Server MVP
--

27 May 2005 3:43 PM

Thomas Coleman

The concept of "last" does not exist in SQL nor SQL Server as SQL thinks in
terms of sets. Last must derived from the data on which you are querying using
things like alphabetic sorting or datetime values. Access is able to provide
this functionality because Access data is stored sequentially in the order in
which it was entered. No such guarantee exists in most database products like
SQL Server or Oracle.

Thomas

Show quoteHide quote

"Oded Dror" <odedd***@cox.net> wrote in message
news:%23gtUM9sYFHA.2996@TK2MSFTNGP10.phx.gbl...
> Hi there,
>
> --Access Query that Doesn't work in SQL
> SELECT Last([FirstName]) AS First_Name, Last([LastName]) AS Last_Name
> FROM Employees
>
> --Access Query that work with SQL if we have an EmployeeID
> SELECT TOP 1 FirstName,LastName
> FROM Employees
> ORDER BY EmployeeID DESC
>
> --SQL only if we don't have an EmployeeID
> Declare @FirstName varchar(20)
> Declare @LastName varchar(20)
> SELECT @FirstName = FirstName, @LastName = LastName
> from Employees
> SELECT @FirstName, @LastName
>
> --Test1 to make sure that we looking for last record
> Select * FROM Employees
>
> What is the equivalent Last access function in T-SQL
>
> Thanks
> Oded Dror
> Email: odedd***@cox.net
>
>