Creating column and filtering it in one select statement-CodePudding

Wondering if it is possible to creating a new column and filter on that column. The following is an example:

SELECT row_number() over (partition by ID order by date asc) row# FROM table1 where row# = 1

Thanks!

CodePudding user response：

Some databases support a QUALIFY clause which you might be able to use:

SELECT *
FROM table1
QUALIFY ROW_NUMBER() OVER (PARTITION BY ID ORDER BY date) = 1;

On SQL Server, you may use a TOP 1 WITH TIES trick:

SELECT TOP 1 WITH TIES *
FROM table1
ORDER BY ROW_NUMBER() OVER (PARTITION BY ID ORDER BY date);

More generally, you would have to use a subquery:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY date) rn
    FROM table1 t
)

SELECT *
FROM cte
WHERE rn = 1;

CodePudding user response：

The WHERE clause is evaluated before the SELECT so your column has to exist before you can use a WHERE clause. You could achieve this by making a subquery of the original query.

SELECT * 
FROM 
     (
        SELECT row_number() over (partition by ID order by date asc) row# 
        FROM table1 
     ) a
WHERE a.row# = 1