Posted: Mon Mar 19, 2012 5:57 am Post subject: Get three records per group in descending order
I am trying to write a query to get a desired output. Below is the DDL code and DML code.
Code:
CREATE TABLE TEST.TBABE
(
CRIT_PART_GRP_NAME CHAR(08) NOT NULL,
DIV_OWNER_COD CHAR(03) NOT NULL,
PARTNUMB CHAR(12) NOT NULL,
CRITICAL_QTY DECIMAL(9,4) NOT NULL
);
INSERT INTO TEST.TBABE VALUES(' ','AB','0000000P3056',1);
INSERT INTO TEST.TBABE VALUES(' ','AB','0000000P3057',2);
INSERT INTO TEST.TBABE VALUES(' ','AB','0000000P3058',3);
INSERT INTO TEST.TBABE VALUES(' ','AB','0000000P3059',4);
INSERT INTO TEST.TBABE VALUES(' ','AB','0000000P3066',5);
INSERT INTO TEST.TBABE VALUES(' ','AD','0000000P3056',1);
INSERT INTO TEST.TBABE VALUES(' ','AD','0000000P3056',1);
INSERT INTO TEST.TBABE VALUES(' ','AD','0000000P3056',1);
INSERT INTO TEST.TBABE VALUES(' ','AD','0000000P3056',1);
INSERT INTO TEST.TBABE VALUES(' ','AD','0000000P3056',1);
and so on
.
.
My expected output is as below:
Code:
CRIT_PART_GRP_NAME DIV_OWNER_COD PARTNUMB SUMCRITICAL_QTY
---------+---------+---------+---------+---------+---------+---------+---------+
AB 0000000P3066 5.0000
AB 0000000P3059 4.0000
AB 0000000P3058 3.0000
AD 0000000P3056 5.0000
I need to have just three records from the group and the sum should be in descending order. I am trying below code and I get
Code:
SELECT * FROM
(
SELECT
CRIT_PART_GRP_NAME,
DIV_OWNER_COD,
PARTNUMB,
SUM(CRITICAL_QTY) AS SUMCRITICAL_QTY
FROM
TEST.TBABE
GROUP BY
CRIT_PART_GRP_NAME,
DIV_OWNER_COD,
PARTNUMB
ORDER BY DIV_OWNER_COD ASC,PARTNUMB DESC,SUMCRITICAL_QTY DESC
) AS TEST
FETCH FIRST 3 ROWS ONLY;
Code:
CRIT_PART_GRP_NAME DIV_OWNER_COD PARTNUMB SUMCRITICAL_QTY
---------+---------+---------+---------+---------+---------+---------+---------+
AB 0000000P3066 5.0000
AB 0000000P3059 4.0000
AB 0000000P3058 3.0000
Can somebody help me with the correct query to get the output.
select * from
(select CRIT_PART_GRP_NAME
, DIV_OWNER_COD
from tbabe t1
group by CRIT_PART_GRP_NAME
, DIV_OWNER_COD ) as g1
join table
(select PARTNUMB
, SUM(CRITICAL_QTY) AS SUMCRITICAL_QTY
from tbabe t2
where t2.CRIT_PART_GRP_NAME = g1.CRIT_PART_GRP_NAME
and t2.DIV_OWNER_COD = g1.DIV_OWNER_COD
group by PARTNUMB
order by 2 desc
fetch first 3 rows only
) g2 on 1=1
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
