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]

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

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--]

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

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_TIMESTA MP 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_ti me)
FROM Foobar AS F2
WHERE F1.user_name = F2.user_name);
-- use column names in production code, not SELECT *.

[John A Fotheringham]

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

"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote:
[color=blue]
>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)[/color]

Thanks
[color=blue]
>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.[/color]

Point noted.
[color=blue]
>The usual recommendation that you shouldn't use SELECT * in production
>code also applies.[/color]

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]

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

"--CELKO--" <jcelko212@eart hlink.net> wrote:
[color=blue]
>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?[/color]

Sorry. Will do in future.

Thanks for the solution.
--
HTML-to-text and markup removal with Detagger

http://www.jafsoft.com/detagger/