how to write the sql command?

table one:

fie开发者_JAVA技巧ld 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

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 asc them.

With regards to your second question (actually sort properly as well) it gets a tad bit more complex. This version works on Sql-Server (don't know what you are using and the functions may differ)

select tableOne.fieldOne, tableOne.fieldTwo
from tableOne
inner join 
   (select 
      fieldOne, 
      MIN(Convert(int, LEFT(tableOne.FieldTwo, CHARINDEX('-', tableOne.fieldTwo)-1))) as LeftPartMin,
      MIN(Convert(int, SubString(tableOne.FieldTwo, CHARINDEX('-', tableOne.fieldTwo)+1, 100))) as RightPartMin
    from tableOne group by fieldOne
   ) b
on (b.fieldOne = tableOne.fieldOne)
order by b.LeftPartMin, b.RightPartMin, fieldOne, Convert(int, LEFT(tableOne.FieldTwo, CHARINDEX('-', tableOne.fieldTwo)-1)), Convert(int, SubString(tableOne.FieldTwo, CHARINDEX('-', tableOne.fieldTwo)+1, 100))

You can test this by adding the following 2 records at the end in your table: 4 1-20 2 1-10

This also revealed an error in the first solution: you need to sort one fieldOne as well! (as the second sort argument) to make sure groups with the same fieldOne end up together (edited other answer to correct this)

Haven't checked the syntax but something along these lines should do it:

select tableOne.fieldOne, tableOne.fieldTwo, b.SortField from tableOne
inner join 
(select fieldOne, min(fieldTwo) as SortField from tableOne group by fieldOne) b
on (b.fieldOne = tableOne.fieldOne)
order by b.SortField, tableOne.fieldOne, tableOne.fieldTwo

In essence: for each fieldOne find the lowest value in the group and use that as primary sortfield for all records in the group. Within the group sort on the actual value in fieldtwo

There are still potential issues left though: 1-10 will sort between 1-1 and 1-2 If you also want to resolve that you need to split up fieldtwo and convert to ints.

In all cases you might want to rethink if this datamodel is really setup the way you want/need