sql

[Member 310024]

*
I have created a temp table (t1)
which contains 3 fields ( f1, f2 & f3 ).
*
f1 is may not be unique.
*
I would like to create 3 more temp tables (t2, t3 & t4).
*
t2 is to contain t1 recs that have a f1 count(*) of 1.
*
t3 is to contain one rec for each unique t1.f1 value,
together with it's f1 count(*) value.
*
t4 is to contain one rec for each unique t1.f1 value,
and the highest t1.f2 & t1.f3 value for that t1.f1 value.
(f2 & f3 are like a date & time - so, the highest date & time)
*
Does anyone have a reference to a truely brilliant SQL cookbook
that is suitable for my tiny brain to understand how to do this?
*
I've tried things like select distinct, group by, count(*) and
I'm sure the solution is to somehow combine these, but I've
never really "GOT" the syntax of more that basic SQL, and the
logic of complex SQL statements still evades me.
*

 

[fredericofonseca]

look into the HAVING clause of the SELECT statement.

The online manuals available at IBM do have lots of samples.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[MarcLodge]

The truly brilliant SQL cookbook to which you refer can be found:

http://mysite.verizon.net/Graeme_Birchall/cookbook/DB2V95CK.PDF

 

[blom0344]

t2 is to contain t1 recs that have a f1 count(*) of 1.

SELECT TEMP.F1 FROM
(SELECT f1 AS F1,COUNT(f1) FROM T1
GROUP BY F1
HAVING COUNT(f1) = 1) TEMP

t3 is to contain one rec for each unique t1.f1 value,
together with it's f1 count(*) value.

SELECT f1 AS F1,COUNT(f1) FROM T1
GROUP BY F1

t4 is to contain one rec for each unique t1.f1 value,
and the highest t1.f2 & t1.f3 value for that t1.f1 value.

SELECT T1.F1,MAX(T1.F2),MAX(T1.F3)
FROM
T1
GROUP BY T1

Obviously you need to extendthe code with the proper ' insert into ....' syntax




Ties Blom

 

[Member 310024]

In solution #3 ( i.e. for T4 - the last one )
is it GROUP BY T1 ( or GROUP BY F1 ) ?

Also in solution #3, will it get the max ( F2 concat F3 )
or get max F2, independently of max F3?
I want the max ( F2 concat F3 ) if that's possible.

 

[blom0344]

Sorry , should be GROUP BY F1

Max function takes the highest value for a given set (highest F2 for a set F1)

A set can be one or any number of fields

F2 concat F3 is an operation on string values.

The best approach is to make a limited test table and play around with SQL statements. You will learn very quickly

Ties Blom