CODEWITHSUNDEEP
sqlteradata
user124123
user124123

Reputation: 1683

min of col x with condition from table teradata

I want to find the minimum of a date column, where another condition is met

i.e with table :

ID     date  condition   ... 

1      3/15   0
1      4/15   1
5123   5/15   1
5123   7/15   1
5123   1/15   0 
2      2/15   0
2      1/15   1
2      1/15   1

I want a single line within the select statement, without filtering the rest of the table, to produce : 

ID    date
1     4/15 
5123  5/15
2     1/15

I have tried :

min( date with condition = 1 )

min( date where condition = 1 )

case when   condition=1  then min(date) end

but am getting a syntax error.

Upvotes: 0

Views: 749

Answers (2)

jarlh
jarlh

Reputation: 44766

"I don't want to filter the rest of the table", use a case expression to do conditional aggregation:

select id, min(case when condition = 1 then date end)
from tablename
group by id

Upvotes: 2

Arulkumar
Arulkumar

Reputation: 13237

For each Id if you want the minimum date, you need to use the GROUP BY

SELECT ID, MIN(Date) AS Date
FROM TableName
WHERE Condition = 1
GROUP BY ID

Upvotes: 0

Related Questions