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

  • ahmed_elbarbary.2010

    SSCrazy

    Points: 2599

    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]
  • Mr. Brian Gale

    SSC-Insane

    Points: 22442

    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.

  • ahmed_elbarbary.2010

    SSCrazy

    Points: 2599

    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: 62613

    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

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

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