Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Apr 2006
    Posts
    16

    Unanswered: Disctinct selections

    Hi I am trying to figure out how to use the DISCTINCT function in s SELECT Query for one particular column, but output more that the disctinct column


    for example:

    table 1

    Alan Andrews 1 main st 07465
    John Andrews 1 main st 07465
    Erick Andrews 1 main st 07465

    I want to select by disctinct last name, but on my results I want to see all the other fields as well, and not just the last name. In this case the first name address and zip code.

    So is there a way of doing this in SQL?

    It does not have to be with the DISCTINCT function, but I need to net down to 1 per last name in a select query.

    Thanks in advance!

    Alan

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Quote Originally Posted by wylderubicon
    Hi I am trying to figure out how to use the DISCTINCT function in s SELECT Query for one particular column, but output more that the disctinct column


    for example:

    table 1

    Alan Andrews 1 main st 07465
    John Andrews 1 main st 07465
    Erick Andrews 1 main st 07465

    I want to select by disctinct last name, but on my results I want to see all the other fields as well, and not just the last name. In this case the first name address and zip code.

    So is there a way of doing this in SQL?

    It does not have to be with the DISCTINCT function, but I need to net down to 1 per last name in a select query.

    Thanks in advance!

    Alan
    is there a way to tell which one of the records from thr above example should be displayed ... or should it be a random one.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Apr 2006
    Posts
    16
    It really doesn't matter which one of the records gets selected. I just need to end up with one.

    Is there a nth funtion in SQL perhaps?

    Alan

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, there is no "nth" function

    i should also like to take this opportunity to point out that DISTINCT is not a function either
    Code:
    select lastname
         , min(firstname) as lowest_firstname_for_this_lastname
         , max(address) as highest_address_for_this_lastname
         , avg(zipcode) as average_zip_assuming_its_numeric
      from daTable
    group
        by lastname
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2006
    Posts
    16
    Quote Originally Posted by r937
    no, there is no "nth" function

    i should also like to take this opportunity to point out that DISTINCT is not a function either
    Code:
    select lastname
         , min(firstname) as lowest_firstname_for_this_lastname
         , max(address) as highest_address_for_this_lastname
         , avg(zipcode) as average_zip_assuming_its_numeric
      from daTable
    group
        by lastname
    Ok, this could work but will it return my actual record. For my example I've excluded other fields that I need to select from my table such as Individual number, family number, gender and much more.

    I think my original example was not the best

    Your query if used with the MIN aggregated function won't I end up with the lowest value first name, lowest value gender etc..


    Won't I ended up with the lowest value first name, lowest value gender, and lowest value induvidual number. etc?

    So Alan Andrews might end up with gender code of F and the wrong customer number, etc.?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by wylderubicon
    So Alan Andrews might end up with gender code of F and the wrong customer number, etc.?
    that is quite correct

    it indicates that what you are doing (combining multiple rows for the same last name) is probably the wrong approach, as it will likely mash up different people

    if the same lastname occurs on multiple rows, do you have any way of differentiating the various rows? any rule for which one you want? and please don't say again "oh, any one"

    what is the primary key of your table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2006
    Posts
    16
    Quote Originally Posted by r937
    that is quite correct

    it indicates that what you are doing (combining multiple rows for the same last name) is probably the wrong approach, as it will likely mash up different people

    if the same lastname occurs on multiple rows, do you have any way of differentiating the various rows? any rule for which one you want? and please don't say again "oh, any one"

    what is the primary key of your table?

    I have a Unique Site number that is unique to each family. I will probably be doing the group by this number instead of the last name.

    How about the 1st record in a group?

    I tried using FIRST, but it doesn't appear to be an aggregated function in SQL server 2005.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there is no such concept as "first" because rows don't have a position

    and using Unique Site number merely shifts the problem from lastname, it does not make the problem go away

    you will still need to somehow specify which row you want from the group of rows which all have the same Unique Site number

    the best way to do this is to designate which row based on its primary key, since primary keys are unique


    what is the primary key of your table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Abritrary data is garbage, so why use it at all....

    Can you explain to us what you are trying to do?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Apr 2006
    Posts
    16
    Quote Originally Posted by r937
    there is no such concept as "first" because rows don't have a position

    and using Unique Site number merely shifts the problem from lastname, it does not make the problem go away

    you will still need to somehow specify which row you want from the group of rows which all have the same Unique Site number

    the best way to do this is to designate which row based on its primary key, since primary keys are unique


    what is the primary key of your table?
    I currently don't have a primary key, but I can add one. Once I create this ID field, how would I designate it on my query?


    Thanks for all the help!

  11. #11
    Join Date
    Apr 2006
    Location
    System.Web.UI.WebControls.Home.BedRoom2
    Posts
    42

    Lightbulb This might work

    I know cursors are forbidden or banished to the WASTELAND , and am try to find a way to do this without cursor, sure its possible!

    Till than here is the cursor solution:
    (tweeked the employee table a bit)

    1> run next 11 line as a batch
    declare @tempname varchar(20)
    declare rpt cursor for select distinct lastname from employees
    open rpt
    fetch next from rpt into @tempname
    while @@fetch_status=0
    begin
    select top 1 * from employees where lastname = @tempname
    fetch next from rpt into @tempname
    end
    close rpt
    deallocate rpt

    Take r937 suggestion, using primary key is better any day, besides two people with same surname can reside a two totally different locations.

    Hope it works, have fun

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by wylderubicon
    Once I create this ID field, how would I designate it on my query?
    with a correlated subquery, which has the same effect as grouping
    Code:
    select lastname
         , firstname 
         , address 
         , zipcode
         , newPKcolumn
      from daTable as T
     where newPKcolumn
         = ( select min(newPKcolumn)
               from daTable
              where lastname = T.lastname )
    the effect of the correlated subquery is to chose the (single) row which has the lowest newPKcolumn value from amongst all the rows with the same lastname
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Apr 2006
    Posts
    16
    Quote Originally Posted by Brett Kaiser
    Abritrary data is garbage, so why use it at all....

    Can you explain to us what you are trying to do?
    Bret, I am trying to net my results to one per site number. unsing a select query.

    Thanks

  14. #14
    Join Date
    Apr 2006
    Location
    System.Web.UI.WebControls.Home.BedRoom2
    Posts
    42

    Lightbulb Weird

    r937 you just gave me an idea .
    This is really weird, the following sql command works:

    select * from employees as A
    where firstname=(select min(firstname)
    from employees as B
    where A.lastname = B.lastname)

    Even though i have three persons with the same firstname and it returns unique records. does it work for anybody else!!! (i tweaked the employee table)

  15. #15
    Join Date
    Apr 2006
    Posts
    16
    Quote Originally Posted by r937
    with a correlated subquery, which has the same effect as grouping
    Code:
    select lastname
         , firstname 
         , address 
         , zipcode
         , newPKcolumn
      from daTable as T
     where newPKcolumn
         = ( select min(newPKcolumn)
               from daTable
              where lastname = T.lastname )
    the effect of the correlated subquery is to chose the (single) row which has the lowest newPKcolumn value from amongst all the rows with the same lastname

    This worked great. Thanks for your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •