How to write select statment get top highest 10000 on count

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

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
CountParts DESC
select distinct v.Revision_ID,CountParts,c.CompanyName from #temprev v
inner join [Parts].[ROHS] r on v.Revision_ID=r.Revision_ID
inner join [Parts].[Nop_Part] p on p.PartID=r.ZPartID
inner join [Parts].[Company] c on c.CompanyID=p.CompanyID
order by CountParts desc

CREATE TABLE [Parts].[ROHS](
[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[ZPartID] [int] NULL,
[Revision_ID] [bigint] NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
====================
CREATE TABLE [Parts].[Nop_Part](
[PartID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[PartName] [nvarchar](70) NOT NULL,
[CompanyID] [int] NOT NULL,
CONSTRAINT [PK_Nop_Part] PRIMARY KEY CLUSTERED
(
[PartID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
=============
CREATE TABLE [Parts].[Company](
[CompanyID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[CompanyName] [nvarchar](70) NOT NULL,
CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
(
[CompanyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ramachandran M6y ago
Hi,

Please try below query

SELECT Revision_ID,CountParts INTO #temprev (
SELECT Revision_ID,COUNT(*) OVER(PARTITION BY ZPartID ORDER BY CountParts DESC) AS "CountParts" FROM Parts,

)
AS INC
WHERE INC.CountParts < 10000
0
Laxmidhar Sahoo6y ago
1. Hi
2. You did not select companyname from #temprev
3. select distinct top 10000 Revision_ID,count( ZPartID)as CountParts,companyname into #temprev from [Parts].[ROHS] r group by Revision_ID,companyname having count( ZPartID)>1 ORDER BY
0