ahmed elbarbary

ahmed elbarbary

  • 1.4k
  • 435
  • 17k

How to write select statment get top highest 10000 on count

Dec 2 2019 6:00 PM
How to write select statment get top highest 10000 on count from table parts.rohs based on Revision_id ?
 
I work on sql server 2012 and i need to get top 10000 revision_id and count parts related that having highest parts from parts.Rohs ?
this table have one million rows of parts but parts are repeated
so that i need to count parts related to every revision it then get top 10000 have counts
    1. Result Expected    
    2. revision_ID COUNTPARTS COMPANY    
    3. 1                   30              KMCOMPANY    
    4. 2                   20              WANDERCOMPANY    
    5. 3                   18              WILIAMCOMPANY    

what I have tried
  1. select distinct top 10000 Revision_ID,count( ZPartID)as CountParts into #temprev from [Parts].[ROHS] r group by Revision_ID having count( ZPartID)>1 ORDER BY  
  2.   
  3. CountParts DESC  
  4. select distinct v.Revision_ID,CountParts,c.CompanyName from #temprev v  
  5. inner join [Parts].[ROHS] r on v.Revision_ID=r.Revision_ID  
  6. inner join [Parts].[Nop_Part] p on p.PartID=r.ZPartID  
  7. inner join [Parts].[Company] c on c.CompanyID=p.CompanyID  
  8. order by CountParts desc  
 
  1. CREATE TABLE [Parts].[ROHS](  
  2. [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,  
  3. [ZPartID] [intNULL,  
  4. [Revision_ID] [bigintNULL,  
  5. PRIMARY KEY CLUSTERED  
  6. (  
  7. [ID] ASC  
  8. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  9. ====================  
  10. CREATE TABLE [Parts].[Nop_Part](  
  11. [PartID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,  
  12. [PartName] [nvarchar](70) NOT NULL,  
  13. [CompanyID] [intNOT NULL,  
  14. CONSTRAINT [PK_Nop_Part] PRIMARY KEY CLUSTERED  
  15. (  
  16. [PartID] ASC  
  17. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  18. =============  
  19. CREATE TABLE [Parts].[Company](  
  20. [CompanyID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,  
  21. [CompanyName] [nvarchar](70) NOT NULL,  
  22.   
  23. CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED  
  24. (  
  25. [CompanyID] ASC  
  26. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
 

Answers (2)