Using result from DISTINCT SELECT

[SomeSwede]

I want to get some data, from 2 fields, but I don't want to select distinctly by them

In my tables I have

Table 1:
A B TIME

Table 2:
A B C D E TIME


my select is

select distinct a, max(2.TIME) from 1 , 2, where 1.a = 2.a and 1.TIME > "2008" GROUP by a

The problem I have is I want to get information about 2.b and 2.c from the fields in this select? is there anyway to do it??? thank you.

 

[blom0344]

A distinct in an aggregate is meaningless:

select distinct a, max(2.TIME) from 1 , 2, where 1.a = 2.a and 1.TIME > "2008" GROUP by a

can be :

select a, max(2.TIME) from 1 , 2, where 1.a = 2.a and 1.TIME > "2008" GROUP by a

It is very unclear what you want, but if you want a listing of individual values without using a distinct then:

select 'b-values',2.b,count(2.b) from 2 
where 2.a in 
(select 1.a from 1 where
1.TIME > "2008") group by 2.b 
union all
select 'c-values',2.c,count(2.c) from 2 
where 2.a in 
(select 1.a from 1 where
1.TIME > "2008") group by 2.c

you may need tweak the time clause to get the proper syntax (depending on the exact type of time)



Ties Blom

 

[SomeSwede]

Was it that unclear? I found how to do it anyway:

Select a, b, c from 2, ( select a, max(2.TIME) AS MAX from 1 , 2, where 1.a = 2.a and 1.TIME > "2008" GROUP by a) AS X where X.a = a and X.max = time;

 

[SomeSwede]

And there is a reason to have Distinct, There is just a bug in DB2 Imagine there are two values like 5 , 5 , both of those will be choosen, but for a DISTINCT select, it should only take out one of those lines, unfortunately DB2 cannot handle that and will give a DISTINCT select with two equal lines. UGLY!!