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
Arvinth Kumar
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
Juan carlos oropeza
source
to share
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
Gordon linoff
source
to share
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
Kannan Kandasamy
source
to share