Re: Need SQL help, I'm stuck.

I just had to do this, and came up with two different ways; maybe someone here
will come up with an even better one.

My first brute force attempt was:
SELECT C1, C3, C4
FROM T1
WHERE C2 = ( SELECT max(T2.C2) FROM T1 as T2 WHERE T1.C1=T2.C1);

That works, but is very slow because the subselect has to be reprocessed on
every row. I tried optimizing by replacing the subselect with an indexed temp
table:
CREATE TEMP TABLE temp_MaxC2 AS SELECT C1, max(C2) AS maxC2 FROM T1 GROUP BY C1;
CREATE INDEX tmp_idx_C1 ON temp_MaxC2 (maxC2);
SELECT C1, C3, C4 FROM T1, temp_MaxC2 TMP WHERE T1.C1= TMP.C1 and
T1.C2=TMP.maxC2;

By my benchmarks, that ran roughly four orders of magnitude faster. Temp tables
go away automatically at the end of a connection, but I'm running this under
mod_perl with Apache::DBI, which pools the connections, so there's a bit more
code to drop the table and indexes before creating them, and ignoring any errors
if they don't exist in the first place. (Wish there was an "IF EXISTS ... CREATE
..." syntax)

If anyone has a still better approach, I'd love to hear what it is. Thanks,

--Wes Sheldahl

Chris Albertson <chrisalbertson90278%yahoo(dot)com(at)interlock(dot)lexmark(dot)com> on
12/10/2001 04:42:54 PM

To: pgsql-general%postgresql(dot)org(at)interlock(dot)lexmark(dot)com
cc: (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject: [GENERAL] Need SQL help, I'm stuck.

Help. I seem to have a case of "brain lock" and can't figure out
something that I should know is simple.

Here is what I am trying to do. Let's say I have a table called
T1 with columns C1, C2, C3, C4. It contains data as follows

a 1 abcd dfg
a 2 cvfr erg
a 3 derg hbg
b 1 cccc rth
c 1 rdvg egt
c 2 derf ett

I want a SQL query that returns these rows

a 3 derg hbg
b 1 cccc rth
c 2 derf ett

All I can think of is

SELECT C1, max(C2), C3, C4 FROM T1 GROUP BY C1;

That does not work. What I really want is the values for C1, C3
and C4 that are associated with the row containing the maximum
value of C2 for each group of like C1 values. I don't even need
to know what is max(C2).

Can I join the table with itself somehow? See: "brain lock".
This should not be hard.

Thanks,

=====
Chris Albertson
Home: 310-376-1029 chrisalbertson90278(at)yahoo(dot)com
Cell: 310-990-7550
Office: 310-336-5189 Christopher(dot)J(dot)Albertson(at)aero(dot)org

__________________________________________________
Do You Yahoo!?
Send your FREE holiday greetings online!
http://greetings.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org