Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

remove dups and Order the rows
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 189
Location: INDIA

PostPosted: Tue Jul 19, 2011 10:33 pm    Post subject: remove dups and Order the rows
Reply with quote

Hi,

My table has data defined below. Please help me to get the expected output.


COL_A COL_B
-------- ---------
P1 ALL PARTS
P2 CLOSED PART 2
P2 CLOSED PART 2
P3 PART 3
P4 BLUE PART
P5 PART 5
P6 CLOSED PART 6


My output table should not contain duplicate ROWS and the rows containing CLOSED should appear in the bottom of the result output.

Can we design a query for this?

Regards
Amar
Back to top
View user's profile Send private message

Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Tue Jul 19, 2011 11:00 pm    Post subject:
Reply with quote

You didn't design your indexes properly, did you?
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 189
Location: INDIA

PostPosted: Tue Jul 19, 2011 11:01 pm    Post subject: Reply to: remove dups and Order the rows
Reply with quote

No there are no indexes, in fact there is no primary key at all for this table.
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Tue Jul 19, 2011 11:05 pm    Post subject:
Reply with quote

IOW, you didn't design the indexes properly (i.e., you decided that no indexes were necessary).
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 189
Location: INDIA

PostPosted: Tue Jul 19, 2011 11:08 pm    Post subject:
Reply with quote

Actually this is not a real table defined in business. It was given as a exercise to design a query if there is a table like this. So we need to provide a solution for that by designing the query. :-)
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Jul 20, 2011 11:19 am    Post subject:
Reply with quote

You can use something like
Code:

SELECT COL_A, COL_B
FROM TABLE
ORDER BY CASE
      WHEN SUBSTR(COL_B,1,6) = 'CLOSED' THEN 2
      ELSE 1
    END ASC


Not tested... Let us know if this works for you...
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1712
Location: UK

PostPosted: Wed Jul 20, 2011 2:32 pm    Post subject:
Reply with quote

Gylbharat - you missed the part wre he wants DISTINCT rows. And test before you post.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Jul 20, 2011 2:37 pm    Post subject:
Reply with quote

We can put a distinct clause...
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Jul 20, 2011 3:28 pm    Post subject:
Reply with quote

Amar,

Quote:
It was given as a exercise to design a query if there is a table like this. So we need to provide a solution for that by designing the query. :-)

This is an exercise given to you. So, what have you done so far, we can start from there.

Clue : result query might involve GROUP BY/DISTINCT and ORDER BY

Thanks,
Sushanth
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Wed Jul 20, 2011 3:35 pm    Post subject: Reply to: remove dups and Order the rows
Reply with quote

if it was an exercise given to you...
ok for hints, but asking for the Full Monty isn' t that too much ?
Back to top
View user's profile Send private message
PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2422
Location: Netherlands, Amstelveen

PostPosted: Wed Jul 20, 2011 4:05 pm    Post subject:
Reply with quote

Isnt DISTINCT used with a SELECT? So the table will not be updated by removing duplicate rows.
Back to top
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Wed Jul 20, 2011 5:38 pm    Post subject:
Reply with quote

This can be done by a simple UNION ALL

Code:

SELECT  DISTINCT COL_A, COL_B                     
FROM TABLE                                       
WHERE COL_B NOT LIKE '%CLOSED%'                   
UNION ALL                                         
SELECT  DISTINCT COL_A, COL_B                     
FROM TABLE                                       
WHERE COL_B  LIKE '%CLOSED%'                     


O/P

Code:


COL_A  COL_B                                                   
-----  --------------------                                   
P1     ALL PARTS                                               
P3     PART 3                                                 
P4     BLUE PART                                               
P5     PART 5                                                 
P2     CLOSED PART 2                                           
P6     CLOSED PART 6             

                             


This applies only rows containing word closed needs to appear at end.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Jul 25, 2011 12:52 pm    Post subject:
Reply with quote

Nowhere in the manual it states that union all will always give rows in order of the selects in the union.
I wouldn't depend on it, especially not when parallelism is involved.

besides the "order by case-expression", you can also use "order by posstr()"

Code:
with cte(COL_A,COL_B) as (
select 'P1', 'ALL PARTS' from sysibm.sysdummy1 union all
select 'P2', 'CLOSED PART 2' from sysibm.sysdummy1 union all
select 'P2', 'CLOSED PART 2' from sysibm.sysdummy1 union all
select 'P3', 'PART 3' from sysibm.sysdummy1 union all
select 'P4', 'BLUE PART' from sysibm.sysdummy1 union all
select 'P5', 'PART 5' from sysibm.sysdummy1 union all
select 'P6', 'CLOSED PART 6' from sysibm.sysdummy1 )

select col_A,col_b from cte
group by col_a,col_b
order by posstr(col_b,'CLOSED')
)
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Mon Jul 25, 2011 1:04 pm    Post subject:
Reply with quote

GuyC wrote:
Nowhere in the manual it states that union all will always give rows in order of the selects in the union.
I wouldn't depend on it, especially not when parallelism is involved.

besides the "order by case-expression", you can also use "order by posstr()"

Code:
with cte(COL_A,COL_B) as (
select 'P1', 'ALL PARTS' from sysibm.sysdummy1 union all
select 'P2', 'CLOSED PART 2' from sysibm.sysdummy1 union all
select 'P2', 'CLOSED PART 2' from sysibm.sysdummy1 union all
select 'P3', 'PART 3' from sysibm.sysdummy1 union all
select 'P4', 'BLUE PART' from sysibm.sysdummy1 union all
select 'P5', 'PART 5' from sysibm.sysdummy1 union all
select 'P6', 'CLOSED PART 6' from sysibm.sysdummy1 )

select col_A,col_b from cte
group by col_a,col_b
order by posstr(col_b,'CLOSED')
)


What will happen if table has P7,P8...Etc... Or the table values changes?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Jul 25, 2011 1:47 pm    Post subject:
Reply with quote

I only added the "with cte" clause for testing purposes
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Jul 25, 2011 7:35 pm    Post subject:
Reply with quote

Hello,

Quote:
What will happen if table has P7,P8...Etc... Or the table values changes?
Suggest you run a few tests on your system and see. . .
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 189
Location: INDIA

PostPosted: Tue Jul 26, 2011 12:19 pm    Post subject:
Reply with quote

Hi All,

Thank you very much for all your replies. Actually I resolved it the next day itself. But forgot to update it in the portal. I apologize for that. For some reason I am not getting emails on this post too. So I was not aware that some updates are happening.

Here is what I did.

Code:


SELECT A.COL_A,CASE WHEN SUBSTR(A.COL_B,2,6) = 'CLOSED'
                             THEN SUBSTR(A.COL_B,2)
                             ELSE A.COL_B
                        END
FROM (SELECT DISTINCT COL_A,CASE WHEN COL_B LIKE 'CLOSED%'
                                                    THEN X'FF'||COL_B
                                                    ELSE COL_B
                                            END AS COL_B
          FROM TABLE
         ) AS A

ORDER BY A.COL_B


Thank you all once again.

Regards
Amar
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Jul 26, 2011 8:33 pm    Post subject: Reply to: remove dups and Order the rows
Reply with quote

Hello,

Quote:
For some reason I am not getting emails on this post too. So I was not aware that some updates are happening.

At the bottom of the "Post a reply" screen is a checkbox that says:
Notify me when a reply is posted.

Make sure this is checked so you receive an e-mail as replies are posted.
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 189
Location: INDIA

PostPosted: Tue Jul 26, 2011 9:27 pm    Post subject:
Reply with quote

Hi,

Thank you very much for the information. I will make sure it is checked from further.

Regards
Amar
Back to top
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Wed Jul 27, 2011 5:37 pm    Post subject:
Reply with quote

Hi GuyC,

Quote:

Nowhere in the manual it states that union all will always give rows in order of the selects in the union.
I wouldn't depend on it, especially not when parallelism is involved


But you depended on the UNION ALL statement to gets rows in order specified by requestor when declaring the temp table CTE and getting rows into it icon_smile.gif

Code:


with cte(COL_A,COL_B) as (
select 'P1', 'ALL PARTS' from sysibm.sysdummy1 union all
select 'P2', 'CLOSED PART 2' from sysibm.sysdummy1 union all
select 'P2', 'CLOSED PART 2' from sysibm.sysdummy1 union all
select 'P3', 'PART 3' from sysibm.sysdummy1 union all
select 'P4', 'BLUE PART' from sysibm.sysdummy1 union all
select 'P5', 'PART 5' from sysibm.sysdummy1 union all
select 'P6', 'CLOSED PART 6' from sysibm.sysdummy1 )

Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm
No new posts Can we use OPTIMIZE FOR 1 ROWS for fe... cvijay784 DB2 1 Fri Aug 05, 2016 11:56 am
No new posts Remove Special Characters from Mainfr... Rodger Zhang All Other Mainframe Topics 6 Wed Jul 06, 2016 1:12 am
No new posts SQL Order By related question Joseph K Thomas DB2 8 Fri Mar 18, 2016 12:53 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us