Selection top 3 where distinct

Question

Selection top 3 where distinct

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

Good afternoon guys.

link

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

My problem is as follows, I need to make a selection of products and all of them separated by vendor, for example.

Imagine that my bank has a product and supplier register, for example, soccer balls, and I have registered 5 balls of the Penalty, 19 of Adidas and 3 of 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 just bringing one product from each vendor, it brings random items in the middle of the bank.

Change -

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));

This is basically my select bank that I tried was this:

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 has returned, but it has taken a null and repeated the products for some reason that I can not see

sql sql-select distinct

asked by anonymous 02.10.2018 / 22:21

2 answers

0

I got it thanks for the tip, Marcelo.

What I was doing, before I was doing the comparison by name, but it worked when I matched the IDs of the vendors table and the associative ProductProvider table. This is how the code

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 if someone wanted to get this result too?

04.10.2018 / 16:20

How to put in hierarchy How to make complex queries on firebase with Ionic?

score 0 · Accepted Answer

Good evening,

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

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

  SELECT 
    distinct forn.nome as Fornecedor,

2- This subselect follows the same logic I mentioned earlier .. 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 table Provider is the table where you have the information you need, because you have the Product X Vendor cross.

from produtoFornecedor pf

4- Performed the join with the Vendor table so that in step 1 you can list its vendor name instead of the code.

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

5- The following is 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

Your query was somewhat confusing. The table in FROM was the one of products and made several outer joins. I suggest you take a look at the difference of INNER JOIN and OUTER JOIN (right, left, etc.) to remedy any doubts.

Good luck and good study!

Good morning,

Based on your description of getting one of each template, I created a similar MySQL example for you to base.

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

You need to return one city from each country, right? A simple way to do this is to first select the different existing countries:

 select distinct sg_pais
 from cidade c1;

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

Then you subselect, equate 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 city names (Brazil1, London and Paris) will come randomly because I did not pass any filters in the subselect, I just requested a registration through limit 1. If you have to filter, include the conditions within the subselect. p>

Good luck!