SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Evangelist runeveryday's Avatar
    Join Date
    Jul 2009
    Posts
    437
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    a hard sql query for me

    table one:
    Code:
    field one     field two
    1               1-1
    2               1-2
    3               1-5
    4               1-3
    4               1-6
    4              1-2
    5              1-0
    using the sql command to get the results as the following table.

    Code:
    field one     field two
    5               1-0
    1               1-1
    2               1-2
    4               1-2
    4               1-3
    4               1-6
    3               1-5
    how to write the query?
    ps: the condition is selecting field two order by asc . but if the result have the same field one.then put the field two together. and sorting them.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT t.field1
         , t.field2
      FROM ( SELECT field1
                  , MIN(field2) AS min2
               FROM table1
             GROUP
                 BY field1 ) AS m
    INNER
      JOIN table1 AS t
        ON t.field1 = m.field1
    ORDER
        BY m.min2
         , t.field2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist runeveryday's Avatar
    Join Date
    Jul 2009
    Posts
    437
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    many many thanks.the query works like a charm. but i don't understand it well.

    ps:if i add a record 1-10 to field 2. the sort may be as this 1-10 1-2.

    is there a way to correct this?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by runeveryday View Post
    is there a way to correct this?
    yes, there is -- split field2 up into two columns, and make them numeric

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

  5. #5
    SitePoint Evangelist runeveryday's Avatar
    Join Date
    Jul 2009
    Posts
    437
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    how to make them numeric? thank you.

    ps:what's the sql meaning?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    INSERT INTO table1           -- old table
    VALUES (  4 , '1-10' )
    ;
    CREATE TABLE new_table1
    ( field1 INTEGER NOT NULL 
    , field2_a INTEGER
    , field2_b INTEGER 
    );
    INSERT INTO new_table1
    SELECT field1
         , SUBSTRING_INDEX(field2,'-',1)
         , SUBSTRING_INDEX(field2,'-',-1)
      FROM table1
    ;
    SELECT t.field1
         , CONCAT(t.field2_a,'-',t.field2_b)
      FROM ( SELECT field1
                  , MIN(field2_a) AS min2a
               FROM new_table1
             GROUP
                 BY field1 ) AS m2a
    INNER
      JOIN ( SELECT field1
                  , field2_a
                  , MIN(field2_b) AS min2b
               FROM new_table1
             GROUP
                 BY field1
                  , field2_a ) AS m2b
        ON m2b.field1 = m2a.field1
       AND m2b.field2_a = m2a.min2a
    INNER
      JOIN new_table1 AS t
        ON t.field1 = m2a.field1
    ORDER
        BY m2a.min2a
         , m2b.min2b
         , t.field2_b
    results --
    Code:
    5  1-0
    1  1-1
    2  1-2
    4  1-2
    4  1-3
    4  1-6
    4  1-10 :)
    3  1-5
    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
  •