CODEWITHSUNDEEP
sqlsql-server
Arvinth Kumar
Arvinth Kumar

Reputation: 1014

How to find the first row which has value not equal to Zero

I need to find the first date where Ten_Yr_CAPE is not having 0 value. Output table should have Country column and Date Column on which has Date when Ten_Yr_CAPE became greater than 0.

I wrote the below query but it pulling all records having 'Ten_Yr_CAPE' <> 0. I need only the initial date. Can anyone help me.

Select TOP(1) returns only one record. I need one record for each country. So totally 20 records for each country 

    select  [Date],[Country] from [Tableau].[dbo].[Country_table4$] where 
    [Ten_Yr_CAPE] <> 0 
     Group by [Country], [Date]
     Order by [Date] ASC

Upvotes: 3

Views: 3015

Answers (3)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can use top (1) with ties as below:

select top (1) with ties  [Date],[Country]
from [Tableau].[dbo].[Country_table4$]
where [Ten_Yr_CAPE] <> 0 
order by row_number() over(partition by [Country] order by [Date] ASC)

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I think aggregation is the simplest method:

select  t.[Country], min(t.[Date])
from [Tableau].[dbo].[Country_table4$] t
where [Ten_Yr_CAPE] <> 0 
Group by [Country]
Order by [Date] ASC

Upvotes: 5

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

SELECT *
FROM (     
     SELECT *, ROW_NUMBER() OVER (PARTITION BY [COUNTRY]
                                  ORDER BY [Date]) as rn
     FROM [Tableau].[dbo].[Country_table4$] 
     WHERE [Ten_Yr_CAPE] <> 0 ) AS T
WHERE T.rn = 1

Upvotes: 4

Related Questions