Make a DISTINCT only in one column?

Question

Make a DISTINCT only in one column?

Navigation

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

1

I want to do a DISTINCT but only apply in a column because I consult two things, an ID and an image and I just want you to show me an image of that product.

I try to keep it that way.

| producto | imagen         |
|----------|----------------|
| 1111     | 0x15A8SA9A.... |
| 1112     | 0x15A8SA9A.... |
| 1113     | 0x15A8SA9A.... |

I have my table like this

Id -> int
producto -> int (relacionado con la tabla producto) 
imagen -> image

Try this way

select producto, imagen 
from imagenes 
where producto in (select producto from imagenes group by producto)

What should I do? How do I make a DISTINCT only in the product column?

I'm using SQL SERVER 2008.

Thank you in advance for reading

sql php sql-server-2008

asked by Thunder 06.08.2017 в 04:36

source

3 answers

Add subgroups in MySQL when a column has a certain value SQL - Group query result in a field

score 1 · Answer 1

What you do

select producto, imagen 
from imagenes 
where producto in (select producto from imagenes group by producto)

In this sentence you are doing something complicated:

select producto from imagenes group by producto

Group the products, from the table images and the groups by product. What gives understand that there may be more than one product with more than one image. Otherwise you are doing select * from imagenes , simply.

 select producto, imagen 
 from imagenes 
 where producto in (select producto from imagenes group by producto)

Then in the consultation, you are saying that if that product exists in the table itself, images.

The query is not correctly mounted.

My solution

an ID and an image and I just want you to show me an image of that product.

Then you only need for example:

SELECT imagen
FROM imagenes
WHERE producto = 1111

This way you get the image of the desired product.

score 0 · Answer 2

select producto, imagen 
from imagenes 
where producto in (select producto from imagenes group by producto)

The problem of this query comes from that although the subquery returns value to you different (1111,1112,1113,etc) the general general query keeps entering all the values for the following reason, since you indicate the following:

Subquery: (1111,1112,1113,1114)
General query: "selects all values if the product_id is in the subquery" . The error is that all the values of the table are already in the subsconsult because despite being duplicated, they still comply with the 'WHERE':
- the 1111 product with the first image is in the subquery
- the 1111 product with the second image is also in the subquery.
Your query without the WHERE parameter will work exactly the same with or without the since all the rows meet the condition.

SOLUTION
The simplest way to do what you say is this:

SELECT producto,imagen 
FROM tabla
GROUP BY producto

Since by default you will get all the different products and the imagen will show the first case home since at the time of doing GROUP BY the first one is placed to not leave the gap blank

score -1 · Answer 3

The solution is this SQL 2008:

For example:

ruc                    clientes
20124512121            madedera los alpes sa 
20124512121            madedera  los alpe sa
20124512121            madedera los  alpe sa
20154251251            maria prado
20154251251            prado maria

There are 2 unique RUCs however they were created with different RS the solution, it is normal to have the two RUCs and grab the first company name and it is done this way:

table: customers fields: ruccliente, rscliente

select  o.* from (SELECT ROW_NUMBER() OVER (PARTITION BY ruccliente ORDER BY ruccliente DESC) AS fila,ruccliente, rscliente FROM TablaTemporalCli2) as o WHERE o.FILA < 2

what it does is:

fila  ruc                    clientes
1     20124512121            madedera los alpes sa 
2     20124512121            madedera  los alpe sa
3     20124512121            madedera los  alpe sa
1     20154251251            maria prado
2     20154251251            prado maria

and with this condition o.FILA < 2 just grab the first row