471,873 Members | 2,027 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,873 software developers and data experts.

Extract Rows with highest values in the columns, MAX doesn't work

From the table i want everything highlighted with a *

I wanted an SQl expression to look at values in Column 1 (ID), look at
the corresponding values in the second column (F1) and select the row
with the highest value, and then if there was more than one row for
that ID with the same value in F1, look at column 3 (F2) and select
the row with the highest value in this column.

SQL> select * from test;

ID F1 F2
---------- ---------- ----------
1 12 4
* 1 12 6
1 11 1
1 9 12
2 3 5
* 2 9 13
2 9 9
3 1 2
3 1 1
* 3 7 5

I try the following

SQL> select * from test t1
2 where f1 = (select max(f1) from test t2 where t2.id = t1.id)
3 ;

And get the following

ID F1 F2
---------- ---------- ----------
1 12 4
* 1 12 6
* 2 9 13
2 9 9
* 3 7 5

If I add another line with an AND statement after line 2 I either get
nothing or rubbish as the output.

Ideas?

Cheers
Jul 20 '05 #1
2 21082
If I understand correctly, this should get u going in the right direction:

select a.*
from test a
inner join (
select id
, f1
, max(f2) maxf2
from test
group by id
, f1
) b on b.id = a.id
and b.f1 = a.f1
and b.maxf2 = a.f2

Harry Helmich

sukh wrote:
From the table i want everything highlighted with a *

I wanted an SQl expression to look at values in Column 1 (ID), look at
the corresponding values in the second column (F1) and select the row
with the highest value, and then if there was more than one row for
that ID with the same value in F1, look at column 3 (F2) and select
the row with the highest value in this column.

SQL> select * from test;

ID F1 F2
---------- ---------- ----------
1 12 4
* 1 12 6
1 11 1
1 9 12
2 3 5
* 2 9 13
2 9 9
3 1 2
3 1 1
* 3 7 5

I try the following

SQL> select * from test t1
2 where f1 = (select max(f1) from test t2 where t2.id = t1.id)
3 ;

And get the following

ID F1 F2
---------- ---------- ----------
1 12 4
* 1 12 6
* 2 9 13
2 9 9
* 3 7 5

If I add another line with an AND statement after line 2 I either get
nothing or rubbish as the output.

Ideas?

Cheers

Jul 20 '05 #2
"sukh" <su**@jatt.com> wrote in message news:e7************************@posting.google.com ...
From the table i want everything highlighted with a *

I wanted an SQl expression to look at values in Column 1 (ID), look at
the corresponding values in the second column (F1) and select the row
with the highest value, and then if there was more than one row for
that ID with the same value in F1, look at column 3 (F2) and select
the row with the highest value in this column.

SQL> select * from test;

ID F1 F2
---------- ---------- ----------
1 12 4
* 1 12 6
1 11 1
1 9 12
2 3 5
* 2 9 13
2 9 9
3 1 2
3 1 1
* 3 7 5

I try the following

SQL> select * from test t1
2 where f1 = (select max(f1) from test t2 where t2.id = t1.id)
3 ;

And get the following

ID F1 F2
---------- ---------- ----------
1 12 4
* 1 12 6
* 2 9 13
2 9 9
* 3 7 5

If I add another line with an AND statement after line 2 I either get
nothing or rubbish as the output.

Ideas?

Cheers


CREATE TABLE Test
(
id INT NOT NULL,
f1 INT NOT NULL,
f2 INT NOT NULL,
PRIMARY KEY (id, f1, f2)
)

INSERT INTO Test (id, f1, f2)
VALUES (1, 12, 4)
INSERT INTO Test (id, f1, f2)
VALUES (1, 12, 6)
INSERT INTO Test (id, f1, f2)
VALUES (1, 11, 1)
INSERT INTO Test (id, f1, f2)
VALUES (1, 9, 12)
INSERT INTO Test (id, f1, f2)
VALUES (2, 3, 5)
INSERT INTO Test (id, f1, f2)
VALUES (2, 9, 13)
INSERT INTO Test (id, f1, f2)
VALUES (2, 9, 9)
INSERT INTO Test (id, f1, f2)
VALUES (3, 1, 2)
INSERT INTO Test (id, f1, f2)
VALUES (3, 1, 1)
INSERT INTO Test (id, f1, f2)
VALUES (3, 7, 5)

SELECT T1.*
FROM Test AS T1
LEFT OUTER JOIN
Test AS T2
ON T1.id = T2.id AND
(T2.f1 > T1.f1 OR (T2.f1 = T1.f1 AND T2.f2 > T1.f2))
WHERE T2.id IS NULL
ORDER BY T1.id

id f1 f2
1 12 6
2 9 13
3 7 5

Regards,
jag
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

12 posts views Thread by Rick DeBay | last post: by
3 posts views Thread by Jim Heavey | last post: by
68 posts views Thread by Martin Joergensen | last post: by
7 posts views Thread by figital | last post: by
reply views Thread by YellowAndGreen | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.