sorting query in sql

Question

I have a table which have field name ID. My data showing like below

LE001001
LE0010010
LE001003
LE001005
LE001006
LE001007
LE001008
LE001009

But I want to show my data by sorting like below

LE001001
LE001003
LE001005
LE001006
LE001007
LE001008
LE001009
LE0010010

Please help me to crate query

Where would you place LE001010? Do you consider an ID to have three parts; two letters, a five digit number and a number of unknown digits? — Thorsten Kettner, Jan 05 '15 at 13:21

score 0 · Accepted Answer · answered Jan 05 '15 at 13:08

0

You want to sort by the number after 'LE'. Assuming the prefix is always the same length, you can use a trick where you order by the length and then the value:

order by len(col), col

This works for the sample data in the question. You can do explicit conversions for this:

order by left(col, 2),
         cast(ltrim(rtrim(substring(col, 3, len(col)))) as int)

But the first method is easier to type if it works.

answered Jan 05 '15 at 13:08

Gordon Linoff

1,242,037
58
646
786

Another anonymous downvote on a correct answer. – Gordon Linoff Jan 05 '15 at 13:43
9

I wasn't the one who downvoted you, and your answer could be correct, I don't really know. But maybe a 250k user should be closing obvious duplicates rather than answering them. – Madara's Ghost Jan 05 '15 at 13:48
@SecondRikudo . . . First, it didn't only affect me. It appeared to affect every answer on the question. Second, I have a different (albeit quite a minority opinion) on what constitutes a duplicate. – Gordon Linoff Jan 05 '15 at 13:56
7

That is your right. Just don't be surprised when you get downvoted by the majority. I find your answer not helpful in the larger sense that now someone from google looking for "sorting query in sql server" will find your specific answer that won't help him at all. – Madara's Ghost Jan 05 '15 at 13:58
@SecondRikudo . . . If such downvotes are going to be effective, they should include a comment explaining why. Wantonly downvoting answers basically communicate no information. Anonymous downvotes on *correct* answers is downright misleading. – Gordon Linoff Jan 05 '15 at 14:09
7

Your definition of effective is different than mine. I use downvotes as an effective tool to make not useful questions and answers disappear with low view rate. I don't need to post a comment for that. And even as you got an explanation on why your answer was downvoted, you engaged in argument and caused me to write two more comments, effectively wasting my productive time. So you shouldn't be surprised why people wouldn't want to comment on your answers when they downvote you. – Madara's Ghost Jan 05 '15 at 14:21
@SecondRikudo . . . You should then remove the comment that says that you didn't downvote me. – Gordon Linoff Jan 05 '15 at 14:22
4

@GordonLinoff It's rare that anyone would ever leave a comment as to why they downvote a post, it just leads to arguments. – Taryn Jan 05 '15 at 14:39
@GordonLinoff I have one more question can you please help me? – James Walker Jan 05 '15 at 16:55
@GordonLinoff I get every day a down Vote on a 0 accepted answer since 3 days, so just don't worry about these -1 :D It all will become good on one or the other day – Rizier123 Jan 14 '15 at 11:43

score 0 · Answer 2 · answered Jan 05 '15 at 13:10

0

order by substr(colum_name, 8, 2)

answered Jan 05 '15 at 13:10

starko

1,150
11
26

why it is 8? can you justify your answer? i couldn't understand – Kalaiarasan Manimaran Jan 05 '15 at 13:13
character after the sorting is in 8th place on the left side – starko Jan 05 '15 at 13:21
@i486: The question isn't about MySQL either. – Thorsten Kettner Jan 05 '15 at 13:33
1

@starko: I think that '9' comes after '10' still. You will have to cast the digits you get from string to number. – Thorsten Kettner Jan 05 '15 at 13:34
That's right. my fault. Thanks for you answer – starko Jan 05 '15 at 13:41
Yes, the question is for MS SQL. BUT `substr` is function in MySQL, not in MS SQL. That was my comment. – i486 Jan 05 '15 at 13:46
@i486: Okay, sorry. So you meant this should not be `substr` as in MySQL or Oracle, but `substring` instead. You are right. – Thorsten Kettner Jan 05 '15 at 13:48
In addition, in MySQL you can simply add `+0` to convert string to integer (and sort it accordingly). In MS SQL there must be additional CAST/CONVERT. Otherwise the result after `ORDER BY SUBSTRING(...` will be the same as original query. – i486 Jan 05 '15 at 13:59
Describe more for others that read this answer.. – Mohamad Shiralizadeh Jan 05 '15 at 15:11

score -1 · Answer 3 · answered Jan 05 '15 at 13:16

-1

You can define 2 columns. One for number 1001, 10010 and the other - formula which will add the number to string LE. Then you sort the first column as integer number and use the compound column LE001001 for other needs.

answered Jan 05 '15 at 13:16

i486

6,491
4
24
41

sorting query in sql

3 Answers3