Menu
I'm trying to get the ip, user, and most recent timestamp from a table which may contain both the current ip for a user and one or more prior ips. I'd like one row for each user containing the most recent ip and the associated timestamp. So if a table looks like this:
username | ip | time_stamp --------------|----------|-------------- ted | 1.2.3.4 | 10 jerry | 5.6.6.7 | 12 ted | 8.8.8.8 | 30 I'd expect the output of the query to be:
jerry | 5.6.6.7 | 12 ted | 8.8.8.8 | 30 Can I do this in a single sql query? In case it matters, the DBMS is Postgresql.
917
Here is the syntax that we can use to get the latest date records in SQL Server. Select column_name, .. From table_name Order By date_column Desc; Now, let's use the given syntax to select the last 10 records from our sample table.
METHOD 1 : Using LIMIT clause in descending order As we know that LIMIT clause gives the no. of specified rows from specifies row. We will retrieve last 5 rows in descending order using LIMIT and ORDER BY clauses and finally make the resultant rows ascending.
The group by will always return the first record in the group on the result set. SELECT id, category_id, post_title FROM posts WHERE id IN ( SELECT MAX(id) FROM posts GROUP BY category_id ); This will return the posts with the highest IDs in each group.
Try this:
Select u.[username] ,u.[ip] ,q.[time_stamp] From [users] As u Inner Join ( Select [username] ,max(time_stamp) as [time_stamp] From [users] Group By [username]) As [q] On u.username = q.username And u.time_stamp = q.time_stamp If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
