471,119 Members | 1,081 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,119 software developers and data experts.

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

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/
Jul 23 '05 #1
4 13910
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
--

Jul 23 '05 #2
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 *.

Jul 23 '05 #3
"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/
Jul 23 '05 #4
"--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/
Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Roland | last post: by
3 posts views Thread by Harry | last post: by
1 post views Thread by Yama | last post: by
1 post views Thread by John Martin | last post: by
3 posts views Thread by Jim Heavey | last post: by
12 posts views Thread by EMW | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.