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/