CODEWITHSUNDEEP
teradata
madsthiru
madsthiru

Reputation: 51

Query to return first row based on condition

I would like to get the first row from the table only when the condition gets satisfied. The table has multiple entries and only when the particular value of a column is present at the top of the table, the query should return the output else the query should not return any row.

SELECT (CASE WHEN COL1 = 'AAA' THEN COL1 ELSE NULL END) AS A
FROM TABLE
QUALIFY ROW_NUMBER () OVER (ORDER BY TIMESTAMP DESC) = 1
WHERE A IS NOT NULL

If the value of the column is 'AAA' present at the top of the table then it should return the output, if not then no row should be returned. I am still getting the output as 'AAA' even when the first row is not 'AAA'.

Upvotes: 1

Views: 792

Answers (2)

Nahiyan
Nahiyan

Reputation: 532

This query should get the first row from the table and see if the COL1 is equal to "AAA."

SELECT * FROM (SELECT * FROM TABLE ORDER BY TIMESTAMP DESC LIMIT 1) WHERE COL1 = 'AAA'

Upvotes: 1

dnoeth
dnoeth

Reputation: 60513

You must move your WHERE-condition to QUALIFY:

SELECT *
FROM TABLE
QUALIFY ROW_NUMBER () OVER (ORDER BY TIMESTAMP DESC) = 1
    AND COL1 = 'AAA'

Upvotes: 1

Related Questions