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 ?

Answer 1

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`