Get the newest added records in the database

Question

I can only describe the question by example.. If I have this data

    use-id         user-name          add-date
---------------------------------------------------
    2              sami               17/1/2011
    2              sami               18/1/2011
    3              alaa               18/1/2011
    4              jamal              19/1/2011

I want to select the newest row for each user, I want this data to result :

    use-id         user-name          add-date
---------------------------------------------------
    2              sami               18/1/2011
    3              alaa               18/1/2011
    4              jamal              19/1/2011

for each unique user-id I want to get the newsest added record. how ?

Thanks for this question, I need the answer too. – panoet Dec 05 '14 at 10:36 — panoet, Dec 05 '14 at 10:36

Matt Fenwick · Accepted Answer · 2012-02-07T21:46:52.030

0

One way is to get the date of the newest record for each user:

select `use-id`, max(`add-date`) as `latest` 
from <table_name> 
group by `use-id`

We'll call that query newest_records, and use it to select only those rows that have the latest date:

select a.*
from <table_name>
inner join newest_records b
on a.`use-id` = b.`use-id` and a.`add-date` = b.`latest`

Edit:

Putting it all together (copy/paste), we have:

select a.*
from <table_name>
inner join (  
  select `use-id`, max(`add-date`) as `latest` 
  from <table_name> 
  group by `use-id`) b
on a.`use-id` = b.`use-id` and a.`add-date` = b.`latest`

edited Feb 07 '12 at 21:46

answered Feb 07 '12 at 21:26

Matt Fenwick

48,199
22
128
192

Why do need the second query? doesn't the first do what is required ? – Adham Feb 07 '12 at 21:36
@Bader -- no, the first query only gets the `use-id` and `date` -- **not the entire rows**. The second query uses the first to get the rest of the columns in the rows of interest. Of course you could combine them into one query, I just thought splitting them up made it look clearer. – Matt Fenwick Feb 07 '12 at 21:40
thanks, select `use-id`,`use-name`, max(`add-date`) as `latest` from group by `use-id` .. is correct ? – Adham Feb 07 '12 at 21:43
@Bader -- **No!!!** You should **never** select non-group-by columns without aggregate functions (unless you want strange things to happen)! That's why the query is slightly more complicated. – Matt Fenwick Feb 07 '12 at 21:45

score 0 · Answer 2 · answered Feb 07 '12 at 22:03

As I see it User-Id determines User-Name (User-Id --> User-Name). That means there won't be two equal User-Ids with different User-Names. And your data clearly shows this with User-Id number 2, which both have the same User-Name (sami).

So it would be valid to do a simpler and more efficient query:

select user-id, user-name, max(add-date) from table1
group by user-id, user-name

Note: MySql also allows you to remove the user-name from the group by

Now, clearly your table is lacking Normalization. Your table should look like this:

YourTable(User-Id, Add-Date)

And you should also have another table Users that should look like this:

Users(User-Id, User-Name)

And to get the result you're expecting in this new schema you should joing both tables.

Get the newest added records in the database

2 Answers2