I've got the following group by query that basically takes equipment items per department and summarizes them. Now I've got to convert it to give me the top 10 most expensive equipment items. How may I rearrange the SQL statement to do so -
Here's the SQL:
SELECT DISTINCTROW qTotals.Dept_TOC, qTotals.AltSortOrder, qTotals.Alternate, qTotals.TypeSortID, qTotals.Equip, qTotals.Type, qTotals.Name, qTotals.Furnish, qTotals.Install, Sum(qTotals.Ex_Quantity) AS SumOfEX_QUANTITY, Sum(qTotals.newqty) AS SumOfnewqty, Sum(qTotals.totqty) AS SumOftotqty, qTotals.Unitcost, Sum(qTotals.TotCOST) AS SumOfTotCOST, qTotals.TypeName, qTotals.Manuf, qTotals.Model_no, qTotals.Alt_Code, qTotals.Dept_Name, qTotals.[Spec Reviewed], qTotals.ASE, qTotals.UNM
FROM qTotals
GROUP BY qTotals.Dept_TOC, qTotals.AltSortOrder, qTotals.Alternate, qTotals.TypeSortID, qTotals.Equip, qTotals.Type, qTotals.Name, qTotals.Furnish, qTotals.Install, qTotals.Unitcost, qTotals.TypeName, qTotals.Manuf, qTotals.Model_no, qTotals.Alt_Code, qTotals.Dept_Name, qTotals.[Spec Reviewed], qTotals.ASE, qTotals.UNM;
It's of course connected to qTotals - so please let me show you that one as well -
SELECT DISTINCTROW PROJ_INF.[Project Information], ALTSORT.AltSortOrder, PROJ_DPT.Dept_TOC, PROJ_DPT.Dept_Code, PROJ_RM.Room_Number, PROJ_EQ.Equip, PROJ_ME.Type, PROJ_ME.ASE, PROJ_RM.Rm_Quantity, [Rm_Quantity]*[quantity] AS totqty, PROJ_EQ.Alternate, PROJ_RM.BldgID, PROJ_DPT.Dept_Name, PROJ_RM.Room_Name, PROJ_EQ.Quantity, PROJ_EQ.Ex_Quantity, [totqty]-[EX_QUANTITY] AS newqty, [newqty]*[Unitcost] AS TotCOST, [newqty]*PROJ_ME.ListPrice
AS TotCOST1, [newqty]*[ReqQuotePrice] AS TotQUOTE, PROJ_ME.Name, tblequiptype.TypeName, PROJ_ME.Furnish, PROJ_ME.Install, Actual_Cost(nz(PROJ_ME.Lis
tPrice,0),
PROJ_ME.Re
qQuotePric
e,nz(PROJ_
ME.discoun
t,1),PROJ_
EQ.Alterna
te) AS Unitcost, tblequiptype.TypeSortID, MEDEQ.Manuf, MEDEQ.Model_no, MEDEQ.ListPrice, PROJ_ME.EManuf, PROJ_ME.EModel_no, PROJ_ME.Alt_Code, PROJ_ME.BidPack, PROJ_INF.[Project Name], PROJ_INF.[Project Number], PROJ_INF.[Project Phase], PROJ_ME.Item, PROJ_EQ.[Qty Checked], PROJ_EQ.[Spec Reviewed], PROJ_ME.InstallBy, PROJ_ME.InstallRemarks, PROJ_ME.LeadTimes, PROJ_ME.EstDueDate, PROJ_ME.SplitShipComments,
PROJ_ME.[Vendor Delivery], PROJ_ME.[Payment Terms], PROJ_ME.MEPcriteria, PROJ_RM.Sheet_No, PROJ_ME.ReqQuotePrice, PROJ_EQ.Remarks, tblequiptype.Show, PROJ_DPT.Show, PROJ_RM.Show, PROJ_ME.Show, ALTSORT.Show, PROJ_INF.Show, PROJ_ME.UNM, PROJ_INF.[Client Name], PROJ_ME.Requisition, IIf(PROJ_ME!Requisition Is Null,False,True) AS Req, PROJ_ME.discount, PROJ_RM.Floor, PROJ_ME.Electrical, PROJ_ME.Mechanical, PROJ_ME.Plumbing, PROJ_ME.Emer_power, PROJ_ME.Seismic
FROM (PROJ_INF INNER JOIN (PROJ_DPT INNER JOIN PROJ_RM ON PROJ_DPT.Dept_Code=PROJ_RM
.Dept_Code
) ON PROJ_INF.[Project Information]=PROJ_DPT.Proj
_infid) INNER JOIN (ALTSORT INNER JOIN (tblequiptype INNER JOIN ((MEDEQ RIGHT JOIN PROJ_ME ON MEDEQ.ProductID=PROJ_ME.Al
t_Code) INNER JOIN PROJ_EQ ON PROJ_ME.Code=PROJ_EQ.Equip
) ON tblequiptype.Type=PROJ_ME.
Type) ON ALTSORT.Alternate=PROJ_EQ.
Alternate)
ON PROJ_RM.Room_Number=PROJ_E
Q.Room_Num
ber
WHERE (((PROJ_INF.[Project Information]) Like fProjectID()) AND ((PROJ_DPT.Dept_Code) Like fDeptID()) AND ((PROJ_RM.Room_Number) Like fRoomNumID()) AND ((PROJ_EQ.Equip) Like fEquipCode()) AND ((PROJ_ME.Type) Like fEquipType()) AND ((PROJ_ME.ASE) Like fASEItems()) AND ((PROJ_RM.Rm_Quantity)>0) AND (([Rm_Quantity]*[quantity]
)>0) AND ((PROJ_EQ.Alternate) Like fAltID()) AND ((tblequiptype.Show) Like fShowType()) AND ((PROJ_DPT.Show) Like fShowDept()) AND ((PROJ_RM.Show) Like fShowRoom()) AND ((PROJ_ME.Show) Like fShowEquip()) AND ((ALTSORT.Show) Like fShowAlt()) AND ((PROJ_INF.Show) Like fShowInf()))
ORDER BY PROJ_INF.[Project Information], ALTSORT.AltSortOrder, PROJ_DPT.Dept_TOC, PROJ_RM.Room_Number, PROJ_EQ.Equip;
I would like to know not only the answer but the logic to help create another one the next time. Thank you very much in advance for this.
Start Free Trial
