September 4, 2015 at 5:05 am
Hi alll,
I was wondering if anyone has any ideas on how I could achieve a ranking number, where I have some grouping involved.
I want to have the groups in sets of 4, but if I get to a new person the ranking increases, even if I haven't filled the group.
For example I might have a like (sorry for the lazy Sales figures, but they're not important for the end goal):
Name Year Sales Ranking
-------------------- ----------- ----------- --------------------
John Smith 2009 1296 1
John Smith 2010 1296 1
John Smith 2011 1296 1
John Smith 2012 1296 1
John Smith 2013 1296 2
John Smith 2014 1296 2
Nina Green 2014 1296 3
Steve Jobs 2006 1296 4
Steve Jobs 2007 1296 4
Steve Jobs 2008 1296 4
Steve Jobs 2009 1296 4
Steve Jobs 2010 1296 5
Steve Jobs 2011 1296 5
Steve Jobs 2012 1296 5
Steve Jobs 2013 1296 5
Steve Jobs 2014 1296 6
So, when I get to John's 5 year, the Rank increased. I then only had two more rows before I get to Nina, but the Rank number goes up, as the details are for a different person from the prior user.
Can anyone think of a way? I did wonder about using mods, but that won't increase at John, so I feel I need an extra step, but unsure what.
Thanks.
Edit:
Currently, this is where I'm at:
Create Table #Test (Name varchar(20), Year int, Sales int)
Insert into #Test (Name, Year, Sales)
Values ('Steve Jobs', 2006, 1296),
('Steve Jobs', 2007, 1296),
('Steve Jobs', 2008, 1296),
('Steve Jobs', 2009, 1296),
('Steve Jobs', 2010, 1296),
('Steve Jobs', 2011, 1296),
('Steve Jobs', 2012, 1296),
('Steve Jobs', 2013, 1296),
('Steve Jobs', 2014, 1296),
('John Smith', 2009, 1296),
('John Smith', 2010, 1296),
('John Smith', 2011, 1296),
('John Smith', 2012, 1296),
('John Smith', 2013, 1296),
('John Smith', 2014, 1296),
('Nina Green', 2014, 1296)
Select *, DENSE_RANK() over (Order by Name) + ((RANK() over (Partition by Name Order by Year) - 1) /4) as Ranking
from #Test
Order by Name, Year
Drop Table #Test
This works for John, but Nina then has a Ranking of 2 for her only year, where as she should be 3. Steve also then has a value one too low for all of his ranks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 4, 2015 at 6:42 am
This seems to produce the required results:
WITH NameGroups
AS
(
SELECT Name, Year, Sales
,(ROW_NUMBER() OVER (PARTITION BY Name ORDER BY year) - 1)/4 + 1 AS Grp
FROM #test
)
SELECT Name, Year, Sales
,DENSE_RANK() OVER (ORDER BY Name, Grp) AS Grp
FROM NameGroups
ORDER BY Name, Year;
September 4, 2015 at 6:57 am
And here I was trying to figure out how to keep the posted order.
CREATE TABLE #Test(
ID int IDENTITY(1,1),
Name varchar(20),
Year int,
Sales int,
RankID int)
INSERT INTO #Test(Name, Year, Sales, RankID)
VALUES
('Steve Jobs', 2009 , 1296 , 1),
('Steve Jobs', 2010 , 832 , 1),
('Steve Jobs', 2011 , 1012 , 1),
('Steve Jobs', 2012 , 999 , 1),
('Steve Jobs', 2013 , 1536 , 2),
('Steve Jobs', 2014 , 751 , 2),
('John Smith', 2012 , 324 , 3),
('John Smith', 2013 , 543 , 3),
('John Smith', 2014 , 459 , 3),
('Nina Green', 2014 , 698 , 4);
WITH CTE AS(
SELECT *,
MIN(ID) OVER( PARTITION BY Name) MinEmpID,
(ROW_NUMBER() OVER( PARTITION BY Name ORDER BY Year) + 3 )/ 4 AS GroupRanking
FROM #Test
)
SELECT *,
DENSE_RANK() OVER( ORDER BY MinEmpID, GroupRanking) AS Ranking
FROM CTE
ORDER BY ID;
GO
DROP TABLE #Test
September 4, 2015 at 8:19 am
Very nice Luis.
(And here I was looking into a QU version)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply