in reply to Re: Help with removing dupes from a string with perl
in thread Help with removing dupes from a string with perl

There are several ways to do it, depending on your database. The most portable is: 
SELECT t.jobname
  , t.status
  , t.timestamp
  , t.comment
FROM your_table t
  JOIN (
      SELECT jobname, MAX(timestamp) as timestamp
      FROM your_table
      GROUP BY jobname
    ) max_timestamp
    ON max_timestamp.jobname = t.jobname
      AND max_timestamp.timestamp = t.timestamp

[download]
Of course this assumes that you don't have 2 entries with the same name and the same timestamp. It is usually less efficient than selecting back and filtering outside of the database.

Google tells me that Sybase doesn't yet support the better SQL 2003 solution of analytic queries.

Also a note about your solution. I would strongly recommend ordering on jobname and then timestamp. This will put all of the records for each jobname together, making it easy to figure out which is first and which you should throw. Otherwise you need to keep a potentially large hash of which jobs you've seen. (You skipped that step in your code.)

Replies are listed 'Best First'.
Re^3: Help with removing dupes from a string with perl (last)
by tye (Sage) on Jul 09, 2009 at 18:48 UTC

    Heh, I just rolled my own first() and last() aggregators in Postgresql yesterday because I wanted something like this (googling for analytic queries found a somewhat related link that mentioned first() and last() aggregators as I was curious if my solution was in-line with SQL 2003's solution).

    The process was a bit more interesting and difficult than I expected. It convinced me that Postgresql doesn't have a general solution for user-defined polymorphic aggregators because it doesn't allow polymorphic user-defined data types (you can't use "anyelement" as part of a "create type" construct -- at least in the versions I was dealing with, 8.2 and 8.3).

    So I was able to create some polymorphic aggregators but can see cases where these approaches would not work (and the first route I tried failed).

    Read more... (1479 Bytes)

    The following test query: 

    select first(v), first_nonnull(v), last(v), last_nonnull(v)
from ( select NULL::text as v union all
    select 'one' as v union all select 'two' as v ) as q
union all
select first(v), first_nonnull(v), last(v), last_nonnull(v)
from ( select 'one'::text as v union all
    select 'two' as v union all select NULL as v ) as q;

    [download]

    produces the following results: 

     first | first_nonnull | last | last_nonnull
-------+---------------+------+--------------
       | one           | two  | two
 one   | one           |      | two
(2 rows)

    [download]

    - tye        

[reply]
[d/l]
[select]
      Sorry, no. Your solution is nothing like SQL 2003's analytic functions which come from Oracle. If you want a brief introduction to them, read http://www.orafusion.com/art_anlytc.htm. The syntax is..heinous. To use them effectively you often want to query a subquery. But they are very powerful and much more efficient than older solutions.
[reply]

      Actually, despite tilly's response, these are exactly like some of what Postgresql calls "window functions" which are exactly the same thing that Oracle calls "analytic functions" (Oracle uses "window functions" to refer to a subset of the "analytic functions").

      One difference between my user-defined aggregate functions above and the standard window functions first_value() and last_value(), is that my aggregates can be used as simple aggregates (such as with just "GROUP BY") while first_value() and last_value() must have a window defined (via "OVER"). That is, my aggregates can be used with OVER or without OVER.

      But the main reason I am responding is to note that my third attempt at defining first() and last() was much simpler. It is just like the _nonnull versions except you drop the word 'strict'. 

      create function first( one anyelement, two anyelement )
    returns anyelement
as $$ begin return one; end $$ language plpgsql;

create function last( one anyelement, two anyelement )
    returns anyelement
as $$ begin return two; end $$ language plpgsql;

create aggregate first( anyelement )(
    stype = anyelement
  , sfunc = first
);

create aggregate last( anyelement )(
    stype = anyelement
  , sfunc = last
);

      [download]

      Which makes me wonder what my first try (that didn't work) looked like.

      I'm now working with newer Postgresql that supports window functions. But this version's first_value() doesn't support the standard "IGNORE NULLS" option which looked like the best solution for a problem I was working on today. So I asked google where my implementations were so I could just use them.

      Swapping in these details again made me suspect that the first() and last() implementations could be as simple as the first_nonnull() and last_nonnull() implementations. Testing on this newer version of Postgresql showed that they could be.

      - tye        

[reply]
[d/l]
Re^3: Help with removing dupes from a string with perl
by mpeppler (Vicar) on Jul 24, 2009 at 18:17 UTC
    In Sybase you can use a a group by and a having clause, even without using any aggregates in the main query.

    So something like 

    select jobname, status, timestamp, comment
from the_table
group by jobname
having timestamp = max(timestamp)

    [download]
    should return something reasonable without the need for a sub-query.

    Michael

[reply]
[d/l]
      For the record, I hate that "feature". What it means is that if you accidentally leave a field that is in the SELECT out of the GROUP BY, you'll introduce a join. Depending on the query, this join can be very large and very poorly behaved. Particularly if you've left joined to a large table.

      When I worked with Sybase I think I made this mistake every few months (with the left out field from a very large table), and inevitably didn't notice until my query had been grinding away for half an hour and the database was feeling the pressure. The database it usually happened on was used for production purposes, but luckily the consequences were only internally visible, and very few people were affected. Still it was my second greatest annoyance with Sybase. (They've since fixed my greatest annoyance at the time, which was page level locking.)

[reply]
        I agree with you for the general case (and I use a development tool that will flag such omissions as a defect).

        But occasionally it is really useful - in particular for me as I write way more SQL (in stored procs, etc) than perl or any other client-side language these days.

        Michael

[reply]

In Section Seekers of Perl Wisdom