CODEWITHSUNDEEP
sqlsql-serversql-server-2008
marifrahman
marifrahman

Reputation: 690

Running Order by on column alias

I am trying to run following SQL query on northwind database :

SELECT * FROM (
    SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY Joinning DESC) rownum,
    LastName, Country, HireDate AS Joinning
   FROM  Employees
   WHERE  Region IS NOT NULL
   ) r

It's giving me the error :

Invalid column name 'Joinning'.

The 'rownumber' is required for pagination.

Can anybody please suggest how I can sort on the Joining alias with the rownumber generated ?

--A possible work around

Just figured out a work around; please suggest if anything is wrong or need changes :

SELECT ROW_NUMBER() OVER (ORDER BY Joinning DESC) rownum,* FROM (
    SELECT  
    LastName, Country, HireDate AS Joinning

   FROM  Employees
   WHERE  Region IS NOT NULL
   ) r

--To put further where clause on row number(what I wanted to do for pagination):

With myres as(
   SELECT ROW_NUMBER() OVER (ORDER BY Joinning DESC) rownum,* FROM (
    SELECT  
       LastName, Country, HireDate AS Joinning
       FROM  Employees
       WHERE  Region IS NOT NULL
     ) a
) Select * from myres where myres.rownum > 0 and myres.rownum < = 0+20

Upvotes: 2

Views: 457

Answers (3)

Kirk
Kirk

Reputation: 5077

Use Original Name of the field, That will work just fine HireDate

SELECT * FROM (
    SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY HireDate DESC) rownum,
    LastName, Country, HireDate AS Joinning
   FROM  Employees
   WHERE  Region IS NOT NULL
   ) r

Upvotes: 0

user3222297
user3222297

Reputation: 206

Hope you ahve joinning in your table. The order by clause is usually given at the last of the query like this :

SELECT * FROM ( SELECT DISTINCT ROW_NUMBER() rownum, LastName, Country, HireDate AS Joinning) FROM Employees WHERE Region IS NOT NULL ORDER BY Joinning DESC)

Hope this helps you!

Upvotes: 0

MusicLovingIndianGirl
MusicLovingIndianGirl

Reputation: 5947

Try

SELECT * FROM (
SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY HireDate DESC) rownum,
LastName, Country, HireDate AS Joinning
FROM  Employees
WHERE  Region IS NOT NULL
) r

Upvotes: 1

Related Questions