MYSQL to display only the earliest of duplicates

Question

select name, reported_at from nodes
where reported_at < curdate() or reported_at is null
group by name

Output:

name                reported at
ncs-linux-test.edu  2012-03-16 18:36:03
ocdev1.net          2012-04-06 16:32:02
pinc-ctm.net        NULL

With that statement, I get any results form reported at/name that are less than the current date.

What I need though is the statement to only pull out data that has a duplicate(s) with more current information.

For example:

The statement would only pull out:

ncs-linux-test.edu    2012-03-16

if there was an

ncs-linux-test.edu

with a date more current than 2012-03-16.

That's essentially http://stackoverflow.com/questions/854128/find-duplicate-records-in-mysql, and then you pick the earliest per group. — nes1983, Apr 17 '12 at 18:57
What are you expecting to get if you have duplicates both with null values? — Mosty Mostacho, Apr 17 '12 at 19:06

score 2 · Accepted Answer · edited Feb 16 '13 at 15:45

2

select *
from nodes n 
join nodes nlater
  on n.name = nlater.name 
  and n.reportedat < nlater.reportedat

edited Feb 16 '13 at 15:45

wickedone

answered Apr 17 '12 at 21:11

Gordon Linoff

1 Answers1