SELECT TOP 10 rows

debugcn Published at Dev

Marin

I have built an SQL Query that returns me the top 10 customers which have the highest outstanding. The oustanding is on product level (each product has its own outstanding).

Untill now everything works fine, my only problem is that if a certain customer has more then 1 product then the second product or more should be categorized under the same customer_id like in the second picture (because the first product that has the highest outstanding contagions the second product that may have a lower outstanding that the other 9 clients of top 10). How can I modify my query in order to do that? Is it possible in SQL Server 2012?

My query is:

select top 10 CUSTOMER_ID
             ,S90T01_GROSS_EXPOSURE_THSD_EUR
             ,S90T01_COGNOS_PROD_NAME
             ,S90T01_DPD_C
             ,PREVIOUS_BUCKET_DPD_REP
             ,S90T01_BUCKET_DPD_REP
from [dbo].[DM_07MONTHLY_DATA]
where S90T01_CLIENT_SEGMENT = 'PI'
      and YYYY_MM = '2017_01'
group by CUSTOMER_ID
        ,S90T01_GROSS_EXPOSURE_THSD_EUR
        ,S90T01_COGNOS_PROD_NAME
        ,S90T01_DPD_C
        ,PREVIOUS_BUCKET_DPD_REP
        ,S90T01_BUCKET_DPD_REP
order by S90T01_GROSS_EXPOSURE_THSD_EUR desc;

iamdave

You need to calculate the top Customers first, then pull out all their products. You can do this with a Common Table Expression.

As you haven't provided any test data this is untested, but I think it will work for you:

with top10 as
(
    select top 10 CUSTOMER_ID
                 ,sum(S90T01_GROSS_EXPOSURE_THSD_EUR) as TOTAL_S90T01_GROSS_EXPOSURE_THSD_EUR
    from [dbo].[DM_07MONTHLY_DATA]
    where S90T01_CLIENT_SEGMENT = 'PI'
          and YYYY_MM = '2017_01'
    group by CUSTOMER_ID
    order by TOTAL_S90T01_GROSS_EXPOSURE_THSD_EUR desc
)
select m.CUSTOMER_ID
        ,m.S90T01_GROSS_EXPOSURE_THSD_EUR
        ,m.S90T01_COGNOS_PROD_NAME
        ,m.S90T01_DPD_C
        ,m.PREVIOUS_BUCKET_DPD_REP
        ,m.S90T01_BUCKET_DPD_REP
from [dbo].[DM_07MONTHLY_DATA] m
    join top10 t
        on m.CUSTOMER_ID = t.CUSTOMER_ID
order by t.TOTAL_S90T01_GROSS_EXPOSURE_THSD_EUR desc
    ,m.S90T01_GROSS_EXPOSURE_THSD_EUR;

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-07-25

Comments

0 comments

From Dev

Related Related

Article

SELECT TOP 10 rows

SELECT TOP 10 rows

Select next 20 rows after top 10

In MariaDB how do I select the top 10 rows from a table?

SQL - select top 10 rows with the highest number (counted)

select sum of top 10 rows grouped by location column

how to select max top 10 (n) rows with there size?

Select top 10 rows from database and display result randomly

Select top 2 rows in Hive

Entity Framework fetch Top 10 rows

Select rows that equals to 10 digit

select top 2 rows in each category

SQL select top counts for grouped rows

Select top n rows in each group in EntityFramework

How to Select Top 100 rows in Oracle?

MySQL select top rows with same condition values

Select top rows from a sqlplus query

SQL Select Top n percent rows

SELECT TOP 20 rows for each group

How to select top 5 after 20 rows

Select top rows from each group

How do I select top rows in SQL

Dynamically select TOP rows in SQL Server

Select top n rows in each group in EntityFramework

How to select top 5 after 20 rows

Select top ten entries of multiple rows in MySQL

oracle sql select maximum top x rows

how to select top 10 without duplicates

select the top 10 products of a given category

MySQL select top 10 results prepared statement

How to select top 10% of values in SQL