Order by Query

Question

Order by Query

dva2007

SSCertifiable

Points: 7707
More actions
June 26, 2008 at 5:50 am

#381375

Hi friends,
i have column which have data like below.
6 hours
6 hours 30 minutes
''
''
''
14 hours
14 hours 30 minutes
now when i use order by it gives result like
10 hours
10 hours 30 minutes
''
''
14 hours
''
6 hours
i want it proper sorted. like 6,7,8 till 14.
ANy suggestion please?
thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 1

One of the ways is to update column to have leading 0 before single digit hours. The other way is to store these values as number of minutes (smallint) and order by that field. so instead of 6 hours 30 minutes you would have 390

Piotr

...and your only reply is slàinte mhath

Mark Beringer-730383 SSCommitted Points: 1824 More actions · Answer 2

Yeah my idea to was to add a leading zero, that would sort it perfect!

schuppe SSC Enthusiast Points: 113 More actions · Answer 3

create table source (timecol varchar(20))

insert into source values('6 hours 00 minutes');

insert into source values('6 hours 30 minutes');

insert into source values('7 hours 00 minutes');

insert into source values('7 hours 30 minutes');

insert into source values('10 hours 00 minutes');

insert into source values('10 hours 30 minutes');

insert into source values('11 hours 00 minutes');

insert into source values('11 hours 30 minutes');

select * from source order by case when len(timecol)<19 then '0'+timecol else timecol end

drop table source

Hari.Sharma SSCertifiable Points: 6904 More actions · Answer 4

HI,

try this query:

select * from source

order by Cast(Left(timecol,2) as int)

Cheers,
Hari
Tips & Tricks for SQL BI Developers

Steven Cameron SSCrazy Points: 2903 More actions · Answer 5

Hari.Sharma (6/26/2008)
select * from source
order by Cast(Left(timecol,2) as int)

If you add the full timecol to this then it would sort the "6 hours" and "6 hours 30 minutes" properly.

SELECT *

FROM source

ORDER BY CAST(LEFT(timecol, 2) AS Int), timecol

Hari.Sharma SSCertifiable Points: 6904 More actions · Answer 6

Yep...

Thats true.

🙂

Cheers,
Hari
Tips & Tricks for SQL BI Developers

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

Heh... that's what happens when you store dates or times as formatted data. These should be stored just as a DateTime data type and formatted for display only. Then, you wouldn't have these types of problems that will make the code horribly slow and unable to use indexes properly... best you'll ever get out of something like this is an index scan. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)