SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table invoices (InvoiceNumber int, InvoiceDate DATETIME, InvoiceTotal int, VendorId int);

insert into invoices(InvoiceNumber, InvoiceDate , InvoiceTotal , VendorId)
values
 (1, '2020-01-21 06:42:24.320', 100, 111)
,(2, '2020-01-21 06:42:24.320', 250, 111)
,(3, '2020-01-21 06:42:24.320', 220, 111)
,(4, '2020-01-21 06:42:24.320', 120, 121)
,(5, '2020-01-21 06:42:24.320', 150, 121)
,(6, '2020-01-21 06:42:24.320', 450, 112)

select * from invoices

SELECT MAX(InvoiceTotal)
     FROM Invoices AS sub
     WHERE VendorID = 121 
     
   


InvoiceNumber
InvoiceDate
InvoiceTotal
VendorId


1
2020-01-21 06:42:24.320
100
111

2
2020-01-21 06:42:24.320
250
111

3
2020-01-21 06:42:24.320
220
111

4
2020-01-21 06:42:24.320
120
121

5
2020-01-21 06:42:24.320
150
121

6
2020-01-21 06:42:24.320
450
112




(No column name)


150


create table Vendors (VendorId int, VendorName varchar(50));

insert into Vendors(VendorId, VendorName)
values
 (111, 'Vendor 111')
,(112, 'Vendor 112')
,(121, 'Vendor 121')

select * from Vendors


VendorId
VendorName


111
Vendor 111

112
Vendor 112

121
Vendor 121


SELECT 
	(SELECT VendorName FROM vendors v where v.vendorid = main.vendorid) VendorName, 
	InvoiceDate, 
	InvoiceNumber, 
	InvoiceTotal
FROM Invoices AS main
WHERE InvoiceTotal >=
    (SELECT MAX(InvoiceTotal)
     FROM Invoices AS sub
     WHERE sub.VendorID = main.VendorID)
ORDER BY VendorName, InvoiceTotal DESC


VendorName
InvoiceDate
InvoiceNumber
InvoiceTotal


Vendor 111
2020-01-21 06:42:24.320
2
250

Vendor 112
2020-01-21 06:42:24.320
6
450

Vendor 121
2020-01-21 06:42:24.320
5
150


SELECT 
	(SELECT VendorName FROM vendors v where v.vendorid = main.vendorid) VendorName, 
	InvoiceDate, 
	InvoiceNumber, 
	InvoiceTotal
FROM Invoices AS main
WHERE InvoiceTotal >
    (SELECT MAX(InvoiceTotal)
     FROM Invoices AS sub
     WHERE VendorID = 121 )
ORDER BY VendorName, InvoiceTotal DESC


VendorName
InvoiceDate
InvoiceNumber
InvoiceTotal


Vendor 111
2020-01-21 06:42:24.320
2
250

Vendor 111
2020-01-21 06:42:24.320
3
220

Vendor 112
2020-01-21 06:42:24.320
6
450

InvoiceNumber	InvoiceDate	InvoiceTotal	VendorId
1	2020-01-21 06:42:24.320	100	111
2	2020-01-21 06:42:24.320	250	111
3	2020-01-21 06:42:24.320	220	111
4	2020-01-21 06:42:24.320	120	121
5	2020-01-21 06:42:24.320	150	121
6	2020-01-21 06:42:24.320	450	112

VendorName	InvoiceDate	InvoiceNumber	InvoiceTotal
Vendor 111	2020-01-21 06:42:24.320	2	250
Vendor 112	2020-01-21 06:42:24.320	6	450
Vendor 121	2020-01-21 06:42:24.320	5	150