SQL Statement - 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

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Statement

Author

Topic

ckuo@kahluadesigns.com
Yak Posting Veteran

58 Posts

Posted - 2002-05-28 : 13:13:36

Hi,
I have the following data

ID  Status  Name
1    1        Microsoft
1    1        Xerox
1    1        McDonalds
1    2        Amtrak
1    2        Adobe
2    7        Barnes and Nobles
and so on..

I would like to view this like this:

ID  Status  Name
1    1        Microsoft
1    1        McDonalds
1    1        Xerox
1    2        Adobe
1    2        Amtrak

So its grouped by ID, within all the records where the ID is 1, the StatusID's are ordered ASC or DESC and within each group of the StatusID, the Name is alphabetical. How can I do this in one sql statment? or perhaps the best way of doing this?

Thanks

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-28 : 13:21:41

select *
from table
where id = 1
order by status desc , name desc

where I have desc written you can change it to asc

ckuo@kahluadesigns.com
Yak Posting Veteran

58 Posts

Posted - 2002-05-28 : 13:45:02

I tried your suggestion, but I get

"Order by clause may not be used in this query type". Here is the actual sql statment.

SELECT Deals.*
FROM Deals INNER JOIN
Tenants ON Deals.TenantID = Tenants.TenantID
WHERE Deals.BuidlingID = 1
ORDER BY rankID ASC, Tenants.Name DESC

Can I use order by when I have a WHERE = 1??

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-05-28 : 15:05:51

If you are using a view see the following article:

http://www.microsoft.com/sql/using/tips/development/ORDERBY.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources