How to return only the first element that matches a criteria in SQL?-CodePudding

I'm trying to determine the first time that a new vaccination was given in each country. AKA the first result when these conditions are met for each country, ordered by date

I have this currently:

Select dea.location, dea.date, vac.new_vaccinations
From PortfolioProject..CovidDeaths dea
Join  PortfolioProject..CovidVaccinations vac
    on dea.location = vac.location AND
    dea.date = vac.date
where   dea.continent is not null 
    and new_vaccinations is not null
order by date

It gives me the number and dates in which each country gave vaccinations, ordered by the date. There are multiple entries for each country though. I tried using Distinct but it didn't work. I would like to know Canada's first date they had a new vaccine, Israel's date when they had their first vaccine, Bahrain's first date when they had a new vaccine etc. Example output Below. Thanks!

location    date    new_vaccinations
Canada  2020-12-22 00:00:00.000 5737
Israel  2020-12-22 00:00:00.000 44621
Israel  2020-12-23 00:00:00.000 62838
Canada  2020-12-23 00:00:00.000 8486
Canada  2020-12-24 00:00:00.000 10309
Bahrain 2020-12-24 00:00:00.000 11106

CodePudding user response：

"First date" is another way of saying "Min Date" or "Lowest date" so I think you can use an aggregation function:

Select
    dea.location,
    min(dea.date) as date,
    vac.new_vaccinations
From 
    PortfolioProject..CovidDeaths dea
    Join  PortfolioProject..CovidVaccinations vac
    on dea.location = vac.location 
    AND dea.date = vac.date
where 
    dea.continent is not null 
    and new_vaccinations is not null
group by 
    dea.location, vac.new_vaccinations

CodePudding user response：

Joining the two tables on date seems odd, unless you want to display something from the deaths table as well. However, that doesn't seem like the case given your sample output. Also, your question is simply this: I'm trying to determine the first time that a new vaccination was given in each country

That could be accomplished with this query:

select location, min(some_date) as min_date
from vaccinations
where new_vaccinations is not null
group by location

But if you want to also display the number of vaccinations on that date, then additional code is needed. On top of that, you want to exclude locations where the continent is null. Given these requirements, the following query accomplishes all of this:

 with first_vaccine as (
    select location, min(some_date) as min_date
    from vaccinations
    where new_vaccinations is not null
    group by location
    )
 select distinct fv.location, fv.min_date, v.new_vaccinations
 from first_vaccine fv
 join deaths d
   on fv.location = d.location
  --and fv.min_date = d.some_date  --this seems unneccessary
 join vaccinations v
   on fv.location = v.location
  and fv.min_date = v.some_date
 where d.continent is not null
 order by location

The output is a 3 column output:

| location | min_date | new_vaccinations |

Which has been represented with data found in this fiddle.