How to Select Only the Latest Entry in a Table

How to select the last record of a table in SQL?

Without any further information, which Database etc the best we can do is something like

Sql Server

SELECT TOP 1 * FROM Table ORDER BY ID DESC

MySql

SELECT * FROM Table ORDER BY ID DESC LIMIT 1

How to select only the last entry in my table?

You can use row_number():

SELECT *
FROM Matieres m LEFT JOIN
     (SELECT r.*,
             ROW_NUMBER() OVER (PARTITION BY id_Matiere, id_user ORDER BY time_of_insertions DESC) as seqnum
      FROM Resultats r
      WHERE r.ID_USER = :userid 
     ) r
     ON m.id = r.ID_MATIERE AND
        seqnum = 1
WHERE Active = TRUE AND ID_Formation = :formation;

How to keep the latest record of a table

One method is for selecting the most recent rows is:

select t.*
from releng_retry_test_phases t
where t.date = (select max(t2.date) from releng_retry_test_phases t2 where t2.train = t.train);

If you actually want to modify the table and delete the older rows;

delete t
    from releng_retry_test_phases t join
         (select t2.train, max(date) as max_date
          from releng_retry_test_phases t2
          group by t2.train
         ) t2
         using (train)
    where t.date < t2.max_date;

how do I query sql for a latest record date for each user

select t.username, t.date, t.value
from MyTable t
inner join (
    select username, max(date) as MaxDate
    from MyTable
    group by username
) tm on t.username = tm.username and t.date = tm.MaxDate

SQL Statement Only latest entry of the day

This one would give you the very last record:

select top 1 * from GAS_COUNTER order by TS desc 

Here is one that would give you last records for every day:

select VALUE from GAS_COUNTER 
where TS in (
    select max(TS) from GAS_COUNTER group by to_date(TS,'yyyy-mm-dd')
)

Depending on the database you are using you might need to replace/adjust to_date(TS,'yyyy-mm-dd') function. Basically it should extract date-only part from the timestamp.

SQL - select only latest record for each foreign key

Using ROW_NUMBER:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY foreign_key
                                   ORDER BY created_at DESC) rn
    FROM yourTable t
)

SELECT foreign_key, string, created_at
FROM cte
WHERE rn = 1;

Another approach, using joins:

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT foreign_key, MAX(created_at) AS max_created_at
    FROM yourTable
    GROUP BY foreign_key
) t2
    ON t2.foreign_key = t1.foreign_key AND
       t2.max_created_at = t1.created_at;

Select row with most recent date per user

Query:

SQLFIDDLEExample

SELECT t1.*
FROM lms_attendance t1
WHERE t1.time = (SELECT MAX(t2.time)
                 FROM lms_attendance t2
                 WHERE t2.user = t1.user)

Result:

| ID | USER |       TIME |  IO |
--------------------------------
|  2 |    9 | 1370931664 | out |
|  3 |    6 | 1370932128 | out |
|  5 |   12 | 1370933037 |  in |

Note that if a user has multiple records with the same "maximum" time, the query above will return more than one record. If you only want 1 record per user, use the query below:

SQLFIDDLEExample

SELECT t1.*
FROM lms_attendance t1
WHERE t1.id = (SELECT t2.id
                 FROM lms_attendance t2
                 WHERE t2.user = t1.user            
                 ORDER BY t2.id DESC
                 LIMIT 1)

How to select the last record of each ID

You can use a window function called ROW_NUMBER.Here is a solution for you given below. I have also made a demo query in db-fiddle for you. Please check link Demo Code in DB-Fiddle

WITH CTE AS
(SELECT product, user_id,
       ROW_NUMBER() OVER(PARTITION BY user_id order by product desc)
       as RN
FROM Mytable)
SELECT product, user_id FROM CTE WHERE RN=1 ;

How can I select the latest entry of mySQL database table?

Why not just

SELECT name, id FROM data ORDER BY id DESC LIMIT 1

---

Related Topics