SQL query to select most recent of duplicates

Question

I have a table of values, with a date stored against each entry for example

• Name
• Age
• PaymentAmount
• Date

Can someone help me to write a query that would show the most recent payment only of any person within a certain age range.

E.g If I had 5 entries, and wanted the most recent payment of all people aged 20-25

Allan, 45, $1500, 1/1/2014
Tim, 22, $1500, 1/2/2001
John, 25, $2000, 2/3/2001
Tim, 22, $2500, 1/2/2010
John, 25, $3000, 2/3/2010

It would return the bottom 2 rows only

Answer 1

You didn't state your DBMS, so this is ANSI SQL

select *
from (
  select name, 
         age,
         PaymentAmount,
         Date, 
         row_number() over (partition by name order by date desc) as rn
  from the_table
  where age between 22 and 25
) t
where rn = 1;

Another option is to use a co-related subquery:

select name,age,paymentamount,date 
from the_table t1
where age between 22 and 25 
and date = (select max(date) 
            from the_table t2
             where t2.name = t1.name
               and t2.age between 22 and 25)
order by name;

Usually the solution with a window function is faster than the co-related subquery as only a single access to the table is needed.

SQLFiddle: http://sqlfiddle.com/#!15/17e37/4

Btw: having a column named age is a bit suspicious because you need to update that every year. You should rather store the date of birth and then calculate the age when retrieving the data.

Answer 2

This query would give you all records of most recent payment of age 20 and 25. Limit it by using TOP 2 or LIMIT 2 or rownum <=2 as per your DB syntax

SELECT NAME,AGE,PAYMENTAMOUNT,DATE FROM MY_TABLE
WHERE AGE BETWEEN 20 AND 25 
AND DATE IN 
    (
        SELECT MAX(DATE) 
    FROM MY_TABLE 
    WHERE 
    AGE BETWEEN 20 AND 25
     );

---

EDIT as per horse_with_no_name:

SELECT NAME,AGE,PAYMENTAMOUNT,DATE 
FROM the_table
WHERE AGE BETWEEN 20 AND 25 
AND DATE IN 
    (
        SELECT (DATE) 
    FROM the_table
    WHERE 
    AGE BETWEEN 20 AND 25 order by date desc limit 2
     )
limit 2;

Fiddle reference : http://sqlfiddle.com/#!15/17e37/10

Answer 3

Simplest of all,Try this following query
select name,age,paymentamount,date from yourtablename where date in (select max(date) from yourtablename where age between 20 and 25 and group by name);

Answer 4

You should Create a Table with Identity Column to make your Life easier

• ColumnPrimaryKey IDENTITY (1,1)
• Name
• Age
• PaymentAmount
• Date

SELECT TOP 2 * FROM [TableName] Where Age BETWEEN 20 AND 25 ORDER BY [PrimaryKey] DESC

The above query will return the top two row Inserted in table

Answer 5

You can use between like

select * from meta where title='$title' and (date between '$start_date' and '$end_date').

Answer 6

Okay, I know you said SQL-- here's for people with two layers.

VIA SQL:

Order your SQL results by date descending (should be newest to oldest...).

VIA YOUR "BACK END":

Create an empty final set.

As you are iterating through your results, if your result row person is not in your final set, add the data to the final set.

Boom, your final set has the latest of each person.