Ordering - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Ordering

Author

Topic

david522
Starting Member

2 Posts

Posted - 2012-05-23 : 04:20:43

Ok so this was fairly easy to me except part of the requirements is that we have a $ symbol before salaries (sal = salaries). Unfortunately when I go to order the numbers, it all works out except for the top number which obviously should be at the bottom.

SELECT DISTINCT a.ename "Supervisor_Name",CONCAT ('$', b.sal) AS Highest_Salary
FROM emp a, emp b 
WHERE a.empno=b.super AND  b.sal = (SELECT max(sal) FROM emp where super in (a.empno)) 
order by Highest_Salary desc;

Any ideas?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-05-23 : 04:44:32

order by b.sal desc

btw. these are MS SQL Server forums only.
Do you use MySQL?
Then better ask here: http://www.dbforums.com/mysql/

No, you're never too old to Yak'n'Roll if you're too young to die.

david522
Starting Member

2 Posts

Posted - 2012-05-23 : 04:54:41

I have no idea sorry, all I could find in the course description was "With this foundation, we
then develop and query databases using SQL in Oracle 11g/MS-Access."

However what you posted doesn't work as this error is returned is "not a SELECTed statement"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-23 : 15:41:51

quote:
Originally posted by david522

I have no idea sorry, all I could find in the course description was "With this foundation, we
then develop and query databases using SQL in Oracle 11g/MS-Access."

However what you posted doesn't work as this error is returned is "not a SELECTed statement"

You're in wrong forum then

please post in some Oracle forums like www.orafaq.com

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-05-30 : 03:52:23

1 Do formatting at front end application
2 or use this
order by replace(Highest_Salary,'$','')*1 desc;

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-05-30 : 03:53:07

quote:
Originally posted by webfred

order by b.sal desc

btw. these are MS SQL Server forums only.
Do you use MySQL?
Then better ask here: http://www.dbforums.com/mysql/

No, you're never too old to Yak'n'Roll if you're too young to die.

Note that direct usage of alias name will work in ORDER BY clause

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-05-30 : 05:07:37

quote:
Originally posted by madhivanan

quote:
Originally posted by webfred

order by b.sal desc

btw. these are MS SQL Server forums only.
Do you use MySQL?
Then better ask here: http://www.dbforums.com/mysql/

No, you're never too old to Yak'n'Roll if you're too young to die.

Note that direct usage of alias name will work in ORDER BY clause

Madhivanan

Failing to plan is Planning to fail

Note that the alias column in that case is character hence my solution to order by b.sal because it might be numeric.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources