MySQL: ignore selected column when using DISTINCT

Question

Say:

• I want to query colA, colB and colC in my table.

• I want to see the DISTINCT values, but I do not want colA to be the difference criterion.

• Lowering colA is not an option.

What is the best way to structure this query?

Answer 1

There are two cases. Say you have data

ABC (columns) ab c1 ab c2 

Taking different values ​​of A, B, gives only one result (a, b), having two values ​​for column C. Therefore, the question arises, do you want to see all the values ​​of C or only one value for each individual value of columns A and B?

If you want to see only one C value, you can write

 SELECT A, B, MAX(C) FROM YourTable GROUP BY A, B 

On the other hand, if you want to see all the values ​​for C, then

 SELECT DISTINCT A, B, C FROM YourTable WHERE ROW(A,B) IN (SELECT A, B FROM YourTable GROUP BY A, B) 

gives you that. This last alternative is necessary if there are other columns in the table.

Answer 2

I do not know the syntax of the temporary table, so pseudocode, if you want =)

 Select Distinct ColB, ColC into @Temp From SomeTable Where (predicates) Select ColA, ColB, ColC From SomeTable Inner Join @Temp on (SomeTable.ColB = @Temp.ColB and SomeTable.ColC = @Temp.ColC) Where (predicates) /* Added for comments */ 

Hope this helps.

Answer 3

try below structure

 Select cola, colb, colc, CONCAT(convert(cola,char),convert(row_number(), char) 

Hope this helps