ORDER BY - 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

ORDER BY

Author

Topic

tclose
Starting Member

24 Posts

Posted - 2010-04-28 : 02:27:48

I'm trying to get the results to group according to the first 2 numbers of the account. Any suggestions.

SELECT Account,Description,Product,Balance FROM Assets
WHERE Balance IS NOT NULL
UNION
SELECT Account,Description,Product,Balance FROM Liabilities
WHERE Balance IS NOT NULL
UNION
SELECT Account,Description,Product,Balance FROM Expenses
WHERE Balance IS NOT NULL

ORDER BY Account ON LEFT(Account,2) <-----error

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2010-04-28 : 02:37:58

instead of ORDER BY Account ON LEFT(Account,2) <-----error

use only

ORDER BY LEFT(Account,2)

Karthik
http://karthik4identity.blogspot.com/

tclose
Starting Member

24 Posts

Posted - 2010-04-28 : 02:54:52

quote:
Originally posted by karthik_padbanaban

instead of ORDER BY Account ON LEFT(Account,2) <-----error

use only

ORDER BY LEFT(Account,2)

Karthik
http://karthik4identity.blogspot.com/

Thanks for responding. When I do that I get the following error:
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2010-04-28 : 03:05:07

quote:
Originally posted by tclose

quote:
Originally posted by karthik_padbanaban

instead of ORDER BY Account ON LEFT(Account,2) <-----error

use only

ORDER BY LEFT(Account,2)

Karthik
http://karthik4identity.blogspot.com/

Thanks for responding. When I do that I get the following error:
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

--- dont use first column

SELECT left(Account,2),Account,Description,Product,Balance FROM Assets
WHERE Balance IS NOT NULL
UNION
SELECT left(Account,2),Account,Description,Product,Balance FROM Liabilities
WHERE Balance IS NOT NULL
UNION
SELECT left(Account,2),Account,Description,Product,Balance FROM Expenses
WHERE Balance IS NOT NULL
ORDER BY left(Account,2)

Karthik
http://karthik4identity.blogspot.com/

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2010-04-28 : 03:07:45

or you can use in this way

select Account,Description,Product,Balance FROM
(SELECT Account,Description,Product,Balance FROM Assets
WHERE Balance IS NOT NULL
UNION
SELECT Account,Description,Product,Balance FROM Liabilities
WHERE Balance IS NOT NULL
UNION
SELECT Account,Description,Product,Balance FROM Expenses
WHERE Balance IS NOT NULL)as a
ORDER BY left(a.Account,2)

Karthik
http://karthik4identity.blogspot.com/

tclose
Starting Member

24 Posts

Posted - 2010-04-28 : 03:13:10

quote:
Originally posted by karthik_padbanaban

or you can use in this way

select Account,Description,Product,Balance FROM
(SELECT Account,Description,Product,Balance FROM Assets
WHERE Balance IS NOT NULL
UNION
SELECT Account,Description,Product,Balance FROM Liabilities
WHERE Balance IS NOT NULL
UNION
SELECT Account,Description,Product,Balance FROM Expenses
WHERE Balance IS NOT NULL)as a
ORDER BY left(a.Account,2)

Karthik
http://karthik4identity.blogspot.com/

Thank you very much - that works great.

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2010-04-28 : 03:15:57

Welcome.

Karthik
http://karthik4identity.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-28 : 09:53:58

You can use it like this also -


SELECT left(Account,2),Account,Description,Product,Balance FROM Assets
WHERE Balance IS NOT NULL 
UNION
SELECT left(Account,2),Account,Description,Product,Balance FROM Liabilities
WHERE Balance IS NOT NULL
UNION
SELECT left(Account,2),Account,Description,Product,Balance FROM Expenses
WHERE Balance IS NOT NULL
ORDER BY 1

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources