Solved

How to select a column with order by for the same column

Posted on 2008-10-20
12
208 Views
Last Modified: 2010-03-19
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.
0
Comment
Question by:rajanbharathi
  • 8
  • 4
12 Comments
 
LVL 18

Expert Comment

by:jmoss111
ID: 22764428
Hello rajanbharathi,

select top 50 DOJ, Empno, Empname, Sal from Employee with (NOLOCK) order by DOJ desc


Regards,

Jim
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22764432
rajanbharathi,

The problem is that you selected top 50 doj and then * which included doj


jmoss111
0
 

Author Comment

by:rajanbharathi
ID: 22764438
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
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:rajanbharathi
ID: 22764444
Yes the problem is that , but i can use the same in SQL Server 2000.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22764453
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

Open in new window

0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22764470
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'.
0
 

Author Comment

by:rajanbharathi
ID: 22764480
Yes this is what i am also facing.. comments please
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22764492
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
0
 

Author Comment

by:rajanbharathi
ID: 22764507
Yes you  are right , But i want to display many columns not two or three in the select list.
0
 
LVL 18

Accepted Solution

by:
jmoss111 earned 500 total points
ID: 22764516
select top 50 DOJ AS TOP50DOJ,* from Employee with (NOLOCK) order by DOJ desc will work.

0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22764582
Did that work out for you?

Jim
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22766793
Did it not work very well, B?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS SQL page split per second is high 19 97
Order by but want it in specific order 2 33
Table create permissions on SQL Server 2005 9 41
convert null in sql server 12 34
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question