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

Serge Rielau wrote:

ja****@gmail.com 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(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

--

Serge Rielau

DB2 Solutions Development

IBM Toronto Lab

IOD Conference

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