473,320 Members | 2,088 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Db2 Min Value with Char field.

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
4 3494
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

30
by: Alf P. Steinbach | last post by:
The C++ FAQ item 29.5 (this seems to be strongly related to C), at <url: http://www.parashift.com/c++-faq-lite/newbie.html#faq-29.5> mentions that <quote> C++ guarantees a char is exactly one...
3
by: Poul Møller Hansen | last post by:
Hi, I need an auto incrementing field that will contain values like N000001, N000002, N000003 etc. I think the way is to use the value from an identity field in a stored procedure that is...
10
by: MaRCeLO PeReiRA | last post by:
Hi guys, I am in troubles with a SERIAL field. I have five tables. A parent table and four child tables. When I do the INSERT in the parent table, I have an ID (generated) by the sequence...
13
by: dbuchanan | last post by:
Hello, Here is the error message; ---------------------------- Exception Message: ForeignKeyConstraint Lkp_tbl040Cmpt_lkp302SensorType requires the child key values (5) to exist in the...
33
by: bill | last post by:
In an application I am writing the user can define a series of steps to be followed. I save them in a sql database using the field "order" (a smallint) as the primary key. (there are in the range...
1
by: David Greenberg | last post by:
Hi I have a character field (char ot varchar) that I want to force only to contain numeric characters. Can that be done by way of defining a constraint on the field ? or by any other way in the...
7
by: dana_livni2000 | last post by:
how do i print the acuale byte sequence that represent a vairable (let say a char - i want to see the 8 bits). thanks dana
2
by: iritchie | last post by:
*(Apologies, I posted this in the SQL Server forum first) Hello all, I am trying to write a query which breaks down a single address field into individual fields, with char(10) or a...
14
by: Jim Langston | last post by:
The output of the following program is: 1.#INF 1 But: 1.#INF 1.#INF was expected and desired. How can I read a value of infinity from a stream?
0
prabirchoudhury
by: prabirchoudhury | last post by:
CRITERIA; +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.