Selection top 3 Where distinct

Asked

Viewed 517 times

0

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)

Resultado

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

  • 1

    To help you better, post the structure of tables and records on http://sqlfiddle.com/

  • http://sqlfiddle.com/#! 18/a81ee/8 I didn’t know how you would need me to put in the tables, but basically two normal tables and a table that gets the ids, not being able to insert the ids in the product tableFornecedor why they keep changing whenever I run a script

  • I adjusted your code in sqlfiddle http://sqlfiddle.com/#! 18/d8f92/3. Could you pass me the experienced result with the data you have there:?

  • So the idea would be to take one product at a time, based on a supplier, because it cannot repeat the supplier. In the link you sent me would bring from the product tableFornecedor, bring the id 1 and id 3 that will differentiate suppliers, I do not know if I could explain you better

2 answers

0


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!

  • I did some tests here, but he returned me only a product the way I did, because I adapted to my need, I do not know where to pass my query, but I will publish here by Google Drive Search : https://drive.google.com/open?id=1Bh2WeKWYLqjhiBBjKfkyHl9qEVFFdjNQy-Hdu7j7aw

  • I mean, he came up with the idea of returning items with only one supplier, but at the time he put the product he just took a product example: product 1; supplier 1; product 1; supplier 2 ...

  • This example I passed solves the question of "I wanted to bring a ball from each supplier. 1 Penalty, 1 Adidas, 1 Umbro.". If that’s not what you need, I really don’t understand the outcome expected of you.. Edit the question and put the tables, fields and what you want back to understand your doubt.

  • I’m sure that if I could adapt the script that you made to my case, it would certainly work, but I must be doing something wrong, but I’ll pass on what I have here, I thought that taking the idea could reproduce here

  • Yes, if your doubt is really this, just an adaptation in your script will work. I suggest you make a new attempt and, if you can’t, post in the question the tables/ columns , some sample data and the expected result. So we can help you right.

0

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?

Browser other questions tagged

You are not signed in. Login or sign up in order to post.