|
View previous topic :: View next topic
|
| Author |
Message |
Guirao
New User
Joined: 27 Nov 2007
Posts: 7
Location: Madrid
|
|
|
|
Hi all,
I have a query that obtain the data from the 10 first users ordered by cpu time consumption from one day:
| Code: |
SELECT DATE,MVS_SYSTEM_ID,USER_ID,
CPU_TOTAL_SECONDS
FROM DRL.MVS_STAT_TSO_D
WHERE DATE = '2011-02-17'
AND MVS_SYSTEM_ID = 'CR01'
AND COD_BANCSB = 182
GROUP BY DATE, MVS_SYSTEM_ID,USER_ID
ORDER BY CPU_TOTAL_SECONDS DESC
FETCH FIRST 10 ROWS ONLY |
I would like, in the same query, to obtain the 10 first users ordered by cpu time consumption from EACH day where date is going to be from A to B like this:
WHERE DATE BETWEEN '2011-01-01' AND '2011-01-31'
I think that I have to develop a subquery or something like that, no?
I don't have much expertise in this area and would thank any help received.
Thanks in advance,
Guirao. |
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| try UNION. |
|
| Back to top |
|
|
Guirao
New User
Joined: 27 Nov 2007
Posts: 7
Location: Madrid
|
|
|
|
Thanks for your answer Dick.
Well, if I use UNION i must indicate each date, and, if i want to put an interval using 'BETWEEN' this doesn't the effect that I want.
Apart from that, I want to order each data from cpu time grouping by the date and selecting the first 10 rows who have the most consumption from each date, and with 'UNION' first of all I must select the data and then group and order by I want.
Any suggestions? |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
Select somehow the 10 dates your interested in (using sysdummy1 or group by)
| Code: |
| select date from tab1 where date between :min and :max group by date |
join this with the select you already hae:
| Code: |
select * from
(select date from tab1 where date between :min and max group by date) A
join table (SELECT DATE,MVS_SYSTEM_ID,USER_ID,
CPU_TOTAL_SECONDS
FROM DRL.MVS_STAT_TSO_D
WHERE DATE = a.date
AND MVS_SYSTEM_ID = 'CR01'
AND COD_BANCSB = 182
GROUP BY DATE, MVS_SYSTEM_ID,USER_ID
ORDER BY CPU_TOTAL_SECONDS DESC
FETCH FIRST 10 ROWS ONLY ) B on 1=1 |
I didn't test it, so it could contain syntax errors. |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hi Guirao,
Try something like this,
| Code: |
Select rank, date,MVS_SYSTEM_ID, USER_ID , CPU_TOTAL_SECONDS
(
SELECT DENSE_RANK ( ) OVER (PARTITION BY DATE, MVS_SYSTEM_ID, USER_ID
ORDER BY CPU_TOTAL_SECONDS DESC) AS RANK
, DATE
, MVS_SYSTEM_ID
, USER_ID
, CPU_TOTAL_SECONDS
FROM DRL.MVS_STAT_TSO_D
WHERE DATE BETWEEN '2011-01-01' AND '2011-01-31'
AND COD_BANCSB = 182
) as MVS
where rank <=10
; |
Thanks,
Sushanth |
|
| Back to top |
|
|
Guirao
New User
Joined: 27 Nov 2007
Posts: 7
Location: Madrid
|
|
|
|
I like the two answers but I cannot correct the sintax errors... :-(
In the first case, I cannot reolve the syntax error, here is the error:
| Code: |
NUMBER OF ROWS SELECTED 0
--------+---------+---------+---------+---------+---------+---------+---------+
SQL CODE IS -199
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD ORDER. TOKEN ) UNION
EXCEPT WAS EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 506 0 0 -1 274 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001FA' X'00000000' X'00000000' X'FFFFFFFF'
X'00000112' X'00000000' SQL DIAGNOSTIC INFORMATION
OLLBACK
--------+---------+---------+---------+---------+---------+---------+---------+
NUMBER OF SQL STATEMENTS PROCESSED IS 1
NUMBER OF INPUT RECORDS READ IS 11
NUMBER OF OUTPUT RECORDS WRITTEN IS 34 |
In the second case, when I excute the SQL ( I have DB2 v8 for z/OS ), I don't know if this is valid in z/OS, this is the SQLCODE:
| Code: |
NUMBER OF ROWS SELECTED 0
---------+---------+---------+---------+---------+---------+---------+-----
SQL CODE IS -104
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "DENSE_RANK". SOME SYMBOLS
THAT MIGHT BE LEGAL ARE: + - AS ORDER
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 502 0 0 -1 71 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001F6' X'00000000' X'00000000' X'FFFFFFFF'
X'00000047' X'00000000' SQL DIAGNOSTIC INFORMATION
ROLLBACK
---------+---------+---------+---------+---------+---------+---------+-----
NUMBER OF SQL STATEMENTS PROCESSED IS 1
NUMBER OF INPUT RECORDS READ IS 13
NUMBER OF OUTPUT RECORDS WRITTEN IS 36 |
Thank you very much for your help. |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| In the second case, the problem may be with the version of DB2 that you are using; ISTR that the DENSE_RANK function was only introduced with V9. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| in the first case to "order by" in a subselect is also V9 |
|
| Back to top |
|
|
Guirao
New User
Joined: 27 Nov 2007
Posts: 7
Location: Madrid
|
|
|
|
Ok dears,
If this functions only works with V9, I will wait to my DB2 administrators to migrate the version of the DB2 to v9, planned in this months.
I will post my results when I can repeat this SQL's.
Thank you another time for your time and for your help.
Have a nice day. :-) |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Good luck - we'll be here after the upgrade 
d |
|
| Back to top |
|
|
|
|
