SEO

#1 (**permalink**) 02-29-2008, 06:12 AM

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/

#2 (**permalink**) 02-29-2008, 06:12 AM

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

#3 (**permalink**) 02-29-2008, 06:12 AM

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 *.

#4 (**permalink**) 02-29-2008, 06:12 AM

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@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/

#5 (**permalink**) 02-29-2008, 06:12 AM

"--CELKO--" <jcelko212@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/

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

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