Solved

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

Posted on 2008-10-20
12
189 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
Comment Utility
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
Comment Utility
rajanbharathi,

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


jmoss111
0
 

Author Comment

by:rajanbharathi
Comment Utility
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
 

Author Comment

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

Expert Comment

by:jmoss111
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

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

Expert Comment

by:jmoss111
Comment Utility
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
Comment Utility
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
Comment Utility
select top 50 DOJ AS TOP50DOJ,* from Employee with (NOLOCK) order by DOJ desc will work.

0
 
LVL 18

Expert Comment

by:jmoss111
Comment Utility
Did that work out for you?

Jim
0
 
LVL 18

Expert Comment

by:jmoss111
Comment Utility
Did it not work very well, B?
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now