0

I have a table like this:

RunId------CompanyId------NumberOfEmployees
1-------------------1-------------------10
2-------------------1-------------------10
3-------------------1-------------------8
4-------------------1-------------------9
5-------------------1-------------------11* (The first time company 1 expands to a size bigger than it's original size of 10)
6-------------------1-------------------12
3-------------------2-------------------50
4-------------------2-------------------55* (The first time company 2 expands to a size bigger than it's original size of 55)
5-------------------2-------------------54
1-------------------3-------------------100
2-------------------3-------------------100
3-------------------3-------------------100

And I want to find for each CompanyID, what run did the company start expanding to a size that's bigger than its original starting size.

So I'd like to have a result set like this:

CompanyId------StartRun------ExpansionRun-------StartingSize
1-------------------1-------------------5-------------------10
2-------------------3-------------------4-------------------50

Thanks so much for your time in advance!

I have also done my research and found this answer Select rows where column value has changed, however this only compares the consecutive runs instead of comparing to the first run a company started.

Community
  • 1
  • 1
vincwng
  • 648
  • 1
  • 6
  • 12
  • How can one find the original size? – 1000111 Jul 26 '16 at 13:49
  • The original size is the size at the first run we see the company. Ex: The original size for company 2 is 50, because the first time we see them is at run 3 and it has size 50. Thanks! – vincwng Jul 26 '16 at 13:51

1 Answers1

1

First you need to find the original starting size. You can get this (presumably) from the minimum run id. Then another join gets you the information you are looking for:

select c.CompanyId, min(tt.runid) as FirstBigger_runid
from (select t.CompanyId, min(runid) as minrunid
      from t
      group by t.CompanyId
     ) c join
     t t
     on t.CompanyId = c.CompanyId and t.runid = c.minrunid left join
     t tt
     on tt.CompanyId = c.CompanyId and
        tt.runid > c.minrunid and
        tt.NumberOfEmployees > t.NumberOfEmployees
group by c.RunId;

This gets the runid of the first time the company exceeds the original number of employees. An additional join will get all other information from that row.

Here is an alternative method of getting the run id that meets the criteria:

select t.CompanyId, min(RunId) as theRunId
from (select t.*,
             (select t2.NumberOfEmployees
              from t t2
              where t2.CompanyId = t.CompanyId
              order by t2.RunId
              limit 1
             ) as first_NumberOfEmployees
      from t
      having NumberOfEmployees > first_NumberOfEmployees
     ) t
group by t.CompanyId;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks so much. I edited your first solution a little bit and got it to work for me. I needed to add group by c.CompanyId, c.minrunid, t.NumberOfEmployees to the last line. Also added t.NumberOfEmployees to the first line. – vincwng Jul 26 '16 at 14:25
  • Didn't have much luck on the second answer, but you definitely pointed me to the right direction Thanks! – vincwng Jul 26 '16 at 14:28