MYSQL to display only the earliest of duplicates [duplicate]

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 stackoverflow.com/questions/854128/…, and then you pick the earliest per group. — nes1983
– nes1983, Commented Apr 17, 2012 at 18:57
What are you expecting to get if you have duplicates both with null values? — Mosty Mostacho
– Mosty Mostacho, Commented Apr 17, 2012 at 19:06

wickedone · Accepted Answer · 2013-02-16 15:45:09Z

2

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

5401 gold badge6 silver badges18 bronze badges

answered Apr 17, 2012 at 21:11

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1