SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Mentor NightStalker-DNS's Avatar
    Join Date
    Jul 2004
    Location
    Cape Town, South Africa
    Posts
    2,873
    Mentioned
    44 Post(s)
    Tagged
    0 Thread(s)

    sql distinct query

    Hey guys

    I have a query that pulls quite a few columns of data, but I need only 1 of the cloumns to be distinct. ie no duplicates in 1 column, but all the other columns can have duplicates.

    How would i do this?

    I hope someone understands what im trying 2 say.

    Any help wud be much appreciated.

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    there are just too many possibilities for us to guess

    why don't you offer us a few sample rows of data, and then indicate what the query should return

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Mentor NightStalker-DNS's Avatar
    Join Date
    Jul 2004
    Location
    Cape Town, South Africa
    Posts
    2,873
    Mentioned
    44 Post(s)
    Tagged
    0 Thread(s)
    Hey

    Thanks for the reply.

    Ok. this is an eg of the database:
    Code:
    America           01/01/2005      NightStalker        132     123    123
    America           02/01/2006      Stalker               123     145    235
    South Africa     09/05/2004      example             123     123     123
    Now i want it to return all the columns data, but only where the country name is disticnt. ie. The eg above should return 2 rows. 1 for america and another for South Africa.

    Thanks for all the help.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    aha, well, you're going to have some problems

    which value of the date should it return for America? and which value of the username?

    would it be okay to return America with 01/01/2005 and Stalker (notice that they don't come from the same row)?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Mentor NightStalker-DNS's Avatar
    Join Date
    Jul 2004
    Location
    Cape Town, South Africa
    Posts
    2,873
    Mentioned
    44 Post(s)
    Tagged
    0 Thread(s)
    Hey

    Im not sure if thats an option or not. Its not actually as simple as it looks. Im using a join for the table and also need to order by date desc. So, no, i dnt think it is an option.

    Im not sure how im going to do this.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you need to decide which columns, and more specifically which of possibly several different values of those columns, that should be returned along with the distinct country names
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Mentor NightStalker-DNS's Avatar
    Join Date
    Jul 2004
    Location
    Cape Town, South Africa
    Posts
    2,873
    Mentioned
    44 Post(s)
    Tagged
    0 Thread(s)
    Hey

    Ok, say i would need the country, name and date to be from the same row. What do I do now?

    Thanks for all the help!

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    fine, now we're getting someplace

    which row? if several rows have the same country, how do you pick which one you want?

    and please, don't say "the first one" or "any one"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Mentor NightStalker-DNS's Avatar
    Join Date
    Jul 2004
    Location
    Cape Town, South Africa
    Posts
    2,873
    Mentioned
    44 Post(s)
    Tagged
    0 Thread(s)
    Hey

    Im not exactally sure how to answer this. hehe

    OK... Im gona order by date desc. So the 1st record should be chosen, but it doesnt really matter.

    Thats all i can tell u. I dnt no how else to choose a row other than the 1st or any one.

    Maybe i can say the one with the latest date.

    Thanks for all the help. It is much appreciated. I know im askin a lot with a little info.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    okay, we'll use a correlated subquery on the date, choosing the latest

    be aware that if there is more than one row per country with the same latest date, you will get them all
    Code:
    select country
         , logdate
         , username
         , foo
         , bar
      from yourtable as T
     where logdate
         = ( select max(logdate)
               from yourtable
              where country = T.country )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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