order by clause not working

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

SQL

;with One as
(
    select top 100 percent 1 as t,t1.regId as a, t1.visitCount as b
     from registerdata as t1 order by t1.visitCount desc 
)
,Two as
(
    select top 100 percent 2 as t,t1.regId, t1.visitCount
    from registerdata as t1
)
select * from One
union
select * from Two order by t desc

this is my query and i want first query order by . it's run but result are shown wrong ..
please give any solution.

Posted 10-Jul-14 20:02pm

Yogesh_Chavan

Updated 10-Jul-14 20:57pm

Nirav Prabtani

v3

Add a Solution

Comments

Dinesh.V.Kumar 11-Jul-14 2:05am

Order BY clause should come outside as shown below

select * from (select top 100 percent 1 as t,t1.regId, t1.visitCount
from registerdata as t1
) as yy order by t1.visitCount desc
Try this and let me know if its working

Regards,
Dinesh Kumar.V.

Yogesh_Chavan 11-Jul-14 2:13am

The multi-part identifier "t1.visitCount" could not be bound

its show me error

4 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)

Sarvesh Kumar Gupta · Answer 1 · 2014-07-10T20:18:00

Solution 1

Hi,

Use below Query

SQL

select * from (select top 100 percent 1 as t,t1.regId, t1.visitCount
    from registerdata) as t1
   order by t1.visitCount desc

Posted 10-Jul-14 20:18pm

Sarvesh Kumar Gupta

Magic Wonder · Answer 2 · 2014-07-10T20:19:00

Solution 2

Hi,

I am not getting, why are you using sub-query? Your simple query will work and give the required output.

SQL

select top 100 percent 1 as t,t1.regId, t1.visitCount from registerdata as t1 order by t1.visitCount desc

Also check below query if you are using union...

now try this....just added desc with 1 in order by clause.

SQL

select top 100 percent 1 as t,t1.regId, t1.visitCount from registerdata as t1 
union
--here i am assuming next part of your query.
select top 100 percent 2 as t,t2.regId, t2.visitCount from table2 as t2 
order by  1 desc , 3 desc

Hope this will help you.

Posted 10-Jul-14 20:19pm

Magic Wonder

Updated 10-Jul-14 21:04pm

v3

Comments

Yogesh_Chavan 11-Jul-14 2:27am

i am also use a union and i want first query order by

Magic Wonder 11-Jul-14 2:34am

Kindly mention what are you trying exactly? If possible edit your question and provide your query.

Yogesh_Chavan 11-Jul-14 2:53am

it's not get me proer result...
i want show second query result on first after first query result shown in order by..

Magic Wonder 11-Jul-14 2:59am

Do you mean to say data of 2nd part of union should be displayed first and then data of first part of union?

am i correct?

Yogesh_Chavan 11-Jul-14 3:03am

yes right ...and first part of union data should be order by

Dhrisya P Nair · Answer 3 · 2014-07-10T20:21:00

Solution 3

Try this code....

SELECT * FROM (
select top 100 percent 1 as t,t1.regId, t1.visitCount
from registerdata as t1
) as yy ORDER BY yy.visitCount desc

Posted 10-Jul-14 20:21pm

Dhrisya P Nair

Yogesh_Chavan · Answer 4 · 2014-07-12T02:28:00

Solution 4

SQL

;with One as
(
    select top 100 percent 1 as t,t1.regId as a, t1.visitCount as b
     from registerdata as t1 order by t1.visitCount desc
)
,Two as
(
    select top 100 percent 2 as t,t1.regId, t1.visitCount
    from registerdata as t1
)
select * from One
union all
select * from Two order by t desc

Posted 12-Jul-14 2:28am

Yogesh_Chavan

Comments

Magic Wonder 14-Jul-14 1:35am

Did you solve your problem?