LAST FUNCTION

I have a sql database that I am trying to make this query work. The
problem is that I can't get the right info out, right'
So, I exported two tables to an Access database, and this is my query
that works beautifully.. Question is, I don't want to keep exporting
this info everytime, I want to make this query in SQL and leave it there
or use it for SQL Reporting Services..
Note that the table names changes from Access to SQL, but I have made
all those mods already.. The problem is with the "LAST" function on line
5 from the Access Query. and this is the error I'm getting when
applying to SQL:
Server: Msg 195, Level 15, State 10, Line 1
'Last' is not a recognized function name.
ACCESS QUERY:
SELECT Users.last_name, Users.first_name, Last(Results.EventStartDtTm)
AS LastLogin, Results.UserID
FROM Results INNER JOIN Users ON Results.UserID = Users.user_name
GROUP BY Users.last_name, Users.first_name, Results.UserID
HAVING (((Last(Results.EventStartDtTm))<Date()-90))
ORDER BY Users.last_name, Users.first_name;
========================================
===========================
SQL Query::
SELECT prod9eps.portal.Users.last_name, rod9eps.portal.Users.first_name,
Last(prod9phy.dbo.UtilLogRecord.EventStartDtTm) AS LastLogin,
prod9phy.dbo.UtilLogRecord.UserID FROM prod9phy INNER JOIN
prod9eps.portal.Users ON prod9phy.dbo.UtilLogRecord.UserID =
prod9eps.portal.Users.user_name
GROUP BY prod9eps.portal.Users.last_name,
prod9eps.portal.Users.first_name, prod9phy.dbo.UtilLogRecord.UserID
HAVING (((Last(prod9phy.dbo.UtilLogRecord.EventStartDtTm))<Date()-90))
ORDER BY prod9eps.portal.Users.last_name, prod9eps.portal.Users.first_name;Please post DDL, sample data and required end results, otherwise we can only
guess.
http://www.aspfaq.com/etiquette.asp?id=5006
The Access version of your query doesn't look like a well-defined result.
The ORDER BY list is what should drive the result of the LAST function in
Access but in your case the ORDER BY columns are also grouped so the result
of the LAST function will be determined by the physical record order in the
table. Since you haven't specified what that order is it's not clear just
what result you want.
In SQL Server there is no direct equivalent of the Access FIRST and LAST
functions. A table in SQL is not logically ordered so there is no first or
last row - you have to specify the column/expression that determines which
rows you want.
Here's an example taken from the Pubs database giving the first and last
title based on the MIN and MAX values of Title_id.
SELECT
(SELECT title
FROM Titles
WHERE title_id
= (SELECT MIN(title_id)
FROM Titles)) AS First_Title,
(SELECT title
FROM Titles
WHERE title_id
= (SELECT MAX(title_id)
FROM Titles)) AS Last_Title
A common requirement is to extract a single row of a group based on some
criteria. The query below retrieves values from the "Last" (maximum
title_id) row for each Type in the Titles table.
SELECT T1.type, T1.title_id, T1.title, T1.price
FROM titles AS T1
JOIN
(SELECT MAX(title_id) AS title_id
FROM titles
GROUP BY type) AS T2
ON T1.title_id = T2.title_id
If you need to retrieve the first or last rows based on the order in which
they were entered into a table then you need to add a DATETIME column to the
table to record the entry date:
date_created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
David Portas
SQL Server MVP
--|||David, thank you for your explanation, but there is a DATETIME field.
The "Results.EventStartDtTm" is a DATETIME
I can't use MAX because there is number, the same goes for MIN. 99% of
the data is text.. Except for the "Results.EventStartDtTm" that is
(01/01/00 00:00:00)
Again, my query works great in Access but not in SQL.
David Portas wrote:
> Please post DDL, sample data and required end results, otherwise we can on
ly
> guess.
> http://www.aspfaq.com/etiquette.asp?id=5006
> The Access version of your query doesn't look like a well-defined result.
> The ORDER BY list is what should drive the result of the LAST function in
> Access but in your case the ORDER BY columns are also grouped so the resul
t
> of the LAST function will be determined by the physical record order in th
e
> table. Since you haven't specified what that order is it's not clear just
> what result you want.
> In SQL Server there is no direct equivalent of the Access FIRST and LAST
> functions. A table in SQL is not logically ordered so there is no first or
> last row - you have to specify the column/expression that determines which
> rows you want.
> Here's an example taken from the Pubs database giving the first and last
> title based on the MIN and MAX values of Title_id.
> SELECT
> (SELECT title
> FROM Titles
> WHERE title_id
> = (SELECT MIN(title_id)
> FROM Titles)) AS First_Title,
> (SELECT title
> FROM Titles
> WHERE title_id
> = (SELECT MAX(title_id)
> FROM Titles)) AS Last_Title
> A common requirement is to extract a single row of a group based on some
> criteria. The query below retrieves values from the "Last" (maximum
> title_id) row for each Type in the Titles table.
> SELECT T1.type, T1.title_id, T1.title, T1.price
> FROM titles AS T1
> JOIN
> (SELECT MAX(title_id) AS title_id
> FROM titles
> GROUP BY type) AS T2
> ON T1.title_id = T2.title_id
> If you need to retrieve the first or last rows based on the order in which
> they were entered into a table then you need to add a DATETIME column to t
he
> table to record the entry date:
> date_created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
>|||> I can't use MAX because there is number
Please explain that statement. Sounds to me like MAX is exactly what you
want but if you won't give us a better description of your problem then how
can we help you? (see the link I posted previously).
David Portas
SQL Server MVP
--|||Ok, here we go..
I have the following file, of course, there is plenty more entries than
this, but just sample..
Tablename = UtilLogRecord
FieldNames::: Event,UserID,EventStartDtTm
Sign-In,aabraham,08/26/04 11:49:58
Sign-In,aabraham,08/26/04 12:13:28
Sign-In,aabraham,08/27/04 10:16:16
Sign-In,aabraham,08/27/04 10:17:53
Sign-In,aabraham,08/27/04 10:12:31
Sign-In,aabraham,08/27/04 11:33:34
Sign-In,aabraham,08/27/04 11:58:09
Sign-In,aabraham,08/27/04 11:59:24
Sign-In,aabraham,08/27/04 12:00:05
Sign-In,aabraham,08/27/04 13:11:58
Sign-In,aabraham,08/27/04 13:38:00
Sign-In,aabraham,08/27/04 13:39:11
Sign-In,aabraham,08/27/04 13:36:27
I also have the following:
Tablename = Users
FieldNames:: user_name,first_name,last_name,role
fherdoci,Filiberto,Herdocia,404
mromano,Mary,Romano,404
sparker,Susan,Parker,506
ltidwell,Liana,Tidwell,506
When I do:
============================
SELECT Users.last_name, Users.first_name, Last(Results.EventStartDtTm)
AS LastLogin, Results.UserID
FROM Results INNER JOIN Users ON Results.UserID = Users.user_name
GROUP BY Users.last_name, Users.first_name, Results.UserID
HAVING (((Last(Results.EventStartDtTm))<Date()-90))
ORDER BY Users.last_name, Users.first_name;
=========================
I get
Sign-In,aabraham,08/27/04 13:36:27 (which is the last time this user
logged in to the system 08/27/04)
In MSSQL, I tried the MAX and it does not work at all. If this were
you, how would you write that statement..
Again, it works in Access and I can keep on doing it that way, but I
rather not.
./ray medina
David Portas wrote:
>
> Please explain that statement. Sounds to me like MAX is exactly what you
> want but if you won't give us a better description of your problem then ho
w
> can we help you? (see the link I posted previously).
>|||Ray,
Your code does not match your data,
and your answer does not give the last
time the user logged in. The most recent
login is
Sign-In,aabraham,08/27/04 13:39:11
Your result is wrong, if you want the
most recent login, but using MAX should
work.
The information you posted is not much help.
Your query refers to a table called [Results],
which you don't give the structure of, and it
joins UserID to user_name, which for this
sample data gives no match. You say your
query returns Sign-In,aabraham,08/27/04 13:36:27,
and you say this is the most recent login, but it
is not, and you don't give data that shows
how the query returns this.
It is also not helpful at all for you to say
"I tried the MAX and it does not work at all."
without explaining at all what you mean. When
you replace LAST with MAX, what doesn't work?
Wrong answers, no results, computer turns into
a piece of burned toast? Please give more information
in a form that is useful to someone trying to help.
If you learn to present your problems clearly and
thoroughly, you may soon discover that you have
fewer problems, too.
Steve Kass
Drew University
Ray Medina wrote:
> Ok, here we go..
> I have the following file, of course, there is plenty more entries
> than this, but just sample..
> Tablename = UtilLogRecord
> FieldNames::: Event,UserID,EventStartDtTm
> Sign-In,aabraham,08/26/04 11:49:58
> Sign-In,aabraham,08/26/04 12:13:28
> Sign-In,aabraham,08/27/04 10:16:16
> Sign-In,aabraham,08/27/04 10:17:53
> Sign-In,aabraham,08/27/04 10:12:31
> Sign-In,aabraham,08/27/04 11:33:34
> Sign-In,aabraham,08/27/04 11:58:09
> Sign-In,aabraham,08/27/04 11:59:24
> Sign-In,aabraham,08/27/04 12:00:05
> Sign-In,aabraham,08/27/04 13:11:58
> Sign-In,aabraham,08/27/04 13:38:00
> Sign-In,aabraham,08/27/04 13:39:11
> Sign-In,aabraham,08/27/04 13:36:27
> I also have the following:
> Tablename = Users
> FieldNames:: user_name,first_name,last_name,role
> fherdoci,Filiberto,Herdocia,404
> mromano,Mary,Romano,404
> sparker,Susan,Parker,506
> ltidwell,Liana,Tidwell,506
> When I do:
> ============================
> SELECT Users.last_name, Users.first_name, Last(Results.EventStartDtTm)
> AS LastLogin, Results.UserID
> FROM Results INNER JOIN Users ON Results.UserID = Users.user_name
> GROUP BY Users.last_name, Users.first_name, Results.UserID
> HAVING (((Last(Results.EventStartDtTm))<Date()-90))
> ORDER BY Users.last_name, Users.first_name;
> =========================
> I get
> Sign-In,aabraham,08/27/04 13:36:27 (which is the last time this user
> logged in to the system 08/27/04)
> In MSSQL, I tried the MAX and it does not work at all. If this were
> you, how would you write that statement..
> Again, it works in Access and I can keep on doing it that way, but I
> rather not.
> ./ray medina
> David Portas wrote:
>