getting latest(last inserted) records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

getting latest(last inserted) records

Author

Topic

pithhelmet
Posting Yak Master

183 Posts

Posted - 2004-08-27 : 16:04:17

Hi Everyone -

I have inherited an application that logs
GPS coordinates form multiple laptop units.

All this data is being written to a single
database table.

there is an autoincrement field, along with the
IP of the incoming packet.

What i need to do is get the latest record
for each of the unique IP addresses.

the table name is
gps_history

the autoincrement field name is
history_id

and the IP field name is
sender_ip

please advise on a fast SQL query
to get the last record for each unique IP address.

thanks
tony

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-27 : 16:12:57

I think this is what you need:

SELECT sender_ip, MAX(history_id)
FROM gps_history
GROUP BY sender_ip

Tara

pithhelmet
Posting Yak Master

183 Posts

Posted - 2004-08-27 : 16:22:08

Tara -

again you have saved my bacon!!

thanks a million!!!
take care
tony

pithhelmet
Posting Yak Master

183 Posts

Posted - 2004-08-27 : 16:31:14

Tara and all -

Thanks for the reply - it works great -

now a similar problem....

I would like to get all the fields associated with the record that is returned in
the query above...

how can i accomplish that???

thanks again

take care
tony

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-27 : 16:34:46

SELECT gh.sender_ip, t.LastOne, gh.SOmeColumnName, ...
FROM gps_history gh
INNER JOIN
(
SELECT sender_ip, MAX(history_id) AS LastOne
FROM gps_history
GROUP BY sender_ip
) t
ON gh.sender_ip = t.sender_ip

Tara

pithhelmet
Posting Yak Master

183 Posts

Posted - 2004-08-27 : 16:39:15

Perfect!!

as usual!

thanks again for all your help!!

take care
tony

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources