Incorrect syntax near the keyword 'asc by Order by in sql server 2008

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008

Hi all visitors
In my query,I select '5AM-7AM' as 'Hour',sum(field) as value
and it shows 5 rows like this
Hour_____________value
5AM-7AM__________409
9AM-10AM__________545
8AM-9AM__________885
10AM-11AM_________7353
7AM-8AM__________788

How can i sort by using order by like this?

Hour_____________value
5AM-7AM__________409
7AM-8AM__________788
8AM-9AM__________885
9AM-10AM__________545
10AM-11AM_________7353

I also use like this order by (LEFT(T0.Hour,CHARINDEX('-', T0.Hour-1)) asc
but it shows me the error "Incorrect syntax near the keyword 'asc'"
Please help me for this.
Best Regards,

Posted 5-Apr-11 4:43am

soeun tony

Add a Solution

Comments

wizardzz 5-Apr-11 12:40pm

Essentially a repost of this question:
http://www.codeproject.com/Questions/177056/Show-hours-in-sql-server.aspx

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Henry Minute · Answer 1 · 2011-04-05T04:48:00

Solution 1

If your code is correctly pasted/entered you have a mismatch in the Brace count. 3 '(' and only 2 ')'

Posted 5-Apr-11 4:48am

Henry Minute

Wendelius · Answer 2 · 2011-04-05T05:03:00

Solution 2

You didn't mention where you get the hour ranges from. If you have the actual time information you could use that for sorting. If you don't have it, perhaps you can generate the necessary ranges (and join the the relevant information for sum calculation etc). For example something like this could be used to generate the time ranges:

SQL

select *
from (select CAST('05:00' AS TIME) AS StartTime, CAST('07:00' AS TIME) AS EndTime UNION ALL
      select CAST('08:00' AS TIME) AS StartTime, CAST('09:00' AS TIME) AS EndTime UNION ALL
      select CAST('09:00' AS TIME) AS StartTime, CAST('10:00' AS TIME) AS EndTime UNION ALL
      select CAST('10:00' AS TIME) AS StartTime, CAST('11:00' AS TIME) AS EndTime) times
order by times.StartTime

Posted 5-Apr-11 5:03am

Wendelius

Comments

soeun tony 5-Apr-11 11:37am

Thanks u so much for ur code.
This is what i need.
Best Regards

Wendelius 5-Apr-11 12:01pm

No problem. If you like, you can mark the answer as accepted.

wizardzz 5-Apr-11 12:38pm

Mika, he was asking originally here:
http://www.codeproject.com/Questions/177056/Show-hours-in-sql-server.aspx
and my answer works correctly for not having to hard code hourly ranges. He is using my said code in this question, but does not understand how to sort the results.

Wendelius 5-Apr-11 14:22pm

Ok, didn't notice that. And as I wrote, the OP didn't mention where the tiems are coming from so I wanted to guide to use the actual times (if they exist) or give an alternative approach :)

wizardzz 5-Apr-11 14:24pm

Oh, no problem at all, great job answering it!

wizardzz 5-Apr-11 12:42pm

Still a 5 for the simple solution that appears to help the OP.

Wendelius 5-Apr-11 14:22pm

Thank you :)