How to write select statment get top highest 10000 on count from table parts.roh

Question

How to write select statment get top highest 10000 on count from table parts.roh

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

December 3, 2019 at 12:03 am

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

Result Expected

revision_ID COUNTPARTS COMPANY

1 30 KMCOMPANY

2 20 WANDERCOMPANY

3 18 WILIAMCOMPANY

what I have tried

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]

This topic was modified 4 years, 4 months ago by ahmed_elbarbary.2010.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24690 More actions · Answer 1

Firstly, we have no access to your data, so I doubt you will get a lot of people helping with this. We cannot see any useful information about your data from what you posted.

Without seeing some sample data, it is a bit tricky to know what is happening and why.

You also did not provide a description of what is happening now with your query. Are you getting wrong results? no results? etc. If you look at just #temprev, completely ignoring the compnay name (ie remove the joins), does your initial data look correct?

I expect the problem is with the join onto ROHS table where you are comparing only the revision ID. Lets say revision ID 1 of ZPartID 1 has a count of 10, but revision ID 1 of ZPartID 2 has a count of 100. Since you are joining on revision ID only you will have at least 2 rows for that revision ID. What you probably want to do is remove the join on ROHS and capture ZPartID in #temprev.

But the above is all guesswork as I have no data nor any description as to what you are currently getting.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

ahmed_elbarbary.2010 Hall of Fame Points: 3118 More actions · Answer 2

revision_ID COUNTPARTS COMPANY
1000 3 KMCOMPANY
2000 2 WANDERCOMPANY
3000 1 WILIAMCOMPANY

parts.rohs

zprtid forign key for table [Parts].[Nop_Part]

id zpartid revisionid

parts.rohs
zprtid forign key for table [Parts].[Nop_Part]
id zpartid revisionid 
9000 2 1000
9001 90 1000
9002 50 1000
9003 70 2000
9004 75 2000
9005 40 3000 
[Parts].[Nop_Part] represent main parts
partid not repeated
PartID PartName CompanyID 
2 trans 10
90 resis 10
50 speaker 10
70 screen 40
75 lcd 40
40 converter 70

[Parts].[Nop_Part] represent main parts

partid not repeated

PartID PartName CompanyID


[Parts].[Nop_Part] represent main parts
partid not repeated
PartID PartName CompanyID 
2 trans 10
90 resis 10
50 speaker 10
70 screen 40
75 lcd 40
40 converter 70

CompanyID CompanyName


CompanyID CompanyName
10 KMCOMPANY
40 WANDERCOMPANY
70 WILIAMCOMPANY

pietlinden SSC Guru Points: 63428 More actions · Answer 3

2600 points and you don't know how to post a question?

Help us help you. Please read this article and re-post your question:

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help