|
View previous topic :: View next topic
|
| Author |
Message |
V S Amarendra Reddy
Active User
Joined: 13 Sep 2006
Posts: 216
Location: USA
|
|
|
|
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 |
|
 |
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| You didn't design your indexes properly, did you? |
|
| Back to top |
|
|
V S Amarendra Reddy
Active User
Joined: 13 Sep 2006
Posts: 216
Location: USA
|
|
|
|
| No there are no indexes, in fact there is no primary key at all for this table. |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| IOW, you didn't design the indexes properly (i.e., you decided that no indexes were necessary). |
|
| Back to top |
|
|
V S Amarendra Reddy
Active User
Joined: 13 Sep 2006
Posts: 216
Location: USA
|
|
|
|
| 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 |
|
|
gylbharat
Active Member
.jpg)
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| Gylbharat - you missed the part wre he wants DISTINCT rows. And test before you post. |
|
| Back to top |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
| We can put a distinct clause... |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
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 |
|
|
PeterHolland
Global Moderator
Joined: 27 Oct 2009
Posts: 2481
Location: Netherlands, Amstelveen
|
|
|
|
| Isnt DISTINCT used with a SELECT? So the table will not be updated by removing duplicate rows. |
|
| Back to top |
|
|
Prasanthhere
Active User
Joined: 03 Aug 2005
Posts: 306
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
| 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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| I only added the "with cte" clause for testing purposes |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
V S Amarendra Reddy
Active User
Joined: 13 Sep 2006
Posts: 216
Location: USA
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
V S Amarendra Reddy
Active User
Joined: 13 Sep 2006
Posts: 216
Location: USA
|
|
|
|
Hi,
Thank you very much for the information. I will make sure it is checked from further.
Regards
Amar |
|
| Back to top |
|
|
Prasanthhere
Active User
Joined: 03 Aug 2005
Posts: 306
|
|
|
|
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 
| 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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
|
|
