How to get the last occurence of rows containing disticnt value in one column

[John A Fotheringham]

I have a table that tracks GPS records broadly speaking as follows

ts DATETIME
username VARCHAR(16)
...
GPS data
...

I want to select the most recent GPS data for each distinct user.
That is, the table will have may records for a given username, but I
only want the most recent for each one.

For a single user I know I can do

SELECT TOP 1 * from <table> order by ts desc

But I want a set of results that effectively does this for
all users, and I can't work out the necessary query/subquery I
should be using.

I'm sure I'm missing something fairly obvious, so usual newbie
disclaimers apply.
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/

[David Portas]

What is the primary key? I'll assume the key consists of (ts,
username), in which case the folllowing should do what you want:

SELECT ts, username, ... /* other columns */
FROM YourTable AS T
WHERE ts =
(SELECT MAX(ts)
FROM YourTable
WHERE username = T.username)

It really helps if you include DDL with questions like this (basically
a CREATE TABLE statement, including keys and constraints). The exact
table structure may make a big difference to the possible solutions.
The usual recommendation that you shouldn't use SELECT * in production
code also applies.

--
David Portas
SQL Server MVP
--

[--CELKO--]

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. Even pseudo-DDL is better than narratives. Is this
what you meant?

CREATE TABLE Foobar
(event_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
user_name VARCHAR(16) NOT NULL,
stuff_1 INTEGER NOT NULL,
stuff_2 INTEGER NOT NULL,
..
stuff_n INTEGER NOT NULL,
PRIMARY KEY (user_name, event_time));

SELECT F1.*
FROM Foobar AS F1
WHERE F1.event_time
= (SELECT MAX(f2.event_time)
FROM Foobar AS F2
WHERE F1.user_name = F2.user_name);
-- use column names in production code, not SELECT *.

[John A Fotheringham]

"David Portas" <RE****************************@acm.org> wrote:

What is the primary key? I'll assume the key consists of (ts,
username), in which case the folllowing should do what you want:

SELECT ts, username, ... /* other columns */
FROM YourTable AS T
WHERE ts =
(SELECT MAX(ts)
FROM YourTable
WHERE username = T.username)
Thanks
It really helps if you include DDL with questions like this (basically
a CREATE TABLE statement, including keys and constraints). The exact
table structure may make a big difference to the possible solutions.
Point noted.
The usual recommendation that you shouldn't use SELECT * in production
code also applies.

I realise this. That was just me being lazy (although for some of my
selects I do want the entire row).

--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/

[John A Fotheringham]

"--CELKO--" <jc*******@earthlink.net> wrote:

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. Even pseudo-DDL is better than narratives. Is this
what you meant?

Sorry. Will do in future.

Thanks for the solution.
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/