SQL Selecting top 2 people from each company

It used to be a nightmarish situation when data is briefed usually for a DM campaign and they want you to select the ‘top 2’ from each company. Based on… ?? Well the top 2 most senior obviously.

I’ve now started asking for clarification so I can build it into the data pull automatically, we have clients where we have 25 marketing directors all registered at the same place. So differentiating this makes sense in order to pull the data.

By using Rank over Partition in SQL I now just get clarification on other variables they want to order by – so, last log in date, number of downloads, subscription level, that sort of thing and then run the script:

Rank() over (Partition by <GROUP BY FIELD> Order by <ORDERING FIELD> asc, <ORDERING FIELD> desc) as ‘Ranking’

Then you can just stick it all in Excel and filter on the Ranking column for values 1 and 2. Its can then be left to the team to decide to chop and change who.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s