-1

How can i select Company name with max count of Employers between 2 dates with using subquery, main problem is that i have 2 same dates with 2 max values, and i need to take only first of it in subquery

select Company.CompanyName from Company 
where FoundingDate between (select top (1) with ties FoundingDate
from Company order by FoundingDate desc) and '2020-11-30' 
group by Company.CompanyName
Drozdiuk Oleksandr
  • 1
  • 2

2 Answers2

0

If you have just one row per founding date, then just order by and top:

select top (1) with ties FoundingDate, EmplCount
from Company 
order by EmplCount desc

If you have more thane one row per date, and you want to sum the corresponding counts, then you need aggregation as well:

select top (1) with ties FoundingDate, sum(EmplCount) as sumEmplCount
from Company 
group by FoundingDate
order by sumEmplCount desc
GMB
  • 216,147
  • 25
  • 84
  • 135
  • I apologize for the incomplete question, I wanted to do it through a subquery `select Company.CompanyName from Company where FoundingDate between (select top (1) with ties FoundingDate from Company order by FoundingDate desc) and '2020-11-30' group by Company.CompanyName` – Drozdiuk Oleksandr Dec 19 '20 at 12:27
  • @SashaDrozdiuk: I have no idea what you mean. If the above queries do not do what you want, then you might want to [edit your question](https://stackoverflow.com/q/65369742/10676716) to provide proper sample data and desired results, in order to clarify your requirement. – GMB Dec 19 '20 at 12:32
0
select * from Company 
where FoundingDate between (select top (1) with ties FoundingDate
from Company order by FoundingDate desc) and '2020-11-30' 
group by Company.Id, Company.CompanyName, Company.DirectorName, Company.EmplCount,    Company.FoundingDate

select * from Company
where FoundingDate between (select max(FoundingDate) from Company where EmplCount = (select max(EmplCount) from Company)) and '2020-11-30'

2 Kind of answer

Drozdiuk Oleksandr
  • 1
  • 2