Confusion About Select Distinct In Sql

When I select COLUMN_1 only I get the right amount of 8 rows in my database because it doesn't give me duplicate rows and its what I want SELECT DISTINCT COLUMN_1 FROM TABLE WHERE

Solution 1:

You have to decide which value for column_2 you want.

One simple way is a group by:

SELECT COLUMN_1, min(COLUMN_2)
FROMTABLEWHERE COLUMN_3=12GROUPBY COLUMN_1;

Solution 2:

I'm not totally clear on your exact requirements but a nice solution here could be to use some king of aggregation function that prevents the duplication but still returns all of the values from the column causing the duplication e.g.

The following data,

COLUMN1 | COLUMN2-----------------a       | xb       | xc       | ya       | zb       | ya       | y

Would be displayed as,

COLUMN1 | COLUMN2
-----------------
a       | x,z,y
b       | x,y
c       | y

You don't specify the database you are using but in Oracle you could use the LISTAGG function which could be something like the following in your case:

SELECT COLUMN1,
       LISTAGG(COLUMN2, ',') WITHINGROUP (ORDERBY COLUMN2) "COLUMN2_VALUES",
  FROMTABLE

If you're not using Oracle, and this sounds like a suitable solution, then it's worth checking if you RDBMS of choice provides something similar.