Db2 Min Value with Char field.

[jaip26]

All,

I came across a strange situation in my query. Basically here is the
result of my query.

col1 col2 col3 col4 col5 col6 col7
--------------------------------------------------------------------------------------------------
Jay 4.5 NULL 4.5 NULL 3.9 'AD'
Jay 3.9 'AD' 5.9 'D' 4.5 NULL

I need a result set looks like below..
------------------------------------------------------------------------------------------------------
Jay 3.9 'AD' 4.5 NULL 3.9 'AD'

col2,col4,col6 should be the minimum and col3,col5,col7 should be the
value of MIN(col2),MIN(c ol4),MIN(col6) respectively.
Any Ideas ?

Thanks

[Serge Rielau]

ja****@gmail.co m wrote:

All,

I came across a strange situation in my query. Basically here is the
result of my query.

col1 col2 col3 col4 col5 col6 col7
--------------------------------------------------------------------------------------------------
Jay 4.5 NULL 4.5 NULL 3.9 'AD'
Jay 3.9 'AD' 5.9 'D' 4.5 NULL

I need a result set looks like below..
------------------------------------------------------------------------------------------------------
Jay 3.9 'AD' 4.5 NULL 3.9 'AD'

col2,col4,col6 should be the minimum and col3,col5,col7 should be the
value of MIN(col2),MIN(c ol4),MIN(col6) respectively.

You need to do thsi in two stages.
use
ROW_NUMBER() OVER(ORDER BY c1) AS rn2,
ROW_NUMBER() OVER(ORDER BY c4) AS rn4,
....
In a nested query.
Then collapse and use the rows into groups
uisng MIN(c2),MIN(c4) , ... MIN(CASE WHEN rn2 = 1 THEN c3 END), ....

This won't be the fastest query in town...
You'll get as many sorts as you have order bys.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[jaip26]

Thanks Serge..It worked..Here's how i implemented..

WITH t
(col1,col2,col3 ,col4,col5,col6 ,col7,r1,r2,r3, r4,r5,r6,r7) AS (
SELECT col1,col2,col3, col4,col5,col6, col7,
ROW_NUMBER() OVER(ORDER BY col1) AS r1,
ROW_NUMBER() OVER(ORDER BY col2) AS r2,
ROW_NUMBER() OVER(ORDER BY col3) AS r3,
ROW_NUMBER() OVER(ORDER BY col4) AS r4,
ROW_NUMBER() OVER(ORDER BY col5) AS r5,
ROW_NUMBER() OVER(ORDER BY col6) AS r6,
ROW_NUMBER() OVER(ORDER BY col7) AS r7
FROM (VALUES
('Jay',4.5,NULL ,4.5,NULL,3.9,' AD')
,('Jay',3.9,'AD ',5.9,'D',4.5,N ULL)
) AS X(col1,col2,col 3,col4,col5,col 6,col7)
)

SELECT col1,
MIN(col2),MIN(C ASE WHEN r2 = 1 THEN col3 END),
MIN(col4),MIN(C ASE WHEN r4 = 1 THEN col5 END),
MIN(col6),MIN(C ASE WHEN r6 = 1 THEN col5 END)
FROM t
Group By col1
Serge Rielau wrote:

ja****@gmail.co m wrote:

All,

I came across a strange situation in my query. Basically here is the
result of my query.

col1 col2 col3 col4 col5 col6 col7
--------------------------------------------------------------------------------------------------
Jay 4.5 NULL 4.5 NULL 3.9 'AD'
Jay 3.9 'AD' 5.9 'D' 4.5 NULL

I need a result set looks like below..
------------------------------------------------------------------------------------------------------
Jay 3.9 'AD' 4.5 NULL 3.9 'AD'

col2,col4,col6 should be the minimum and col3,col5,col7 should be the
value of MIN(col2),MIN(c ol4),MIN(col6) respectively.

You need to do thsi in two stages.
use
ROW_NUMBER() OVER(ORDER BY c1) AS rn2,
ROW_NUMBER() OVER(ORDER BY c4) AS rn4,
...
In a nested query.
Then collapse and use the rows into groups
uisng MIN(c2),MIN(c4) , ... MIN(CASE WHEN rn2 = 1 THEN c3 END), ....

This won't be the fastest query in town...
You'll get as many sorts as you have order bys.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Serge Rielau]

Yep, you don't need r1, r3, r5 and r7 though
I think DB2 throws them away (at least I hope so).

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Tonkuma]

SELECT col1,

MIN(col2),MIN(C ASE WHEN r2 = 1 THEN col3 END),
MIN(col4),MIN(C ASE WHEN r4 = 1 THEN col5 END),
MIN(col6),MIN(C ASE WHEN r6 = 1 THEN col5 END)
FROM t
Group By col1

I think that you need one correction(may be simple careless mistake).

SELECT col1,
MIN(col2),MIN(C ASE WHEN r2 = 1 THEN col3 END),
MIN(col4),MIN(C ASE WHEN r4 = 1 THEN col5 END),
MIN(col6),MIN(C ASE WHEN r6 = 1 THEN col7 END) <-------
FROM t
Group By col1