Last Function in SQL Server ?

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: odeddror@.cox.netA 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
--|||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
"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: odeddror@.cox.net
>
>|||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 usi
ng
things like alphabetic sorting or datetime values. Access is able to provide
this functionality because Access data is stored sequentially in the order i
n
which it was entered. No such guarantee exists in most database products lik
e
SQL Server or Oracle.
Thomas
"Oded Dror" <odeddror@.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: odeddror@.cox.net
>