-2

Data Model: 1

Hi, I am trying to get "country with the highest number of tests".

Query: 2

I tried using one table.. ok... but how I get it with "countryname"? How should I make this with inner join?

Olivia Stork
  • 4,660
  • 5
  • 27
  • 40
furkanak
  • 3
  • 2
  • just as an aside, simply adding an underscore to "DATE" (DATE_) to keep from using a reserved word is not, in my opinion, a very good naming convention. I look to name all of my columns as adjective_noun. Much more descriptive/self-documenting, and no chance of trying to use a reserved or key word. I'd also think that an individual test would have enough properties to justify having it's own TESTS table with a unique row for each unique test. And with that, storing 'total_tests' is a design flaw, as it would be derived from the TESTS table. – EdStevens Jan 16 '21 at 20:21

2 Answers2

0

Join, as you said.

select s.countryname,
       s.date_,
       s.total_tests
from (select 
         row_number() over (order by a.total_tests desc) rn,
         a.date_,
         a.total_tests,
         c.countryname
      from cases_by_countries a join country c 
        on c.countryid = a.country_id       
     ) s
where s.rn = 1;
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
-1

if you need only the highest you should try this

select c.CountryID, ts.Total_Tests
from Country c
    inner join (
    select top(1) Country_ID, Total_Tests
    from CASES_BY_COUNTRIES
    order by Total_Tests desc
) ts on c.CountryID = ts.Country_ID
Manfred Wippel
  • 1,946
  • 1
  • 15
  • 14