how to write the sql command?

Question

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

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.

It is really not clear what your desired SQL command needs to return. "Get the results as this order"; is this what you have accomplished so far, or is it what you wish to accomplish? — Steven Ryssaert
– Steven Ryssaert, Commented Jul 15, 2011 at 8:21
group by field one and order by field two?doesn't that work?Or vice versa? — Bastardo
– Bastardo, Commented Jul 15, 2011 at 8:24
i am sorry, i have updated the question. the table 2 is what i wish to accomplish. — zhuanzhou
– zhuanzhou, Commented Jul 15, 2011 at 8:24
JohnnyCageWins ,but i don't know how to order fields tow, there is a hyphen in it — zhuanzhou
– zhuanzhou, Commented Jul 15, 2011 at 8:26
field two in the second table is not ordered numerically ... 1-5 is last, should 1-6 be last ? — NimChimpsky
– NimChimpsky, Commented Jul 15, 2011 at 8:27

Eddy · Accepted Answer · 2011-07-15 10:16:55Z

2

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)

answered Jul 15, 2011 at 10:16

Eddy

5,38026 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

zhuanzhou Over a year ago

Eddy,you're my hero! you datebase skill is gorgeous.up to now,i am not 100% following the first solution very well. ): i don't know why using inner join.in my memory, i always using it in two tables.

Eddy · Accepted Answer · 2011-07-15 10:17:46Z

1

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

edited Jul 15, 2011 at 10:17

answered Jul 15, 2011 at 8:33

Eddy

5,38026 silver badges41 bronze badges

8 Comments

Jacob Over a year ago

b.SortField is min(fieldtwo) from the subquery

Eddy Over a year ago

It's a temp value that comes from the subselect (aliased as b). You need it to determine the sorting order. I noticed btw that I missed the from statement for both the select and subselect. I'll update them.

Paolo Falabella Over a year ago

+1 this works (it's only missing a couple of FROM tableOne in the two SELECTs)

zhuanzhou Over a year ago

paolo ,i have tested the command.it's ok. where missed FROM tableOne? thank you

zhuanzhou Over a year ago

Eddy,you're my hero, if i want to resolve the potential issues : 1-10 will sort between 1-1 and 1-2, how to do it? and how to convert the recored to ints. many many thanks

|

Collectives™ on Stack Overflow

how to write the sql command?

2 Answers 2

1 Comment

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related