Nested query needed?

Transact-SQL
1

Trying to get the most frequent return back. The following results would need to use Excel with Vlookup to get what I need.

Select Part_Type, Model_Type, Qty, Count(Qty) as Frequent_Order
From Cust_Tbl
Where Model_Type = "A" and Part_Type in ('R01', 'R02', 'R03',...'R20')
Group by Part_Type, Model_Type, Qty
Order by Frequent_Order DESC

Desired output:
Part_Type | Model_Type | Qty
R01 | A | 2
R01 | BB | 16
R12 | BB | 16
R12 | CCC | 155

I tried putting TOP 1 function on the first row, but that only return on R01. I don't know how to pass "A", BB", "CCC" and R02, R03, etc. through the query.

Select TOP 1 Part_Type, Count(Qty) as Frequent_Order
From Cust_Tbl
Where Model_Type = "A" and Part_Type in ('R01', 'R02', 'R03',...'R20')
Group by Part_Type

Many thanks,
Herman
SQL Sample table

3

hi

Hope this helps :slight_smile:

please click arrow to the left for DROP CREATE Sample Data 
DROP TABLE #Cust_Tbl

CREATE TABLE #Cust_Tbl
(
item INT IDENTITY(1,1) NOT NULL ,
Part_Type VARCHAR(10),
Model_Type VARCHAR(10),
Customer VARCHAR(10),
Qty INT 
)
GO  

INSERT INTO [#Cust_Tbl]
     (
         [Part_Type]
         , [Model_Type]
		 , [Customer]
         , [Qty]
     )
VALUES
    ('R01'     -- Party_Type - varchar(10)
     , 'A'   -- Customer - varchar(10)
     , 'N01'   -- Model_Type - varchar(10)
     , 1    -- Qty - int
    )

INSERT INTO [#Cust_Tbl]
     (
         [Part_Type]
         , [Model_Type]
		   , [Customer]
         , [Qty]
     )
VALUES
    ('R01'     -- Party_Type - varchar(10)
     , 'A'   -- Customer - varchar(10)
     , 'N02'   -- Model_Type - varchar(10)
     , 2    -- Qty - int
    )

INSERT INTO [#Cust_Tbl]
     (
         [Part_Type]
         , [Model_Type]
		 , [Customer]
         , [Qty]
     )
VALUES
    ('R01'     -- Party_Type - varchar(10)
     , 'A'   -- Customer - varchar(10)
     , 'N03'   -- Model_Type - varchar(10)
     , 2    -- Qty - int
    )

INSERT INTO [#Cust_Tbl]
     (
         [Part_Type]
         , [Model_Type]
		 , [Customer]
         , [Qty]
     )
VALUES
    ('R01'     -- Party_Type - varchar(10)
     , 'A'   -- Customer - varchar(10)
     , 'N04'   -- Model_Type - varchar(10)
     , 6    -- Qty - int
    )


--------------------------------
INSERT INTO [#Cust_Tbl]
     (
         [Part_Type]
         , [Model_Type]
		 , [Customer]
         , [Qty]
     )
VALUES
    ('R01'     -- Party_Type - varchar(10)
     , 'BB'   -- Customer - varchar(10)
     , 'N05'   -- Model_Type - varchar(10)
     , 1    -- Qty - int
    )

INSERT INTO [#Cust_Tbl]
     (
         [Part_Type]
         , [Model_Type]
		   , [Customer]
         , [Qty]
     )
VALUES
    ('R01'     -- Party_Type - varchar(10)
     , 'BB'   -- Customer - varchar(10)
     , 'N06'   -- Model_Type - varchar(10)
     , 20   -- Qty - int
    )

INSERT INTO [#Cust_Tbl]
     (
         [Part_Type]
         , [Model_Type]
		 , [Customer]
         , [Qty]
     )
VALUES
    ('R01'     -- Party_Type - varchar(10)
     , 'BB'   -- Customer - varchar(10)
     , 'N07'   -- Model_Type - varchar(10)
     , 15    -- Qty - int
    )

INSERT INTO [#Cust_Tbl]
     (
         [Part_Type]
         , [Model_Type]
		 , [Customer]
         , [Qty]
     )
VALUES
    ('R01'     -- Party_Type - varchar(10)
     , 'BB'   -- Customer - varchar(10)
     , 'N08'   -- Model_Type - varchar(10)
     , 2    -- Qty - int
    )

SELECT 'Sample Data',* FROM [#Cust_Tbl] AS [CT]
GO

image

;
WITH [cte]
AS (
       SELECT
            ROW_NUMBER() OVER (PARTITION BY
                                   [#Cust_Tbl].[Part_Type]
                                   , [#Cust_Tbl].[Model_Type]
                               ORDER BY
                                   [#Cust_Tbl].[Qty] DESC) AS [rn]
            , [#Cust_Tbl].[item]
            , [#Cust_Tbl].[Part_Type]
            , [#Cust_Tbl].[Model_Type]
            , [#Cust_Tbl].[Customer]
            , [#Cust_Tbl].[Qty]
       FROM
            [#Cust_Tbl]
   )
SELECT
    [cte].[rn]
    , [cte].[item]
    , [cte].[Part_Type]
    , [cte].[Model_Type]
    , [cte].[Customer]
    , [cte].[Qty]
FROM
    [cte]
WHERE
    [cte].[rn] = 1;

image

2 Likes
4

Thanks harishgg1. This works! Now. The inner query with Row_Number()...has the same logic as mike1, which I now begin to appreciate the beauty. It looks as thought SQL reorganize the entire table before executing the conditions in the where clause. Is CTE more efficient say than Mike1's solution? My main table has over 600k records, and I am looking at a few thousand interested records. Thanks again!