Ufdjrw

What Brexit solution does the DUP want?

Can I make popcorn with any corn?

Why is the design of haulage companies so “special”?

Why are 150k or 200k jobs considered good when there are 300k+ births a month?

Why is an old chain unsafe?

How is it possible for user's password to be changed after storage was encrypted? (on OS X, Android)

What is the white spray-pattern residue inside these Falcon Heavy nozzles?

New order #4: World

Why is this code 6.5x slower with optimizations enabled?

Motorized valve interfering with button?

Can you lasso down a wizard who is using the Levitate spell?

Download, install and reboot computer at night if needed

How can the DM most effectively choose 1 out of an odd number of players to be targeted by an attack or effect?

How do we improve the relationship with a client software team that performs poorly and is becoming less collaborative?

Why do we use polarized capacitor?

Is it legal to have the "// (c) 2019 John Smith" header in all files when there are hundreds of contributors?

How do you conduct xenoanthropology after first contact?

What do you call a Matrix-like slowdown and camera movement effect?

I’m planning on buying a laser printer but concerned about the life cycle of toner in the machine

What would happen to a modern skyscraper if it rains micro blackholes?

Can a German sentence have two subjects?

Why Is Death Allowed In the Matrix?

Can town administrative "code" overule state laws like those forbidding trespassing?

How to make payment on the internet without leaving a money trail?

Queries: To find name of project ,which has used maximum number of parts

Find all tables containing column with specified name - MS SQL ServerForeign key constraints: When to use ON UPDATE and ON DELETESQL group function nested too deeplySQL - Finding parts that were ordered by a customer who ordered more than 2 distinct partsHow can I fix ORA-01427: single-row subquery returns more than one row?SQL query to find total sale per month - oracleSet null to a foreign key which is part of primary keyDatabase query- find most expensive partAssistance with “Missing right parenthesis” errorHow do you correctly format a new table that is pulling from another table while creating constraints in SQL

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

I am doing an assignment in which I have created three tables namely "Project", "Part" and "Use". The structure of the tables are given below:

 SQL> CREATE TABLE PROJECT
 2 (
 3 PNO VARCHAR2(5) PRIMARY KEY,
 4 PNAME VARCHAR2(15) NOT NULL,
 5 CITY VARCHAR2(15) NOT NULL
 6 );

SQL> CREATE TABLE PART
 2 (
 3 PID VARCHAR2(5) PRIMARY KEY,
 4 PNAME VARCHAR2(15) NOT NULL,
 5 COLOR VARCHAR2(15) NOT NULL
 6 );

SQL> CREATE TABLE USE
 2 (
 3 PNO VARCHAR2(5),
 4 PID VARCHAR2(5),
 5 QTY NUMBER CHECK(QTY >5 AND QTY<100),
 6 FOREIGN KEY(PNO) REFERENCES PROJECT,
 7 FOREIGN KEY(PID) REFERENCES PART,
 8 PRIMARY KEY(PNO,PID)
 9 );

I want to write a query to find out the project name which has used the highest number of products.

I am able to write the following query which returns the number of parts used by each project.

SQL> SELECT PNO, SUM(QTY)
 2 FROM USE
 3 GROUP BY PNO;

PNO SUM(QTY)
----- ----------
P4 205
P1 95
P2 95
P3 20

sql oracle10g rdbms

share|improve this question

asked Mar 8 at 6:09

J. AndersonJ. Anderson

122

add a comment | 

0

I am doing an assignment in which I have created three tables namely "Project", "Part" and "Use". The structure of the tables are given below:

 SQL> CREATE TABLE PROJECT
 2 (
 3 PNO VARCHAR2(5) PRIMARY KEY,
 4 PNAME VARCHAR2(15) NOT NULL,
 5 CITY VARCHAR2(15) NOT NULL
 6 );

SQL> CREATE TABLE PART
 2 (
 3 PID VARCHAR2(5) PRIMARY KEY,
 4 PNAME VARCHAR2(15) NOT NULL,
 5 COLOR VARCHAR2(15) NOT NULL
 6 );

SQL> CREATE TABLE USE
 2 (
 3 PNO VARCHAR2(5),
 4 PID VARCHAR2(5),
 5 QTY NUMBER CHECK(QTY >5 AND QTY<100),
 6 FOREIGN KEY(PNO) REFERENCES PROJECT,
 7 FOREIGN KEY(PID) REFERENCES PART,
 8 PRIMARY KEY(PNO,PID)
 9 );

I want to write a query to find out the project name which has used the highest number of products.

I am able to write the following query which returns the number of parts used by each project.

SQL> SELECT PNO, SUM(QTY)
 2 FROM USE
 3 GROUP BY PNO;

PNO SUM(QTY)
----- ----------
P4 205
P1 95
P2 95
P3 20

sql oracle10g rdbms

share|improve this question

asked Mar 8 at 6:09

J. AndersonJ. Anderson

122

add a comment | 

I am doing an assignment in which I have created three tables namely "Project", "Part" and "Use". The structure of the tables are given below:

 SQL> CREATE TABLE PROJECT
 2 (
 3 PNO VARCHAR2(5) PRIMARY KEY,
 4 PNAME VARCHAR2(15) NOT NULL,
 5 CITY VARCHAR2(15) NOT NULL
 6 );

SQL> CREATE TABLE PART
 2 (
 3 PID VARCHAR2(5) PRIMARY KEY,
 4 PNAME VARCHAR2(15) NOT NULL,
 5 COLOR VARCHAR2(15) NOT NULL
 6 );

SQL> CREATE TABLE USE
 2 (
 3 PNO VARCHAR2(5),
 4 PID VARCHAR2(5),
 5 QTY NUMBER CHECK(QTY >5 AND QTY<100),
 6 FOREIGN KEY(PNO) REFERENCES PROJECT,
 7 FOREIGN KEY(PID) REFERENCES PART,
 8 PRIMARY KEY(PNO,PID)
 9 );

I want to write a query to find out the project name which has used the highest number of products.

I am able to write the following query which returns the number of parts used by each project.

SQL> SELECT PNO, SUM(QTY)
 2 FROM USE
 3 GROUP BY PNO;

PNO SUM(QTY)
----- ----------
P4 205
P1 95
P2 95
P3 20

sql oracle10g rdbms

share|improve this question

asked Mar 8 at 6:09

J. AndersonJ. Anderson

122

 SQL> CREATE TABLE PROJECT
 2 (
 3 PNO VARCHAR2(5) PRIMARY KEY,
 4 PNAME VARCHAR2(15) NOT NULL,
 5 CITY VARCHAR2(15) NOT NULL
 6 );

SQL> CREATE TABLE PART
 2 (
 3 PID VARCHAR2(5) PRIMARY KEY,
 4 PNAME VARCHAR2(15) NOT NULL,
 5 COLOR VARCHAR2(15) NOT NULL
 6 );

SQL> CREATE TABLE USE
 2 (
 3 PNO VARCHAR2(5),
 4 PID VARCHAR2(5),
 5 QTY NUMBER CHECK(QTY >5 AND QTY<100),
 6 FOREIGN KEY(PNO) REFERENCES PROJECT,
 7 FOREIGN KEY(PID) REFERENCES PART,
 8 PRIMARY KEY(PNO,PID)
 9 );

SQL> SELECT PNO, SUM(QTY)
 2 FROM USE
 3 GROUP BY PNO;

PNO SUM(QTY)
----- ----------
P4 205
P1 95
P2 95
P3 20

share|improve this question

asked Mar 8 at 6:09

J. AndersonJ. Anderson

122

share|improve this question

asked Mar 8 at 6:09

J. AndersonJ. Anderson

122

asked Mar 8 at 6:09

J. AndersonJ. Anderson

122

asked Mar 8 at 6:09

J. AndersonJ. Anderson

122

3 Answers
3

active

oldest

votes

0

you can try below-

select * from
(
SELECT PNO, SUM(QTY) as totalqty
 FROM USE
 GROUP BY PNO order by SUM(QTY) desc
)A where rownum=1

share|improve this answer

answered Mar 8 at 6:12

fa06fa06

18.9k21019

add a comment | 

0

try like below

 with cte as (
 SELECT PNO, SUM(QTY) as q
 FROM USE
 GROUP BY PNO
) select * from cte where cte.q= (select max(q) from cte)

share|improve this answer

answered Mar 8 at 6:12

Zaynul Abadin TuhinZaynul Abadin Tuhin

18.9k31134

add a comment | 

0

We can try using RANK here, to capture all ties for first place:

WITH cte AS (
 SELECT p.PNAME, SUM(u.QTY) AS QTY,
 RANK() OVER (ORDER BY SUM(u.QTY) DESC) rnk
 FROM PROJECT p
 INNER JOIN USE u
 ON p.PNO = u.PNO
 GROUP BY p.PNO, p.PNAME
)

SELECT PNAME, QTY
FROM cte
WHERE rnk = 1
ORDER BY PNAME;

share|improve this answer

answered Mar 8 at 6:19

Tim BiegeleisenTim Biegeleisen

237k13100160

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55057648%2fqueries-to-find-name-of-project-which-has-used-maximum-number-of-parts%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

you can try below-

select * from
(
SELECT PNO, SUM(QTY) as totalqty
 FROM USE
 GROUP BY PNO order by SUM(QTY) desc
)A where rownum=1

share|improve this answer

answered Mar 8 at 6:12

fa06fa06

18.9k21019

add a comment | 

0

you can try below-

select * from
(
SELECT PNO, SUM(QTY) as totalqty
 FROM USE
 GROUP BY PNO order by SUM(QTY) desc
)A where rownum=1

share|improve this answer

answered Mar 8 at 6:12

fa06fa06

18.9k21019

add a comment | 

you can try below-

select * from
(
SELECT PNO, SUM(QTY) as totalqty
 FROM USE
 GROUP BY PNO order by SUM(QTY) desc
)A where rownum=1

share|improve this answer

answered Mar 8 at 6:12

fa06fa06

18.9k21019

select * from
(
SELECT PNO, SUM(QTY) as totalqty
 FROM USE
 GROUP BY PNO order by SUM(QTY) desc
)A where rownum=1

share|improve this answer

answered Mar 8 at 6:12

fa06fa06

18.9k21019

answered Mar 8 at 6:12

fa06fa06

18.9k21019

answered Mar 8 at 6:12

fa06fa06

18.9k21019

0

try like below

 with cte as (
 SELECT PNO, SUM(QTY) as q
 FROM USE
 GROUP BY PNO
) select * from cte where cte.q= (select max(q) from cte)

share|improve this answer

answered Mar 8 at 6:12

Zaynul Abadin TuhinZaynul Abadin Tuhin

18.9k31134

add a comment | 

0

try like below

 with cte as (
 SELECT PNO, SUM(QTY) as q
 FROM USE
 GROUP BY PNO
) select * from cte where cte.q= (select max(q) from cte)

share|improve this answer

answered Mar 8 at 6:12

Zaynul Abadin TuhinZaynul Abadin Tuhin

18.9k31134

add a comment | 

try like below

 with cte as (
 SELECT PNO, SUM(QTY) as q
 FROM USE
 GROUP BY PNO
) select * from cte where cte.q= (select max(q) from cte)

share|improve this answer

answered Mar 8 at 6:12

Zaynul Abadin TuhinZaynul Abadin Tuhin

18.9k31134

 with cte as (
 SELECT PNO, SUM(QTY) as q
 FROM USE
 GROUP BY PNO
) select * from cte where cte.q= (select max(q) from cte)

share|improve this answer

answered Mar 8 at 6:12

Zaynul Abadin TuhinZaynul Abadin Tuhin

18.9k31134

answered Mar 8 at 6:12

Zaynul Abadin TuhinZaynul Abadin Tuhin

18.9k31134

answered Mar 8 at 6:12

Zaynul Abadin TuhinZaynul Abadin Tuhin

18.9k31134

0

We can try using RANK here, to capture all ties for first place:

WITH cte AS (
 SELECT p.PNAME, SUM(u.QTY) AS QTY,
 RANK() OVER (ORDER BY SUM(u.QTY) DESC) rnk
 FROM PROJECT p
 INNER JOIN USE u
 ON p.PNO = u.PNO
 GROUP BY p.PNO, p.PNAME
)

SELECT PNAME, QTY
FROM cte
WHERE rnk = 1
ORDER BY PNAME;

share|improve this answer

answered Mar 8 at 6:19

Tim BiegeleisenTim Biegeleisen

237k13100160

add a comment | 

0

We can try using RANK here, to capture all ties for first place:

WITH cte AS (
 SELECT p.PNAME, SUM(u.QTY) AS QTY,
 RANK() OVER (ORDER BY SUM(u.QTY) DESC) rnk
 FROM PROJECT p
 INNER JOIN USE u
 ON p.PNO = u.PNO
 GROUP BY p.PNO, p.PNAME
)

SELECT PNAME, QTY
FROM cte
WHERE rnk = 1
ORDER BY PNAME;

share|improve this answer

answered Mar 8 at 6:19

Tim BiegeleisenTim Biegeleisen

237k13100160

add a comment | 

We can try using RANK here, to capture all ties for first place:

WITH cte AS (
 SELECT p.PNAME, SUM(u.QTY) AS QTY,
 RANK() OVER (ORDER BY SUM(u.QTY) DESC) rnk
 FROM PROJECT p
 INNER JOIN USE u
 ON p.PNO = u.PNO
 GROUP BY p.PNO, p.PNAME
)

SELECT PNAME, QTY
FROM cte
WHERE rnk = 1
ORDER BY PNAME;

share|improve this answer

answered Mar 8 at 6:19

Tim BiegeleisenTim Biegeleisen

237k13100160

WITH cte AS (
 SELECT p.PNAME, SUM(u.QTY) AS QTY,
 RANK() OVER (ORDER BY SUM(u.QTY) DESC) rnk
 FROM PROJECT p
 INNER JOIN USE u
 ON p.PNO = u.PNO
 GROUP BY p.PNO, p.PNAME
)

SELECT PNAME, QTY
FROM cte
WHERE rnk = 1
ORDER BY PNAME;

share|improve this answer

answered Mar 8 at 6:19

Tim BiegeleisenTim Biegeleisen

237k13100160

answered Mar 8 at 6:19

Tim BiegeleisenTim Biegeleisen

237k13100160

answered Mar 8 at 6:19

Tim BiegeleisenTim Biegeleisen

237k13100160