| View previous topic :: View next topic |
| Author |
Message |
WallaceC
Beginner
Joined: 17 Dec 2002
Posts: 22
Topics: 10
|
 Posted: Tue Mar 02, 2010 3:45 pm Post subject: Get Part Keys that Have Max Count |
 |
|
I have the following table TBL:
| Code: |
KEY1 KEY2 KEY3 FLD4
---------------------------
A A A WWW
B A A XXX
A B B EEE
|
I want to get the combination of KEY2 and KEY3 with maximum count of rows as follow:
| Code: |
KEY2 KEY3 COUNT
---------------------------
A A 2
|
Could anyone tell me how it can be on a query? |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Tue Mar 02, 2010 4:04 pm Post subject: |
|
|
WallaceC,
Unless I am missing something , isn't it simple as this
| Code: |
SELECT KEY2
,KEY3
,COUNT(*) AS COUNT
FROM TABLE
GROUP BY KEY2
,KEY3
ORDER BY 3 DESC
FETCH FIRST 1 ROW ONLY
;
|
Kolusu |
|
| Back to top |
|
|
WallaceC
Beginner
Joined: 17 Dec 2002
Posts: 22
Topics: 10
|
| Posted: Tue Mar 02, 2010 4:45 pm Post subject: |
|
|
I got -904 for the query. I do not know whether it is related to the setup of the shop or not, but I will verify.
| Quote: |
DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN
UNAVAILABLE RESOURCE. REASON 00C90084, TYPE OF RESOURCE 00000100, AND
RESOURCE NAME DSNDB07
DSNT418I SQLSTATE = 57011 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXRSOR SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -115 13172746 0 13231826 -959250432 14090279 SQL
DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF8D' X'00C9000A' X'00000000' X'00C9E6D2'
X'C6D30000' X'00D70027' SQL DIAGNOSTIC INFORMATION
|
|
|
| Back to top |
|
|
papadi
Supermod
Joined: 20 Oct 2009
Posts: 594
Topics: 1
|
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
