Select unique rows for all columns

Using DISTINCT with SELECT have effect only for one column.
But when is needed to select (or to count) queries for all rows for all columns in a table without duplicates, doesn't work.

Select DISTINCT a1,a2,a3,a4 From Y ---> results 167 rows
Select DISTINCT a4 From Y ---> " 85 rows

Any thoughts?


Jorge3921

no, that's not true

DISTINCT applies to all columns in the SELECT

for example, consider this data --
select distinct A,B ... 4 rows

select distinct B ... 3 rows

If you have a table Y with 2 columns: A and B, and 4 rows, when doing

SELECT DISTINCT A,B from Y

you get 4 rows, including the one row with duplicates values on col B.
That is not a unique result for all the columns!
Meaning, to get the unique values the SELECT query must be done only for the colum with duplicates. But then the query output will be only to that column.
I need to query a big table with 15 columns and 6 million rows to get an output with all the columns, and all the rows with unique values in all the columns.
Maybe now you can understand what I mean.
Thanks.

no, now i don't understand at all

perhaps give a more detailed example? how about an example with 5 columns?

in addition, you might also explain why you want what you want, to help us understand

maybe you mean this:


and btw,
the result does contain the unique results for all of the columns selected when distinct was used. That is the functionality of distinct. You probably want what I've shown above in the sql example.

Thank you for you participation wordracr. Your code will certainly output only all the rows with unique values for the selected column.
But what I need is to have all the rows with columns with unique data and one row for each of the rows with duplicate values for the selected cloumn.

Jorge3921

huh?

which one is the selected column?

and if there is more than one row with the duplicated value for the selected column, then which row did you want?

you will really need to give more specific examples than what you have given for columns a1,a2,a3,a4

Thank you rc97 also, for answering.
Let me try to put it this way:
Using you simple example of an small table with two columns and four rows, there are only 3 rows with unique values for

A B
1 37
2 25
3 9
4 37

the column B.
Now, you wrote that :

select distinct A,B ... 4 rows

and as you can see DISTINCT don't eliminate the duplicated row with the value 37 under column B.
What I'm looking to resolve is to have a SELECT statement that will output all the rows with all the columns with unique values for column B.
In our example:

A B
1 37
2 25
3 9

The difference is that using DISTINCT,
as you wrote:

select distinct B ... 3 rows,

the output is only for column B.

B
37
25
9

As you can see, you wrote

select distinct B

meaning, only one selected column,because otherwise, if adding more columns to the select statement, will not detect the duplicates.

So, if there is a table with 1000 rows and 6 columns, and one of the columns is PHONE , and under Phone there are 100 rows with duplicates numbers, then there are only 950 rows with unique Phones numbers.
This is having 1 duplicates phones number per two rows.
What I need to get are the WHOLE 950 rows with all its columns in the output.
Which of the duplicates rows? Doesn't matter. But only one row per pairs of PHONE duplicated rows. Or one row per each trio of duplicated PHONE rows. Or one row per each quartet of duplicated PHONE rows, etc.

I hope that I was able to express it clearly now.

Thank you

Jorge3921.

so you want something like this --

ok, what about a larger table with multiple duplicates floating around?
I have to fix a program that instead of using an optimised database chose to make it a big spreadsheet :-(
my efforts are succeeding into a new webbased program.

table:
aaaaa bbbbb ccccc ddddd eeeee
0001 alpha centauri rock 0.98
0002 beta centauri gas 1.43
0003 gamma centauri gas 3.03
0004 alpha epsilon lava 3.03
0005 alpha lyra rock 1.43

some silly example with a lot of duplicates

SELECT DISTINCT eeeee AS e FROM table WHERE eeeee >= 0.5 AND eeeee <= 1.5
SELECT DISTINCT ddddd AS d FROM table WHERE ddddd = 'rock'

so how do I JOIN these two tables without knowing which column has less resulting rows than the other ?

which two tables? i see only one

SELECT *
FROM y
WHERE (b IN
(SELECT DISTINCT b
FROM y AS y_1))

two comments about this

first, i doubt very much that UndiFineD is still waiting for a solution after three and a half years

second, the (il)logic of this solution is classic -- you can remove the WHERE clause for the same results

If we came to original question

it looks like you looking for

in this query, DISTINCT is totally redundant and unnecessary