Post reply

Partition By Clause

  • June 23, 2019 at 9:49 pm

    #3656747

    Hi Friends I was wondering if I could run this query (see below) without the count column being in the result set ? Or does the Partition By Clause only work with a windows function, in this case COUNT(City). I am using the Northwind Database dbo.Customers table. Thanks !

    Select City,

    CompanyName,

    COUNT(City) OVER(PARTITION BY City) As City

    FROM dbo.customers

     

     

    Attachments:
    You must be logged in to view attached files.

  • June 23, 2019 at 10:18 pm

    #3656749

    What result are you aiming for, here?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • June 24, 2019 at 11:53 am

    #3656889

    Thanks for your help. I want to show only the columns City and Company Name. I dont want to show  column City with a count of the Cities per partition.

  • June 24, 2019 at 1:23 pm

    #3656941

    Data Rat wrote:

    Thanks for your help. I want to show only the columns City and Company Name. I dont want to show  column City with a count of the Cities per partition.

     

    It sounds like either of the following queries will achieve what you are looking for

    SELECT    City, CompanyName
    FROM      dbo.customers
    GROUP BY  City, CompanyName;
    SELECT  DISTINCT
            City, CompanyName
    FROM    dbo.customers;

    How to post data/code on a forum to get the best help.

  • June 24, 2019 at 2:55 pm

    #3656980

    Thanks for your help. But will this query partition the resultset by City? Meaning will I be able to see all of the Cities and Companies? Or will it rollup or aggregate the Company Names and Cities? Thanks !

  • June 24, 2019 at 3:13 pm

    #3656993

    Data Rat wrote:

    Thanks for your help. But will this query partition the resultset by City? Meaning will I be able to see all of the Cities and Companies? Or will it rollup or aggregate the Company Names and Cities? Thanks !

    It will provide a distinct list of all combinations of Company Names and Cities. Try running it for yourself – it won't do any harm.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 6 posts - 1 through 5 (of 5 total)

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