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]
December 3, 2019 at 9:02 pm
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.
December 3, 2019 at 11:40 pm
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
December 4, 2019 at 1:33 am
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:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply