SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    distinct country

    Code:
    data in myTable1
    
    (1) America
    (2) Korea
    (3) France
    (4) German
    (5) China
    (6) England
    (7) Canada
    
    data in myTable2
    
    (1) Boston
    (1) New York
    (2) Seoul
    (3) Paris
    (4) Berlin
    (6) London
    (7) Montreal
    I have data in myTables like the above.

    The following code produces the following result.
    Code:
    code
    
    select myTable1.id, city, country
    from myTable1
    left join myTable2 on myTable2.id=myTable1.id
    where myTable2.id is not null
     
    order by myTable1.id
    
    result
    
    (1) Boston     America
    (1) New York   America
    (2) Seoul      Korea
    (3) Paris      France
    (4) Berlin     German
    (6) London     England
    (7) Montreal   Canada
    Becuase America has two cities, it produces america two times.
    I like to produce America just one time, and its First city in Alphabetical order (Boston is before New York in Alphabetical order.)

    The following would-be code doesn't work correctly, but it'll show what I want.
    Code:
    would-be code
    
    select myTable1.id, 
    distinct city(limit 1 order by city), country
    from myTable1
    left join myTable2 on myTable2.id=myTable1.id
    where myTable2.id is not null
     
    order by myTable1.id
    
    
    target result
    
    (1) Boston     America
    (2) Seoul      Korea
    (3) Paris      France
    (4) Berlin     German
    (6) London     England
    (7) Montreal   Canada

  2. #2
    masquerading Nick's Avatar
    Join Date
    Jun 2003
    Location
    East Coast
    Posts
    2,215
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So you just want one occurrence of the first American city? Look into using SELECT DISTINCT(col) and use ORDER BY to get the top alphabetical result.
    Nick . all that we see or seem, is but a dream within a dream
    Show someone you care, send them a virtual flower.
    Good deals on men's watches

  3. #3
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Possibility View Post
    So you just want one occurrence of the first American city? Look into using SELECT DISTINCT(col) and use ORDER BY to get the top alphabetical result.
    The code below doesn't work.
    Code:
    distinct city(limit 1 order by city)
    How can I make it work?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Possibility, i should like to point out that DISTINCT is not a function

    and joon wanted one city per country -- ORDER BY to get the top result will return only one result, the top one for all countries

    joon, you've got your table 2 and table 1 mixed up

    why have you forgotten the MIN() function?
    Code:
    SELECT MIN(myTable2.name) AS city
         , myTable1.name AS country
      FROM myTable1
    INNER
      JOIN myTable2
        ON myTable2.id = myTable1.id
    GROUP
        BY myTable1.name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Possibility, i should like to point out that DISTINCT is not a function

    and joon wanted one city per country -- ORDER BY to get the top result will return only one result, the top one for all countries

    joon, you've got your table 2 and table 1 mixed up

    why have you forgotten the MIN() function?
    Code:
    SELECT MIN(myTable2.name) AS city
         , myTable1.name AS country
      FROM myTable1
    INNER
      JOIN myTable2
        ON myTable2.id = myTable1.id
    GROUP
        BY myTable1.name
    Thank you. It works fine.


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
  •