Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2007
    Posts
    4
    Rep Power
    0

    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
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by jorge3921
    Using DISTINCT with SELECT have effect only for one column.
    no, that's not true

    DISTINCT applies to all columns in the SELECT

    for example, consider this data --
    Code:
    A  B
    1  37
    2  25
    3   9
    4  37
    select distinct A,B ... 4 rows

    select distinct B ... 3 rows
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2007
    Posts
    4
    Rep Power
    0

    You are getting the wrong conclusion!!!!


    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.

    Originally Posted by r937
    no, that's not true

    DISTINCT applies to all columns in the SELECT

    for example, consider this data --
    Code:
    A  B
    1  37
    2  25
    3   9
    4  37
    select distinct A,B ... 4 rows

    select distinct B ... 3 rows
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    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
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2002
    Posts
    608
    Rep Power
    15
    maybe you mean this:
    sql Code:
    SELECT * 
    FROM y 
    WHERE b IN (SELECT b FROM y GROUP BY b HAVING COUNT(*) = 1)


    and btw,
    That is not a unique result for all the columns!
    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.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2007
    Posts
    4
    Rep Power
    0
    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
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by jorge3921
    ... and one row for each of the rows with duplicate values for the selected cloumn.
    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
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2007
    Posts
    4
    Rep Power
    0
    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.
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    so you want something like this --
    Code:
    select min(col1)
         , max(col2)
         , avg(col3)
         , phone
      from daTable
    group
        by phone

    Comments on this post

    • f'lar agrees : LOL!
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    2
    Rep Power
    0
    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 ?
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by UndiFineD
    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
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    1
    Rep Power
    0
    SELECT *
    FROM y
    WHERE (b IN
    (SELECT DISTINCT b
    FROM y AS y_1))
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by humaira
    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
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    101
    Rep Power
    9
    If we came to original question

    it looks like you looking for

    Code:
    select distinct min(a) as a, b 
    from table1
    group by b
  28. #15
  29. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by gk53
    Code:
    select distinct min(a) as a, b 
    from table1
    group by b
    in this query, DISTINCT is totally redundant and unnecessary
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo