IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

remove dups and Order the rows
IBM Mainframe Forums -> DB2
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 220
Location: USA
PostPosted: Tue Jul 19, 2011 10:33 pm
 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: 1787
Location: Bloomington, IL
PostPosted: Tue Jul 19, 2011 11:00 pm
 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: 220
Location: USA
PostPosted: Tue Jul 19, 2011 11:01 pm
 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: 1787
Location: Bloomington, IL
PostPosted: Tue Jul 19, 2011 11:05 pm
 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: 220
Location: USA
PostPosted: Tue Jul 19, 2011 11:08 pm
 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
 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: 2454
Location: Hampshire, UK
PostPosted: Wed Jul 20, 2011 2:32 pm
 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
 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: 1020
Location: India
PostPosted: Wed Jul 20, 2011 3:28 pm
 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

Superior Member


Joined: 14 Mar 2007
Posts: 10888
Location: italy
PostPosted: Wed Jul 20, 2011 3:35 pm
 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: 2481
Location: Netherlands, Amstelveen
PostPosted: Wed Jul 20, 2011 4:05 pm
 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
 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: 1281
Location: Belgium
PostPosted: Mon Jul 25, 2011 12:52 pm
 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
 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: 1281
Location: Belgium
PostPosted: Mon Jul 25, 2011 1:47 pm
 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

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
PostPosted: Mon Jul 25, 2011 7:35 pm
 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: 220
Location: USA
PostPosted: Tue Jul 26, 2011 12:19 pm
 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

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
PostPosted: Tue Jul 26, 2011 8:33 pm
 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: 220
Location: USA
PostPosted: Tue Jul 26, 2011 9:27 pm
 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
 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
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
PostPosted: Wed Jul 27, 2011 7:49 pm
 Reply with quote
I didn't depend on it, but because in this case the order of the union all was correct, I didn't notice my mistake:

Code:
with cte(COL_A,COL_B) as (
select 'P4', 'BLUE PART' 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 'P2', 'CLOSED PART 2' from sysibm.sysdummy1 union all
select 'P1', 'ALL PARTS' 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'),col_a
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 

Similar Topics
Topic Forum Replies
No new posts Reflection 3270 screen shows only 23 ... Compuware & Other Tools 6
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Sortjoin and Search for a String and ... DFSORT/ICETOOL 1
No new posts Rotate partition-logical & physic... DB2 0
No new posts Exclude rows with > than x occurre... DFSORT/ICETOOL 6
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us