CODEWITHSUNDEEP
sqldb2
Edward Ruchevits
Edward Ruchevits

Reputation: 6696

How to refactor this SQL query?

I am writing the following query to IBM DB2 server:

SELECT 
    EMPNO, 
    YEAR(CURRENT TIMESTAMP - BIRTHDATE) AS AGE,
    DENSE_RANK() OVER(ORDER BY YEAR(CURRENT TIMESTAMP - BIRTHDATE) ASC) AS RANK

FROM 
    DANDY.EMPLOYEE;

It works, but looks ugly, as I am repeating expression:

YEAR(CURRENT TIMESTAMP - BIRTHDATE)

But writing as follows doesn't works:

DENSE_RANK() OVER(ORDER BY AGE ASC) AS RANK

How to make it better?

Upvotes: 2

Views: 306

Answers (3)

steve godfrey
steve godfrey

Reputation: 1234

SELECT 
EMPNO, 
AGE,
DENSE_RANK() OVER(ORDER BY AGE ASC) AS RANK
FROM (SELECT EMPNO,
             YEAR(CURRENT TIMESTAMP - BIRTHDATE) AS AGE
      FROM DANDY.EMPLOYEE);

Upvotes: 2

WhoaItsAFactorial
WhoaItsAFactorial

Reputation: 3558

You could use a Common Table Expression (CTE) to get AGE, then use AGE again as needed. 

WITH
   CTEAGE AS
      (SELECT 
          YEAR(CURRENT TIMESTAMP - BIRTHDATE) AS AGE,
      FROM 
          DANDY.EMPLOYEE
      )

   SELECT 
      EMPNO, 
      CTEAGE.AGE,
      DENSE_RANK() OVER(CTEAGE.AGE ASC) AS RANK
   FROM 
      DANDY.EMPLOYEE;

Upvotes: 0

Chamila Chulatunga
Chamila Chulatunga

Reputation: 4914

I know standard ORDER BY clauses usually accept the column index, though not sure if it will work with DENSE_RANK() - maybe try:

DENSE_RANK() OVER(ORDER BY 2 ASC) AS RANK

Upvotes: 0

Related Questions