By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,233 Members | 1,538 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,233 IT Pros & Developers. It's quick & easy.

Db2 Min Value with Char field.

P: n/a
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

Sep 29 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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/
Sep 29 '06 #2

P: n/a
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/
Sep 29 '06 #3

P: n/a
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/
Sep 29 '06 #4

P: n/a
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

Sep 30 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.