Find the highest and second in the query

Transact-SQL
1

Hi,

I have below table data vendor and vendorAmt

vendorID-----VendorType
1234---------ABCD
8907--------XYZ
4567--------PQRS
6785--------Undefined
3490--------FRST
7304--------Undefine

VendorID------Month-----Amount
1234---------Jan21------1000
8907---------Jan21------2000
4567---------Jan21------1500
1234---------FEB21------3000
3490---------FEB21------1000
6785---------FEB21------4000
6785---------Mar21------1000
7304---------Mar21------2000
4567---------Apr21------1500
6785---------Apr21------4000
8907---------Apr21------3000

Need to find the vendor type based on highest Amount
Condition - get the highest Vendor type based on amount but if highest vendor type is undefined then get the second highest vendor type (month FEB data). If all vendor type is undefined then undefined (Mar data), if highest is Undefined then pick the second value (Apr data)

Jan - Vendor type (XYZ/PQRS/ABCD) --- correct value is XYZ
FEB - Vendor type (Undefined/ABCD/FRST) -- correct value is ABCD
Mar - Vendor type (Undefined/Undefined) - - correct value is Undefined
Apr - vendor Type (Undefined/XYZ/PQRS) --- correct value is XYZ

Month-------VendorID-----Vendor Type
Jan21-------8907---------XYZ
FEB21-------1234---------ABCD
Mar21-------7304---------Undefined
Apr21-------8907---------XYZ

Any suggestion will be helpful

thanks in advance

2

There are several options for cases like this. I would create a new field with a case statement:
SELECT CASE WHEN vendor.VendorType='Undefined' THEN 0 ELSE 1 END AS VendorLogic
and a row_number but if you find that a little bit to hard you can always multiply the amount of Undefined with -1 and a max.

If you provide some data we can easily use I can make an example.

ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Docs

3

If you'll post directly usable sample data -- CREATE TABLE and INSERT statement(s) -- I'll write the SQL for you.

4

I have added the sample data, any suggestion will be great. thanks

CREATE TABLE #vendor
(
vendorID int,
VendorType varchar(50)
)

INSERT INTO #vendor
values(1234,'ABCD'),
(8907,'XYZ'),
(4567,'PQRS'),
(6785,'Undefined'),
(3490,'FRST'),
(7304,'Undefined')

SELECT * FROM #vendor

CREATE TABLE #vendorAmount
(
VendorID int, Month_Name varchar(10), Amount decimal(14,6)
)

INSERT INTO #vendorAmount
values(1234,'Jan21',1000),
(8907,'Jan21',2000),
(4567,'Jan21',1500),
(1234,'FEB21',3000),
(3490,'FEB21',1000),
(6785,'FEB21',4000),
(6785,'Mar21',1000),
(7304,'Mar21',2000),
(4567,'Apr21',1500),
(6785,'Apr21',4000),
(8907,'Apr21',3000)

SELECT * FROM #vendorAmount

5 
;WITH cte_sorted_amounts AS (
    SELECT va.*, v.vendorType, ROW_NUMBER() OVER(PARTITION BY va.Month_Name
        ORDER BY CASE WHEN v.VendorType = 'Undefined' THEN 1 ELSE 0 END, 
        va.Amount DESC, va.VendorID) AS row_num
    FROM #vendorAmount va
    INNER JOIN #vendor v ON v.vendorID = va.vendorID
)
SELECT Month_Name, VendorID, VendorType, Amount
FROM cte_sorted_amounts
WHERE row_num = 1
ORDER BY CAST('01' + Month_Name AS date)
1 Like
6

As I would prefer @ScottPletcher aswer you could also use this:

With VendorSalesAmount AS
(
	SELECT 
		va.Month_Name, 
		v.VendorType, 
		SUM(
				CASE 
					WHEN VendorType='Undefined' THEN 
						Amount * -1 
					ELSE 
						Amount 
					END
			)  AS Amount /* Undefined should be last option --> * -1 */
	FROM #vendor v
		INNER JOIN #vendorAmount va 
			ON va.VendorID=v.vendorID
	GROUP BY v.VendorType, va.Month_Name
) SELECT
	VSA.Month_Name,
	VSA.VendorType,
	CASE 
		WHEN VSA.VendorType='Undefined' THEN 
			VSA.Amount *-1 /* Back to normal --> * -1 */
		ELSE 
			VSA.Amount 
		END AS Amount
FROM
	VendorSalesAmount VSA
WHERE Amount=(SELECT 
				MAX(Amount) 
			  FROM VendorSalesAmount VSA_
			  WHERE VSA.Month_Name=VSA_.Month_Name
			  )
7

thank you both, I used ScottPlecher suggestion it worked perfectly and got required result. when you get a chance please explain how ORDER BY CASE WHEN v.VendorType = 'Undefined' THEN 1 ELSE 0 END is working. I haven't used case in order by.

8

what you do is actually numbering the rows by specific order:

SELECT va.*, v.vendorType, ROW_NUMBER() OVER(PARTITION BY va.Month_Name
    ORDER BY CASE WHEN v.VendorType = 'Undefined' THEN 1 ELSE 0 END, 
    va.Amount DESC, va.VendorID) AS row_num
FROM #vendorAmount va
INNER JOIN #vendor v ON v.vendorID = va.vendorID

because your special requirement to order undefined as last option we need the case to do so.

9

Got it!! Appreciate your support, thanks for your time.