Comparing rows keeping latest

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hello experts, Im a Perl newbie and Im looking for a way to compare rows returned in a sql statement and keep the row with the most current date. Below is a simplified version of the rows and also a copy of the code I wrote but it doesnt work.

ID               Date
13               10102005
13               09152005
13               11052005
200              11012005
200              11152004

Here's the code I wrote:
foreach $data1(@data_241) {
    @compare1 = split($data1);
    foreach $data2(@data_241) {
        @compare2 = split($data2);
        if ($compare1[0] == $compare2[1]) {
            if ($compare1[1] > $compare2[2]) {
                print NEW "@compare1";
            }
        }
    }
}
[download]

So in the example, I need to keep only ID 13 with a date of 11052005 and ID 200 with the date 11012005. Thanks in advance....

Comment on Comparing rows keeping latest Download Code

Replies are listed 'Best First'.
Re: Comparing rows keeping latest by Grygonos (Chaplain) on Feb 20, 2006 at 20:44 UTC
Assuming you're the one writing the query, a simple `SELECT MAX(Date) As MaxDate,ID FROM tblFoobar GROUP BY ID` will get you exactly what you want. Leaving this problem to Perl is not the best approach IMHO Grygonos	[reply] [d/l]
Re^2: Comparing rows keeping latest by Anonymous Monk on Feb 20, 2006 at 20:53 UTC
Thanks, I think that should work... I wil have to go look up the syntax. Thanks again all for all your help...	[reply]
Re^3: Comparing rows keeping latest by Grygonos (Chaplain) on Feb 21, 2006 at 18:02 UTC
The syntax of the query I provided, should be ANSI compliant. I don't know how much you would need to change. Grygonos	[reply]
Re: Comparing rows keeping latest by GrandFather (Saint) on Feb 20, 2006 at 20:23 UTC
One trick is to normalize the date so you can compare it directly: `use warnings; use strict; my %bestLine; while (<DATA>) { my ($line, $month, $day, $year) = /(\d+)\s+(\d{2})(\d{2})(\d{4})/; my $date = "$year$month$day"; $bestLine{$line} = $date if ! exists $bestLine{$line} or $bestLine{$ +line} < $date; } print join "\n", map {"$_: $bestLine{$_}"} sort keys %bestLine; __DATA__ 13 10102005 13 09152005 13 11052005 200 11012005 200 11152004` [download] Prints: `13: 20051105 200: 20051101` [download] Update: revise code to match OP's problem. DWIM is Perl's answer to Gödel	[reply] [d/l] [select]
Re^2: Comparing rows keeping latest by Anonymous Monk on Feb 20, 2006 at 20:27 UTC
I need the latest date for each ID, so in this example I need the latest for ID 13 and one for ID 200... Thanks for your response...	[reply]
Re: Comparing rows keeping latest by neilwatson (Priest) on Feb 20, 2006 at 20:02 UTC
It might be more efficient to have your SQL statement perform this logic. Should be simple enough. Otherwise, you'll want to use a module like Date::Calc or Date::Manip to compare the dates. Neil Watson watson-wilson.ca	[reply]
Re^2: Comparing rows keeping latest by Anonymous Monk on Feb 20, 2006 at 20:11 UTC
Well I could order by ID then date but would still need to program sql to retrieved the first or last row for each ID. Wouldnt I need to write some PL-SQL to do that? I have less PL-SQL knowledge than I have of Perl, which is sad I know... :(	[reply]
Re^3: Comparing rows keeping latest by neilwatson (Priest) on Feb 20, 2006 at 20:37 UTC
You original post states that you want to keep the row with the most recent date. Something like the SQL MAX statement might do the trick. What SQL server are you using? Neil Watson watson-wilson.ca	[reply]
Re: Comparing rows keeping latest by CountOrlok (Friar) on Feb 20, 2006 at 20:34 UTC
Assuming each line of your data is in @data_241 and NEW is the file handle you want to write to: `my %latest; foreach my $line (@data_241) { # split line my ($id, $date) = split(/\s+/, $line); if (! exists($latest{$id}) { # place in hash if not seen before $latest{$id} = $date; } else { # place in hash if date is greater than the one in hash $latest{$id} = $date if substr($date,4,4).substr($date,0,4) ge substr($latest{$id},4,4).substr($latest{$id},0,4); } } foreach my $key (sort keys %latest) { # print out the latest values print NEW "$key\t$latest{$key}\n"; }` [download] -imran	[reply] [d/l]