I think there is a better way.

It seems like im missing something, what i want to do is select the last row
from a table that relates to a specific person. My table and select
statement follow, the select statement works fine, works great as a matter
of fact, but i feel as if i am missing something (well something in addition
to a better understanding of sql but im still working on that).

CREATE TABLE Logins
(
ID IDENTITY(1,1) PRIMARY KEY
Username VARCHAR(100),
LoginTime DATETIME
)

-- Now i want to select the last login time for a specific user, so this is
what i have been doing.
SELECT TOP 1 FROM Logins WHERE Username = 'x' ORDER BY ID DESC

Best,
Muhd.Ok i just realized this might not be the best example because i think you
could probably just compare the date value of logintime and select the one
thats the highest (although im not sure how to do that either). So maybe
this is a better example, where what i want to do is select the most recent
comment made by someone.

CREATE TABLE Comments
(
ID IDENTITY(1,1) PRIMARY KEY,
UserName VARCHAR(100),
Comments VARCHAR(1000)
)

And then as in the previous example i would simply select the top 1 row
sorted desc by id. Of course maybe i touched on the solution, if i date
stamped each entry i could then select the most recent entry (its that most
recent entry thats giving me problems).

"Muhd" <muhd@.binarydemon.com> wrote in message
news:nLDNb.113982$JQ1.55283@.pd7tw1no...
> It seems like im missing something, what i want to do is select the last
row
> from a table that relates to a specific person. My table and select
> statement follow, the select statement works fine, works great as a matter
> of fact, but i feel as if i am missing something (well something in
addition
> to a better understanding of sql but im still working on that).
> CREATE TABLE Logins
> (
> ID IDENTITY(1,1) PRIMARY KEY
> Username VARCHAR(100),
> LoginTime DATETIME
> )
> -- Now i want to select the last login time for a specific user, so this
is
> what i have been doing.
> SELECT TOP 1 FROM Logins WHERE Username = 'x' ORDER BY ID DESC
> Best,
> Muhd.|||"Muhd" <muhd@.binarydemon.com> wrote in message news:nLDNb.113982$JQ1.55283@.pd7tw1no...
> It seems like im missing something, what i want to do is select the last row
> from a table that relates to a specific person. My table and select
> statement follow, the select statement works fine, works great as a matter
> of fact, but i feel as if i am missing something (well something in addition
> to a better understanding of sql but im still working on that).
> CREATE TABLE Logins
> (
> ID IDENTITY(1,1) PRIMARY KEY
> Username VARCHAR(100),
> LoginTime DATETIME
> )
> -- Now i want to select the last login time for a specific user, so this is
> what i have been doing.
> SELECT TOP 1 FROM Logins WHERE Username = 'x' ORDER BY ID DESC
> Best,
> Muhd.

CREATE VIEW LatestLogins (username, login_time)
AS
SELECT Username, LoginTime
FROM Logins AS L1
WHERE NOT EXISTS (SELECT *
FROM Logins AS L2
WHERE L2.Username = L1.Username AND
L2.LoginTime > L1.LoginTime)

SELECT username, login_time
FROM LatestLogins
WHERE username = 'x'

Regards,
jag|||Ok one last comment, by "last row" i actually mean the last entry that
someone made. I know enough to understand there isn't a "last row" in a
relational database. Just thought i would clarify.

"Muhd" <muhd@.binarydemon.com> wrote in message
news:nLDNb.113982$JQ1.55283@.pd7tw1no...
> It seems like im missing something, what i want to do is select the last
row
> from a table that relates to a specific person. My table and select
> statement follow, the select statement works fine, works great as a matter
> of fact, but i feel as if i am missing something (well something in
addition
> to a better understanding of sql but im still working on that).
> CREATE TABLE Logins
> (
> ID IDENTITY(1,1) PRIMARY KEY
> Username VARCHAR(100),
> LoginTime DATETIME
> )
> -- Now i want to select the last login time for a specific user, so this
is
> what i have been doing.
> SELECT TOP 1 FROM Logins WHERE Username = 'x' ORDER BY ID DESC
> Best,
> Muhd.|||> if i date
> stamped each entry i could then select the most recent entry (its that
most
> recent entry thats giving me problems).

Exactly. If you don't put that date/time stamp in your table then you
haven't recorded the information you need for your query. I suggest you
don't rely on the sequence of an identity column since identity isn't a
"real" attribute of your entity and it will cause you problems if you want
re-seed the value or merge it with data from another table.

SELECT username, comments
FROM Comments AS C
WHERE date_created =
(SELECT MAX(date_created)
FROM Comments
WHERE username = C.username)

--
David Portas
----
Please reply only to the newsgroup
--