How to get the cheapest product for each product type of each company?

Question

I previously listed the valid offers using:

SELECT
    pt.prod_type_description 'Tipo do Produto',
    p.prod_brand 'Marca',
    c.comp_fantasy_name 'Nome da Empresa',
    o.offer_price 'Preco'
FROM offer                  AS o
    INNER JOIN company      AS c ON o.comp_id = c.comp_id
    INNER JOIN product      AS p ON o.prod_id = p.prod_id
    INNER JOIN product_type AS pt ON p.prod_type_id = pt.prod_type_id
WHERE 
    CONVERT(VARCHAR, o.offer_expired_at, 23) >= CONVERT(VARCHAR, dbo.datetimeBrasilia(), 23)
GROUP BY pt.prod_type_id, o.offer_price, p.prod_id, c.comp_fantasy_name, pt.prod_type_description, p.prod_brand, o.offer_id

But my question is how to get the cheapest product (without importance by the brand) of each type of product of each company?

TYPE PRODUCT    |     BRAND     |   COMPANY     |   PRICE
----------------------------------------------------------
Cimento	            Cauê	    MAConstuc	    13.65
Areia	            AB Areias	    MAConstuc	    15.79
Brita	            Briforte	    MAConstuc	    21.30
Paver PaviS	    MotriX	    MAConstuc	    0.65
Paver Raquete	    Forten	    TilJola	    1.20
Cimento	            Votoran	    TilJola	    10.40
Cimento	            Cauê	    TilJola	    14.65
Areia	            AB Areias	    TilJola	    11.56
Brita	            Briforte	    TilJola	     9.99
Paver PaviS	    MotriX	    TilJola	     1.30
Paver Raquete	    Forten	    TilJola	     1.00
Cimento	            Votoran	    KiCalc	    22.90
Cimento	            Cauê	    KiCalc	    20.00
Areia	            AB Areias	    KiCalc	    18.30
Brita	            Briforte	    KiCalc	    17.00
Paver PaviS	    MotriX	    KiCalc	     1.35
Paver Raquete	    Forten	    KiCalc	     0.99

-- example of company TilJola (without Cimento Cauê)
Cimento	            Votoran	        TilJola	        10.40
Areia	            AB Areias	        TilJola	        11.56
Paver PaviS	    MotriX	        TilJola	        1.30
Paver Raquete	    Forten	        TilJola	        1.00

-- example of company KiCalc (without Cimento Votoran)
Cimento	            Cauê	        KiCalc	        20.00
Areia	            AB Areias	        KiCalc	        18.30
Brita	            Briforte	        KiCalc	        17.00
Paver PaviS	    MotriX	        KiCalc	        1.35
Paver Raquete	    Forten	        KiCalc	        0.99

previously I have listed the valid offers using:

Answer 1

Given this structure for the query, the simplest method is window functions in the order by with top (1) with ties:

SELECT TOP (1) WITH TIES
       pt.prod_type_description as [Tipo do Produto],
       p.prod_brand as Marca,
       c.comp_fantasy_name as [Nome da Empresa],
       o.offer_price as Preco
FROM offer        o JOIN
     company      c
     ON o.comp_id = c.comp_id JOIN
     product      p
     ON o.prod_id = p.prod_id JOIN
     product_type pt
     ON p.prod_type_id = pt.prod_type_id
WHERE offer_expired_at >= dbo.datetimeBrasilia()
GROUP BY pt.prod_type_id, o.offer_price, p.prod_id, c.comp_fantasy_name, pt.prod_type_description, p.prod_brand, o.offer_id
ORDER BY ROW_NUMBER() OVER (PARTITION BY pt.prod_type_description, pt.comp_fantasy_name ORDER BY o.offer_price);

Notes some other changes to your query:

• Dates should be compared as dates and not strings.
• In SQL Server, you should never use VARCHAR without a length. The length varies by context and fixing problems caused by not having a length can be really hard to debug.
• Only use single quotes for string and date constants. If you start using them for column names, you'll eventually make a mistake.
• I only use as for column aliases and never for table aliases. Admittedly, this is just a preference, but I think it minimizes the chances of errors (particularly leaving a comma out of the select.

Answer 2

You can try using row_number() function

select * from 
(
SELECT
    pt.prod_type_description 'Tipo do Produto',
    p.prod_brand 'Marca',
    c.comp_fantasy_name 'Nome da Empresa',
    o.offer_price 'Preco',
 row_number() over(partition by c.comp_fantasy_name,pt.prod_type_description order by o.offer_price) as rn
FROM offer                  AS o
    INNER JOIN company      AS c ON o.comp_id = c.comp_id
    INNER JOIN product      AS p ON o.prod_id = p.prod_id
    INNER JOIN product_type AS pt ON p.prod_type_id = pt.prod_type_id
WHERE 
    CONVERT(VARCHAR, o.offer_expired_at, 23) >= CONVERT(VARCHAR, dbo.datetimeBrasilia(), 23)
)A where rn=1