0

Is it possible to sort the results of a PagedTable by one of the columns aliases?

Here is my working query:

SELECT * FROM 
( 
SELECT 
DI.P_ID as IDEA_ID, 
DI.P_IDEA_NUMBER as IDEA_NUMBER, 
CASE WHEN len(DI.P_TITLE) < 1 then 'None recorded' else DI.P_TITLE END as IDEA_TITLE, 
CASE WHEN len(DI.P_IDEA_MANAGER) < 1 then 'None' ELSE isnull((Select top 1 FName+' 'LName from OTHERSERVER.OTHERDB.dbo.Usernames as UN1 where (XID=DI.P_IDEA_MANAGER) order by endDate desc),'No longer on record') end as IDEA_MANAGER, 
CASE WHEN len(DI.P_RELEASE_MONTH) < 1 or len(DI.P_RELEASE_YEAR) < 1 then 'None recorded' else DI.P_RELEASE_MONTH+'/'+DI.P_RELEASE_YEAR END as IDEA_REQUESTED_RELEASE, 
SIS.LABEL as IDEA_STATUS, 
ROW_NUMBER() OVER(Order by DI.P_IDEA_NUMBER ASC) as RowNum 
FROM 
DATA_IDEA as DI 
LEFT OUTER JOIN 
SEL_IDEA_STATUS as SIS on SIS.ID=(SELECT TOP 1 S_ID from DATA_IDEA_STATUS as DIS where (DIS.P_ID=DI.P_ID) order by created desc) 
WHERE 
(SIS.IS_DRAFT='True')
) 
as PagedTable where (RowNum between 1 and 10)

With this I am sorting the results by the "Idea Number", which is 1,2,3, etc. Instead of sorting by "DI.P_IDEA_NUMBER", is it possible to sort instead by "IDEA_NUMBER"?

You're probably thinking this isn't that big of a deal, sorting by the idea number. Well, I'm also going to have to have the results sortable by the "IDEA_MANAGER". IDEA_MANAGER will be one of three things, based on the case statemnt. 1) John Smith, 2) None Recorded, 3)No longer on record

user3560594
  • 13
  • 2
  • Are you trying to sort the final result set? If so, I would think that your only choice would be to order by the alias names as these will be the column names in the result set passed to the outer query from the inner query. Have you tried adding ORDER BY IDEA_NUMER or ORDER BY IDEA_MANAGER to the very end of your query. What is the result? – SPKoder Apr 25 '14 at 01:36
  • @SPKoder - No, sorting the final result set (10 records) is not what I want to do. The basic gist here is that the total results will be anywhere from 1 - 9,999,999. I want to be able to sort the total results, and then return the subset of 10 results from the paged table. – user3560594 Apr 25 '14 at 01:46

1 Answers1

0

"Is it possible to sort the results of a PagedTable by one of the columns aliases?"

Did you try it?

This should work:

SELECT Col1 AS MyAlias 
  FROM tbl 
 ORDER BY MyAlias

This should also work:

SELECT * 
  FROM (SELECT Col1 AS MyAlias FROM tbl) a
 ORDER BY MyAlias

But it may depend on you RDBMS.

Borrowing from How do i use alias in where clause?:

column_alias can be used in an ORDER BY clause, but it cannot be used in a WHERE, GROUP BY, or HAVING clause.

Taken from the MSSQL Doc

Also, you cannot use it an OVER clause.

cannot use alias in ROW_NUMBER() over in SQL Server?

Community
  • 1
  • 1
Karl Kieninger
  • 8,841
  • 2
  • 33
  • 49
  • "ROW_NUMBER() OVER(Order by DI.P_IDEA_NUMBER ASC) as RowNum" That is where the actual sorting of the data occurs. If I change "DI.P_IDEA_NUMBER" to the alias "IDEA_NUMBER" or "DI.IDEA_NUMBER" I get the error "Invalid column name: IDEA_NUMBER" – user3560594 Apr 25 '14 at 01:59
  • You cannot use an alias in the an OVER clause. http://stackoverflow.com/questions/7297609/cannot-use-alias-in-row-number-over-in-sql-server – Karl Kieninger Apr 25 '14 at 02:04
  • Thanks, I guess. That just means the past few hours spent coding were wasted. – user3560594 Apr 25 '14 at 02:21