A hard sql query for me

Databases
1

table one:


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.

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 
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

:cool:

3

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

yes, there is – split field2 up into two columns, and make them numeric

:slight_smile:

5

how to make them numeric? thank you.

ps:what’s the sql meaning?

6 
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 –

5  1-0
1  1-1
2  1-2
4  1-2
4  1-3
4  1-6
4  1-10 :)
3  1-5