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

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?

order by clause not working

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0

order by clause not working

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us