Clever Geek Handbook

How to find the first row that has a non-zero value

I need to find the first date when Ten_Yr_CAPE is not 0. The output table must have a country column and date columns that have a date when Ten_Yr_CAPE becomes greater than 0.

I wrote the following query, but it pulled in for all records that have "Ten_Yr_CAPE" <> 0. I only want the start date. Can someone help me.

Select TOP (1) only returns one record. I need one entry for each country. Thus, there are a total of 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
+3


source to share


3 answers 


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


+4


source


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
+4


source


You can use the top (1) with bindings as shown 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)
+3


source






More articles:


All Articles