Union of 2 TOP statements, one ordered DESC, one ASC

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

SQL Server Development (2000)

Union of 2 TOP statements, one ordered DESC, one ASC

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-03-16 : 08:30:12

Simon writes "Hi everyone and thanks for this great ressource.
I am trying to retrieve both the top 40 long and top 40 short positions in my database.

Ideally I would like to use something like this:

(SELECT TOP 40 'Long' as isLong,Position
FROM tblCategorizedInventory
WHERE position>0
ORDER BY Position DESC)
UNION
(SELECT TOP 40 'Short' as isLong, Position
FROM tblCategorizedInventory
WHERE position<0
ORDER BY Position ASC)

Which is not valid sql syntax.

The trick is, each top has to be in a different order : biggest long positions are at the top when DESC ordered, biggest short positions (biggets negative numbers in absolute value) are at the top when ordered ASC.

How would I go about doing this?

Thanks

SQL server 8.00.94
Windows XP SP2"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-16 : 08:39:18

select * from (your first top 40 DESC) a
union all
select * from (your second top 40 ASC) b

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources