Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Aristotle (5147)

Aristotle
  pagaltzis@gmx.de
http://plasmasturm.org/

Blah blah blah blah blah [technorati.com]

Journal of Aristotle (5147)

Wednesday May 09, 2007
05:46 PM

The Guttman-Rosler transform… in SQL

[ #33236 ]

There was recently a grouping question on the SQLite mailing list.

Eliedaat Adler :

I have a complex query result set RESULT_TABLE that returns:

G        V        P
___________________
A        X        1
A        X        2
B        Y        4
B        Z        2
B        X        8
C        Y        6
C        Z        8
C        X        9
C        Y       11

G – defines groups
V – some value for that specific row
C – defined a display priority – i.e.

I need to define a query that returns only the “first” row in each group – i.e. the row with the lowest display priority:

G        V        P
___________________
A        X        1
B        Z        2
C        Y        6

most preferably a query that doesn’t require selecting RESULT_TABLE more than once.

One response contained a familiar-looking pattern…

Ed Pasma :

This solution may is tricky but has occasionally helped me. It is written here dedicated for the example data. For real data the leftpadding should likely be increased to the content of the sorting key. Also the result may need to be converted to the expected data type, it has now become text.

  SELECT g,
         SUBSTR (MAX (SUBSTR ('  ' || p, -2, 2) || v), 3, 1) v
    FROM t
GROUP BY g;

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.