Db2 Min Value with Char field.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jaip26@gmail.com - 29 Sep 2006 19:20 GMT

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(col4),MIN(col6) respectively.

Any Ideas ?

Thanks

Reply to this Message

Serge Rielau - 29 Sep 2006 20:27 GMT

> All,
>
[quoted text clipped - 12 lines]
> col2,col4,col6 should be the minimum and col3,col5,col7 should be the
> value of MIN(col2),MIN(col4),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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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

Reply to this Message

jaip26@gmail.com - 29 Sep 2006 22:04 GMT

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,NULL)
) AS X(col1,col2,col3,col4,col5,col6,col7)
)

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

> > All,
> >
[quoted text clipped - 33 lines]
> IOD Conference
> http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message

Serge Rielau - 29 Sep 2006 22:43 GMT

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

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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

Reply to this Message

Tonkuma - 30 Sep 2006 17:58 GMT

> SELECT col1,
> MIN(col2),MIN(CASE WHEN r2 = 1 THEN col3 END),
> MIN(col4),MIN(CASE WHEN r4 = 1 THEN col5 END),
> MIN(col6),MIN(CASE 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(CASE WHEN r2 = 1 THEN col3 END),
MIN(col4),MIN(CASE WHEN r4 = 1 THEN col5 END),
MIN(col6),MIN(CASE WHEN r6 = 1 THEN col7 END) <-------
FROM t
Group By col1

Reply to this Message