Re^2: Help with removing dupes from a string with perl

The SQL for looking up a unique column will roughly look like this(depending upon your database),

SELECT DISTINCT(JOBNAME), STATUS, TIMESTAMP, COMMENT FROM YOUR_TABLES 
+WHERE STATUS = ? ORDER BY TIMESTAMP DESC
[download]

Now you remove that loop for looking up individual job names.

Comment on Re^2: Help with removing dupes from a string with perl Download Code

Replies are listed 'Best First'.
Re^3: Help with removing dupes from a string with perl by jrsimmon (Hermit) on Jul 09, 2009 at 14:06 UTC
With DB2, using DISTINCT in that manner won't guarantee that you get the most recent result. DISTINCT will be evaluated before the ORDER BY clause and may not find the most recent record first. I'm not sure whether this behavior is standard among databases or not. From the db2 doc: When values match in the specified columns of two or more rows, the distinct operator retains only one of the duplicate rows in the result data set and discards the others. When duplicate rows are found, only the first row that is read during processing is retained. Because the order of rows is not guaranteed during processing, choose carefully when you select columns for the distinct operation. To ensure you get the most recent occurrence, you'd probably need to use a temp table in the statement (at least that's how I've seen it done).	[reply]

Replies are listed 'Best First'.

Re^3: Help with removing dupes from a string with perl
by jrsimmon (Hermit) on Jul 09, 2009 at 14:06 UTC

When values match in the specified columns of two or more rows, the distinct operator retains only one of the duplicate rows in the result data set and discards the others. When duplicate rows are found, only the first row that is read during processing is retained. Because the order of rows is not guaranteed during processing, choose carefully when you select columns for the distinct operation.

[reply]