CODEWITHSUNDEEP
sqlinformix
FrankBlack78
FrankBlack78

Reputation: 162

Reducing the list of results (SQL)

I stuck on an SQL statement since 2 days now and I hope you can help me with that.

The result of my select is a list with 4 attributes A, B, C and D (below is an example list of 5 datasets):

 1. A=1 B=100 C=200 D=300
 2. A=2 B=200 C=100 D=300
 3. A=3 B=300 C=200 D=100
 4. A=3 B=100 C=100 D=200
 5. A=3 B=300 C=100 D=200

The list shall be reduced, so that every attribute A is in the list only once.

In the example above the dataset 1. and 2. should be in the list, because A=1 and A=2 exists only once.

For A=3 I have to build a query to identify the dataset, that will be in the final list. Some rules should apply:

  1. Take the dataset with the highest value of B; if not distinct then
  2. Take the dataset with the highest value of C; if not distinct then
  3. Take the dataset with the highest value of D.

In the example above the dataset 3. should be taken.

The expected result is:

1.A=1 B=100 C=200 D=300
2.A=2 B=200 C=100 D=300
3.A=3 B=300 C=200 D=100

I hope you understand my problem. I've tried various versions of SELECT-statements with HAVING and EXISTS (or NOT EXISTS), but my SQL knowledge isn't enough.

Upvotes: 1

Views: 121

Answers (4)

Obaidul
Obaidul

Reputation: 57

SELECT A, MAX(B) AS B, MAX(C) AS C, MAX(D) AS D
  FROM table_name
 GROUP BY A

Upvotes: -1

Maciej Piaseczny
Maciej Piaseczny

Reputation: 59

Probably there is an easier way to solve this problem, but this one works:

CREATE TEMP TABLE TEST (
A INTEGER,
B INTEGER,
C INTEGER,
D INTEGER
);

insert into TEST values (1,1,1,1);
insert into TEST values (2,1,5,1);
insert into TEST values (2,2,1,1);
insert into TEST values (3,1,4,1);
insert into TEST values (3,2,1,4);
insert into TEST values (3,2,3,1);
insert into TEST values (3,3,1,5);
insert into TEST values (3,3,2,3);
insert into TEST values (3,3,2,7);
insert into TEST values (3,3,3,1);
insert into TEST values (3,3,3,2);

select distinct
t1.A,
t2.B as B,
t3.C as C,
t4.D as D
from TEST t1 
join (select A ,MAX (B) as B from TEST group by A)t2 on t2.A=t1.A
join (select A, B, MAX(C) as C from TEST group by A,B)t3 on t3.A=t2.A and t3.B=t2.B
join (select A, B, C, MAX (D) as D  from TEST group by A,B,C)t4 on t4.A=t3.A and t4.B=t3.B and t4.C=t3.C;

Result: 

a   b   c   d
1   1   1   1
2   2   1   1
3   3   3   2

Tested on IBM Informix Dynamic Server Version 11.10.FC3.

Upvotes: 1

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

I have no idea about Informix but you can try. This works in Sql Server. May be it will also work in Informix:

select * from tablename t1
where id = (select first 1 id from tablename t2 
            where t2.A = t1.A order by B desc, C desc, D desc)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270633

This type of prioritization query is most easily done with row_number(), but I don't think Informix supports that.

So, one method is to enumerate the rows using a correlated subquery:

select t.*
from (select t.*,
             (select count(*)
              from t t2
              where (t2.b > t.b) or
                    (t2.b = t.b and t2.c > t.c) or
                    (t2.b = t.b and t2.c = t.c and t2.d > t.d)
             ) as NumGreater
      from t
     ) t
where NumGreater = 0;

Upvotes: 1

Related Questions