rajanbharathi
asked on
How to select a column with order by for the same column
How to select a column with order by for the same column,
For eg :- If a table Employee having 4 columns Empno, Empname, Sal, DOJ and i want to get the top 50 employees based on the latest DOJ and first column as DOJ in my results.
if i use the below query
select top 50 DOJ ,* from Employee with (NOLOCK) order by DOJ desc
It throws Ambiguous column name.
For eg :- If a table Employee having 4 columns Empno, Empname, Sal, DOJ and i want to get the top 50 employees based on the latest DOJ and first column as DOJ in my results.
if i use the below query
select top 50 DOJ ,* from Employee with (NOLOCK) order by DOJ desc
It throws Ambiguous column name.
rajanbharathi,
The problem is that you selected top 50 doj and then * which included doj
jmoss111
The problem is that you selected top 50 doj and then * which included doj
jmoss111
ASKER
Hi Jim,
This is ok ,, if the table having more than 50 columns i cant apply this .. Please suggest for such case..
Thanks in Advance..
Bharathi
This is ok ,, if the table having more than 50 columns i cant apply this .. Please suggest for such case..
Thanks in Advance..
Bharathi
ASKER
Yes the problem is that , but i can use the same in SQL Server 2000.
I dont think that you understand; the query return the top 50 rows of doj and the other columns in the table.
use apstaging
select top 50 invoice_amount, invoice_number from aging order by invoice_amount desc
returns:
9999 5820009380
9998.3 63622
9998.1 A86018
9998.05 A86018
9997.29 2975
9997.27 2975
9997 91010406
9993.6 9401770153
99900 2008057838
9990 40817
9990 5363N
9990 5399N
999.98 0027234
999.9 080938636
999.9 100281
999.73 393/105492
999.68 009516
999.68 009517
999.6 38603
999.6 2160166
999.58 589766
999.39 147201
999.36 000065079
999.36 000064975
999.18 205218
999.12 21424
999.12 21424
999.05 5632S
999.02 5681S
999.02 5683S
999 5368N
999 190414
999 190415
999 5378N
999 5387N
999 5390N
999 5397N
999 5375N
999 5372N
999 E03771
999 5683S
999 5327N
999 5791S
999 08080137
999 5632S
999 5835S
999 5795S
999 5796S
999 5748S
999 5814S
use apstaging
select top 50 invoice_amount, * from aging order by invoice_amount desc
Msg 209, Level 16, State 1, Line 2
Ambiguous column name 'invoice_amount'.
select top 50 invoice_amount, * from aging order by invoice_amount desc
Msg 209, Level 16, State 1, Line 2
Ambiguous column name 'invoice_amount'.
ASKER
Yes this is what i am also facing.. comments please
I'm not facing the same problem; that was an example that I made for you.
I have given you the solution to your problem.
Your problem is that you selected top 50 of a column then used * which would return all columns but you already had doj in the top 50 so you got the ambiguous column name error.
Jim
I have given you the solution to your problem.
Your problem is that you selected top 50 of a column then used * which would return all columns but you already had doj in the top 50 so you got the ambiguous column name error.
Jim
ASKER
Yes you are right , But i want to display many columns not two or three in the select list.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Did that work out for you?
Jim
Jim
Did it not work very well, B?
select top 50 DOJ, Empno, Empname, Sal from Employee with (NOLOCK) order by DOJ desc
Regards,
Jim