Selection top 3 Where distinct

Question

Good afternoon, you guys.

https://stackoverflow.com/questions/1925176/sql-server-2008-top-10-and-distinct-together

I need a help, I tried to see in the above section but could not solve my problem.

My problem is the following, I need to make a selection of products and all separated by supplier only, example.

Imagine that in my bank has a register of product and supplier, example, soccer balls, and I have registered 5 balls from Penalty, 19 from Adidas and 3 from Umbro, I wanted to bring a ball from each supplier. 1 Penalty, 1 Adidas, 1 Umbro.

SELECT 
    distinct top 3 sup.Name as Supplier, pr.CommercialName
FROM Product pr
    FULL OUTER JOIN ProductSupplier AS ps ON (pr.IdProduct = ps.IdProduct)
    FULL OUTER JOIN Supplier AS sup ON (ps.IdSupplier = sup.IdSupplier)
WHERE 
pr.CASNo in ('516849','99879846','68487498','984987','6510016847') or 
pr.IdProduct in ('2270')

But instead of bringing only one product from each supplier it brings some random items that are in the middle of the seat.

Altering --

CREATE TABLE Produto (
   id int not null primary key identity(1,1),
   nome varchar(100),
);

CREATE TABLE Fornecedor (
   id int not null primary key identity(1,1),
   nome varchar(100)
);

CREATE TABLE produtoFornecedor(
   id int not null primary key identity(1,1),
   idProduto int FOREIGN KEY REFERENCES produto(id),
   idFornecedor int FOREIGN KEY REFERENCES fornecedor(id));

That’s the bank basically my select that I tried was that:

SELECT 
    distinct sup1.nome as Fornecedor,
    (
        select 
            top 1 (pr2.nome)
        from Produto pr2
            inner JOIN Fornecedor AS sup2 ON (ps.idFornecedor = sup2.idFornecedor)
        WHERE sup2.nome = sup1.nome
    ) as produto
FROM Produto pr
    FULL OUTER JOIN Produto AS ps ON (pr.idProduto = ps.idProduto)
    FULL OUTER JOIN Fornecedor AS sup1 ON (ps.idFornecedor = sup1.idFornecedor)

This is the mass of data that came back, but it took a null and repeated the products for some reason that I can’t see

Answer 1

Good night,

Well, now with the new information , follows adaptation that I made of my first suggestion with the new scenario that you passed (OBS: I could not test the commands, but I will explain the idea and you fix any problem nomenclature and/ or syntax).

1- In this part is listed the name of the supplier.

  SELECT 
    distinct forn.nome as Fornecedor,

2- This subselect follows the same logic as I mentioned before.. for each distinct vendor it will select a product from that vendor and list.

    (select ps.nome
  from produto ps
 where ps.idProduto = pf.idProduto 
  limit 1) as produto

3- The product tableFornecedor is the table where you have the information you need, because in it you have the cross of Product X Supplier.

from produtoFornecedor pf

4- I built Join with the Vendor table so that in step 1 the respective vendor name can be listed instead of the code.

INNER JOIN Fornecedor AS forn ON pf.idFornecedor = forn.idFornecedor

5- Follows the final query:

 SELECT 
    distinct forn.nome as Fornecedor,
    (select ps.nome
  from produto ps
 where ps.idProduto = pf.idProduto 
  limit 1) as produto
FROM produtoFornecedor pf
  INNER JOIN Fornecedor AS forn ON pf.idFornecedor = forn.idFornecedor

This query was a bit confusing. The table in FROM was one of products and made several Uter joins. I suggest you take a look at the difference between INNER JOIN and OUTER JOIN (right, left, etc) to clear up any doubts.

Good luck and good study!

---

Good morning,

Based on your description of taking one of each template, I’ve created a similar Mysql example for you to base on.

+-----------+---------+
| nm_cidade | sg_pais |
+-----------+---------+
| Brasil1   | BR      |
| Brasil2   | BR      |
| Londres   | EN      |
| Londres1  | EN      |
| Paris     | FR      |
| Paris1    | FR      |
| Paris2    | FR      |
+-----------+---------+

You need to return a city from each country, right? One simple way to do it is to first select the distinct existing countries:

 select distinct sg_pais
 from cidade c1;

 Retorno:
+---------+
| sg_pais |
+---------+
| BR      |
| EN      |
| FR      |
+---------+

Then you do a subselect, equalize countries and use limit 1 to return only one.

select distinct sg_pais,
  (select nm_cidade
   from cidade c2
 where c1.sg_pais = c2.sg_pais
  limit 1)
 from cidade c1; 

 Retorno:
+--------------+
| sg_pais      |
+--------------+
| BR   Brasil1 |
| EN   Londres |
| FR   Paris   |
+--------------+

In this example the names of the cities (Brazil1, London and Paris) will come randomly because I did not pass any filter in the subselect, I only requested a record through limit 1. If you have to filter, include the conditions within the subselect.

Good luck!

Answer 2

Opa got thanks for the tip, Marcelo.

What I was doing, before I was making the comparison by name, however it worked when I equalized the Ids of the suppliers table and the associative table Productofornecedor. That’s what the code looks like

SELECT 
    DISTINCT sup.Name,
    (
        SELECT top 1 prd.CommercialName FROM Product AS prd

            FULL OUTER JOIN ProductSupplier AS pSu ON ( prd.IdProduct = pSu.IdProduct)

            FULL OUTER JOIN Supplier AS supJoin ON ( pSu.IdSupplier = supJoin.IdSupplier)

        WHERE pSu.IdSupplier = sup.IdSupplier
    ) AS fornecedor
FROM Supplier AS sup

Does anyone know a better name for the topic in case someone wanted to get that result too?