CODEWITHSUNDEEP
sql
user3550682
user3550682

Reputation: 51

SQL query to select most recent of duplicates

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

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

Upvotes: 5

Views: 18370

Answers (6)

BradChesney79
BradChesney79

Reputation: 668

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.

Upvotes: 0

R.K.Bhardwaj
R.K.Bhardwaj

Reputation: 2192

You can use between like

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

Upvotes: 0

nobalG
nobalG

Reputation: 4620

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);

Upvotes: 1

ngrashia
ngrashia

Reputation: 9884

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

Upvotes: 1

user330315
user330315

Reputation:

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.

Upvotes: 5

Shekhar Pankaj
Shekhar Pankaj

Reputation: 9125

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

Upvotes: 0

Related Questions