0

There are two tables. P(pno,pname,color) and sp(sno,pno,qty) and s(sno,sname) I want to get the most supplied pname and its total supplied quantity. I wrote query using derived table like

select pno,totalqty 
from (select p.pno, sum(quality) as totalqty 
      from p,sp 
      where p.pno=sp.pno  
      group by p.pno
     )d 
where totalqty = (select max(totalqty) 
                  from (select p.pno, sum(quality) as totalqty  
                        from p,sp 
                        where p.pno=sp.pno 
                        group by p.pno
                       )d
                 );

I have to repeat the expressions that find the total number of quantity of each part. I can not reuse derived name ( I used d). is there any better way?

  • shouldn't from clause has table name not select. – Ahmad Abuhasna Dec 13 '15 at 15:09
  • Not possible in MySQL, but you can put it into a temporary table instead, use a variable to store the max() and then select from it a second time. Putting it into one query would need clonning the temp table because it can be used only once in one query (unfortunate but deep limitation which was not yet been lifted afaik). – jkavalik Dec 13 '15 at 20:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.