0

how can i return these in this order:

1: aaaa
2: bbbb
3: the cccc
4: dddd

So ignoreing the leading 'the'

currently im using. 

select * from houses order by name asc

and its returning it in this order.

1: aaaa
2: bbbb
3: dddd
4: the cccc

Thanks

Steve Taylor
  • 301
  • 3
  • 14

3 Answers3

2

A more correct, but slower answer is:

ORDER BY CASE WHEN SUBSTR(name, 1, 4) = 'the ' THEN SUBSTR(name, 5) ELSE name END;
steffen
  • 16,138
  • 4
  • 42
  • 81
1

If you want to remove the the for the order by then you can use:

select id, name
from yourtable
order by replace(name, 'the ', '')

See SQL Fiddle with Demo

Or:

select id, name
from yourtable
order by ltrim(replace(name, 'the', ''))

See SQL Fiddle with Demo

Or even use this following:

select id, name
from yourtable
order by 
  case 
    when substring(name, 1, 4) = 'the ' 
    then substring(name, 5) 
    else name end

See SQL Fiddle with Demo

Taryn
  • 242,637
  • 56
  • 362
  • 405
  • wow man thanks buddy ! i also got something from this answer! salute :) – Muhammad Talha Akbar Dec 14 '12 at 10:37
  • woah woah man if you have added id then there is no use of adding those functions! and if there are huge records then? they will not be in order instead gave him idea of adding unique id to each name and then echo them in order! – Muhammad Talha Akbar Dec 14 '12 at 10:47
  • 1
    @AspiringAqib that only works if the id is in the same order as the ordering -- see this demo -- http://sqlfiddle.com/#!2/72e3b/2 – Taryn Dec 14 '12 at 10:48
  • 2
    This deletes all 'the' from the ordering string and thus results in wrong order in cases like (atheb, ac) -> see my answer. – steffen Dec 14 '12 at 10:52
  • yeah i know @bluefeet. Forget Everything and take a head start. he makes a table. here if he add column of id (auto-incremented) and name only! So, when the records will be inserted they will be in order and he can echo them in order by using **id**. think bigger to get bigger and many respect and salute to you! You have helped more than 1000 people! i admit that i don't know the above functions but if i had to echo them in order i only prefer the id based structures! – Muhammad Talha Akbar Dec 14 '12 at 10:52
  • @AspiringAqib if i had wanted to order by the id i would have done and not by the name man. – Steve Taylor Dec 14 '12 at 10:53
  • man why you are inserting names in table ? for what purpose? authentication e.t.c ? – Muhammad Talha Akbar Dec 14 '12 at 10:55
  • The idea is to list all house names in alphabetical order from the database ignoring the leading 'the ' So making "The old house" appear with the o's and not the t's. As when people look for a house name they dont always know that it has a leading 'the '. The 2nd example by bluefeet did exactly what i needed it too. – Steve Taylor Dec 14 '12 at 11:03
  • So the final result is a copy of my answer? Cool. – steffen Dec 14 '12 at 12:04
-1

Hey Man Its ascending order means a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z. As d comes before t so, thats why it is echoing it first! ORDER BY only checks the first letter of string and then order them. if you want them to order like:

1: aaaaa

2: bbbbb

3: the ccccc

4: ddddd

then make one more column of id with integer type, values according to your taste and also add Auto Increment Attribute to it and then order them by ascending order!

Muhammad Talha Akbar
  • 9,952
  • 6
  • 38
  • 62
  • Im aware of how ordering works. I asked "how can i return these in this order". If its not possible within mysql then thats fair enough. However there are sites out there that format there data returned from a database ignoring the leading 'a ' or 'the '. – Steve Taylor Dec 14 '12 at 10:37
  • no man they have id-based structure! take example of facebook, they order their records by id and when they are echoed they are in order! open the sqlfiddle of @bluefeet and run this query in right pane window and you will see it ignore all the name instead order them by id they have if `the cccccc` is at id 2 it will be displayed first than `bbbbbbb`. **this is the query** : `select id, name from yourtable order by id ASC` – Muhammad Talha Akbar Dec 14 '12 at 10:45