Hi.. i have a table with 3 columns: Name, Service, and Date.

I want to know for each name, wich is the last service that used.

i would do:

select Name, Last(Date), Last(Service)
from (
select top 100 percent Name, Date, Service
from dbo.aTable
order by Name, Date
) q
group by Name

but "Last" don't exists in sql server... if i use "Min(Service)" it will get
the alphabetically lowest. not the last one in the order....

Any ideas?

There is no such concept as last or first in relational
databases as a table by definition has unordered columns and
unordered rows. So if the order of the rows is meaningless,
there is no concept as first or last. But....without knowing
more about your data and the table structure, it's hard to
give you a correct answer but possibly something like this
may work for you:
select Name, Service, Date
From YourTable A
where Date =
(
Select Max(Date)
from YourTable B
where A.Name = B.Name
)

-Sue

On Mon, 8 Sep 2003 17:20:55 -0400, "Andrew Ofthesong"
<Andrew (AT) NoSpam (DOT) net> wrote:

Hi sue.. is something like you said.. but in your example i still can't get
the "service".

The data is like:

Name Service Date
Sue Serv01 8/Sep
Sue Serv02 9/Sep
Bill Serv01 7/Sep
Bill Serv02 5/Sep

So the result should be, the last service, and date for each user...
Sue Serv02 9/Sep
Bill Serv01 7/Sep

I can get the alst date for each user, but i'm complicated with retrieving
the service associated with that user and name.

I have thinked of left joining the result of name and service with the whole
data... but i think t's not good solution... Is it?


"Sue Hoegemeier" <Sue_H (AT) nomail (DOT) please> escribió en el mensaje
news:396qlvk89cs2qtlod98sc3he4oaqri360r (AT) 4ax (DOT) com...

I just populated a test table with the same data and it
worked. So either you missed something in the example posted
or you have something else in your table definition and need
to post the DDL for us to figure out a solution for you.

-Sue

On Tue, 9 Sep 2003 10:38:12 -0400, "Andrew Ofthesong"
<Andrew (AT) NoSpam (DOT) net> wrote:

And I can confirm Sue's excellent example of syntax.

CREATE TABLE MyTable(colName varchar(10), Service varchar(10), colDate
smalldatetime)
GO
INSERT MyTable VALUES('Sue','Serv01','20030908')
INSERT MyTable VALUES('Sue','Serv02','20030909')
INSERT MyTable VALUES('Bill','Serv01','20030907')
INSERT MyTable VALUES('Bill','Serv01','20030905')
GO

SELECT colName, Service, colDate
FROM MyTable
WHERE ColDate = (SELECT MAX(ColDate) FROM MyTable T2 WHERE T2.colName =
MyTable.ColName)


--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Sue Hoegemeier" <Sue_H (AT) nomail (DOT) please> wrote

¡Excelent Code!...

Altought I still don't quite understand the "...WHERE ColDate = (SELECT
MAX..." part...

PD: I guess that if more than one equal registry can exist (same name,
service, date), the "distinct" would bu used in the first SELECT, right?

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> escribió en el mensaje
news:e6NSbZwdDHA.2416 (AT) TK2MSFTNGP09 (DOT) phx.gbl...

In the code I ask for the rows that pertain to the last entry for each
person. This means the MAX() of the date. I qualify that by joining
back from the subquery to the colName field in my outside query.






Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org