CODEWITHSUNDEEP
sqlms-access-2010
frankiehf
frankiehf

Reputation: 180

select most recent record if theres a duplicate

So i have been scratching my head over this one,mostly because i am on access 2010 and most of the queries i have found on the internet have commands that do not work on access. 

id    name    date        qty       created
====================================================
1     abc     01/2016     20        06/07/2016 11:00
2     abc     02/2016     20        06/07/2016 11:00
3     abc     03/2016     20        06/07/2016 11:00
4     abc     01/2016     30        06/07/2016 13:00

I need to pull out a recordset like this:

id    name    date        qty       created
====================================================
2     abc     02/2016     20        06/07/2016 11:00
3     abc     03/2016     20        06/07/2016 11:00
4     abc     01/2016     30        06/07/2016 13:00

the created field is just a timestamp, the date field is a "due date". basically i need to pull out the most recent qty for each name and date. the ID is unique so i can use it instead,if its easier. By far i've got: 

SELECT m1.date, m1.name, m1.created
FROM table AS m1 LEFT JOIN table AS m2 ON (m1.created < m2.created) AND 
(m1.date = m2.date)
WHERE m2.created IS NULL;

but this one gives me only the most recent conflicted data, ie. the record n°4 in my example.i also need the other two records. any thoughts?

Upvotes: 3

Views: 4390

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72225

Try this:

SELECT m1.date, m1.name, m1.qty, m1.created
FROM table AS m1
JOIN (
   SELECT date, name, MAX(created) AS created
   FROM table 
   GROUP BY date, name
) AS m2 ON m1.date = m2.date AND m1.name = m2.name AND m1.created = m2.created

Upvotes: 0

sagi
sagi

Reputation: 40491

Try using NOT EXISTS() :

SELECT * FROM YourTable t
WHERE NOT EXISTS(SELECT 1 FROM YourTable s
                 WHERE t.date = s.date and s.created > t.created
                   AND t.name = s.name)

I think you are also missing a condition so I've added it:

and t.name = s.name

You didn't tag your RDBMS, if its SQL-Server/Oracle/Postgresql you can use ROW_NUMBER() :

SELECT s.date, s.name, s.created FROM (
    SELECT t.*,
           ROW_NUMBER() OVER(PARTITION BY t.date,t.name ORDER BY t.created DESC) as rnk
    FROM YourTable t) s
WHERE s.rnk = 1

Upvotes: 4

Related Questions