I have a below requirement. I want to get output as mentioned in where clause of the query.
For Eg:- TABLE1 has three columns COL1,COL2 and COL3
Query is:
SELECT COL1,COL2,SUM(COL3)
FROM TABLE1
WHERE (COL1=A AND COL2=1
OR COL1=E AND COL2=5
OR COL1=F AND COL2=6
OR COL1=C AND COL2=3
OR COL1=D AND COL2=4
OR COL1=B AND COL2=2
OR COL1=G AND COL2=7)
GROUP BY COL1,COL2
I want the output as mentioned in above where clause. But, I am getting output in some default order of COL1 and COL2 as below
Actual Output:
A 1 4
B 2 6
C 3 7
D 4 9
E 5 8
F 6 2
G 7 3
Expected Output:
A 1 4
E 5 8
F 6 2
C 3 7
B 2 6
D 4 9
G 7 3
Is there any way in DB2 to get the output as mentioned in where clause.
The sequence will be the same as the "group by" . . .
You will need to use a different method to get what you want.
No it will not. at least not always.maybe if your lucky with the accesspath.
The only guarantee is to use ORDER BY as craq said.
but to solve this specific problem, it's not a very nice solution but it works :
Code:
With Temp(srt, col1,col2) as (
select 1,'A',1 from sysibm.sysdummy1 union all
select 2,'E',5 from sysibm.sysdummy1 union all
select 3,'F',6 from sysibm.sysdummy1 union all
select 4,'C',3 from sysibm.sysdummy1 union all
select 5,'D',4 from sysibm.sysdummy1 union all
select 6,'B',2 from sysibm.sysdummy1 union all
select 7,'G',7 from sysibm.sysdummy1 )
SELECT Temp.COL1,Temp.COL2
, (Select SUM(T1.COL3) from TABLE1 T1 where temp.COL1 = T1.col1 and temp.col2 = T1.col2) as Sum_Col3
FROM temp
order by Temp.srt
OR
Code:
With Temp(srt, col1,col2) as (
select 1,'A',1 from sysibm.sysdummy1 union all
select 2,'E',5 from sysibm.sysdummy1 union all
select 3,'F',6 from sysibm.sysdummy1 union all
select 4,'C',3 from sysibm.sysdummy1 union all
select 5,'D',4 from sysibm.sysdummy1 union all
select 6,'B',2 from sysibm.sysdummy1 union all
select 7,'G',7 from sysibm.sysdummy1 )
SELECT Temp.srt, Temp.COL1,Temp.COL2,SUM(T1.COL3)
FROM temp
join TABLE1 T1 on temp.COL1 = T1.col1 and temp.col2 = T1.col2
GROUP BY Temp.srt,Temp.COL1,Temp.COL2
order by Temp.srt
"With" is just another way to write a Subselect.
As a side remark : if you are going to use ANDs and ORs in one where clause , I suggest to use brackets for clarity.
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
Hello,
Quote:
No it will not. at least not always.maybe if your lucky with the accesspath.
The only guarantee is to use ORDER BY as craq said.
The only "guarantee" is the ORDER BY (which i also mention with some regularity).
But my point was that one cannot ORDER BY one thing and GROUP BY some other thing in the same query. . . I probably should have used more keystrokes. . .
But my point was that one cannot ORDER BY one thing and GROUP BY some other thing in the same query. . . I probably should have used more keystrokes. . .
Sure you can :
Code:
select dbname, max(obid) , count(*) from sysibm.systables
group by dbname
order by 2 desc , 3
or
Code:
group by timestamp order by day(timestamp)
of course with a group by you can't sort on columns not in the group by , but even then it is possible to change the hierarchy of the sort.
suppose you have an index on colA,colB but you need it ordered by colB :
prior to Version 9 you could (depending on cardinalities) gain performance by writing
Code:
group by colA, colB
order by colB, colA
since V9 the optimizer can rearrange the sequence of the columns in the group by to search for a matching index
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
Hi Guy,
Quote:
Sure you can :
Not until V9. For example, V8 gives a -122. . .
I suspect it will be much more valuable to the majority if "things" that are only available in V9 (and probably nfm at that) are so mentioned. . .
From what i can tell most of the forum members are on less than V9 and several of the ones that are on V9 have not gone to the new function mode. Possibly, i'm mistaken
Actually, i've not yet worked with an organization that is running V9 except in a test environment. But then, i don't get around as much these days
