Get two highest versions, not cancelled

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Get two highest versions, not cancelled

Author

Topic

Zifter
Yak Posting Veteran

51 Posts

Posted - 2012-08-13 : 04:17:30

Hi all,

In an earlier topic (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=165075), I had help finding the records with the highest version and status not cancelled.

Now the user requirements have changed...
I need to indicate the two records with the highest MajorVersion per Code and MinorVersion.

This is what I've got so far

declare @aTable table(ID int IDENTITY(1,1), Code varchar(1), MajorVersion int, MinorVersion int, StatusCode int)

insert into @aTable
values
('A',0,0,0),
('A',1,0,1),
('A',2,0,3),
('A',3,1,2),
('A',4,1,2),
('A',5,2,2),
('A',6,2,6),
('A',7,2,2),
('A',8,2,3),
('B',0,0,1),
('B',1,1,2),
('B',2,2,3)


select t.Code, t.MajorVersion, t.MinorVersion, t.StatusCode,
case when t.rn = 1 then 'This one' else '' end
from
(
  select Code, MajorVersion, MinorVersion, StatusCode,
         ROW_NUMBER() over (partition by Code, MinorVersion 
                            order by case when StatusCode <> 6 then 0 else 1 end, 
                            MajorVersion desc, 
                            MinorVersion desc) as rn
  from @aTable
) as t
order by Code, MajorVersion desc, MinorVersion desc

This gives the following result, where all the highest Major versions per Code and Minor version are indicated

Code	Major	Minor	StatusCode	
A	8	2	3	This one
A	7	2	2	
A	6	2	6	
A	5	2	2	
A	4	1	2	This one
A	3	1	2	
A	2	0	3	This one
A	1	0	1	
A	0	0	0	
B	2	2	3	This one
B	1	1	2	This one
B	0	0	1	This one

What I would like to have is only the top two for each Code / MinorVersion (I've colored the ones I don't need in red)

Code	Major	Minor	StatusCode	
A	8	2	3	This one
A	7	2	2	
A	6	2	6	
A	5	2	2	
A	4	1	2	This one
A	3	1	2	
A	2	0	3	This one
A	1	0	1	
A	0	0	0	
B	2	2	3	This one
B	1	1	2	This one
B	0	0	1	This one

I have no idea how to get only the first two. Can I use a "top 2" and if so, where? Do I need a second "ROW_NUMBER() over (partition by <something>"?
Any help would be highly appreciated! Thanks in advance.

(edited layout)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-13 : 06:55:17

[code];WITH cteSource(ID, Code, MajorVersion, MinorVersion, StatusCode, rn)
AS (
SELECT ID,
Code,
MajorVersion,
MinorVersion,
StatusCode,
CASE
WHEN StatusCode = 6 THEN 0
WHEN MajorVersion = 0 THEN 0
WHEN MinorVersion = 0 THEN 0
ELSE ROW_NUMBER() OVER (PARTITION BY Code, MinorVersion ORDER BY MajorVersion DESC)
END AS rn
FROM @aTable
)
SELECT ID,
Code,
MajorVersion,
MinorVersion,
StatusCode,
CASE rn
WHEN 1 THEN 'This one'
ELSE ''
END AS Yak
FROM cteSource[/code]

N 56°04'39.26"
E 12°55'05.63"

Zifter
Yak Posting Veteran

51 Posts

Posted - 2012-08-13 : 07:41:50

Thanks SwePeso for taking the time to answer my post!

In the meantime I've found a solution which gives me the same result.

select ID, Code, MajorVersion, MinorVersion, StatusCode ,
       case when t.rwn = 1 and (t.rnk = 1 or t.rnk = 2) and StatusCode <> 6 then 'This one' else '' end
from
(
  select *, 
         ROW_NUMBER() over (partition by Code, MinorVersion
                            order by
                            case when StatusCode <> 6 then 0 else 1 end, 
                            Code, 
                            MajorVersion desc, 
                            MinorVersion desc) as rwn,
         DENSE_RANK() over (order by MinorVersion desc) as rnk
  from @aTable
) t
order by t.Code, t.MajorVersion desc, t.MinorVersion desc

Both give the same result, exactly like in my first example.
But! One of the requirements is not to include the ones with StatusCode 6. If the test data is changed, so both records for Code A with MinorVersion 1 get StatusCode 6, I would like to get the next avialable.

declare @aTable table(ID int IDENTITY(1,1), Code varchar(1), MajorVersion int, MinorVersion int, StatusCode int)

insert into @aTable
values
('A',0,0,0),
('A',1,0,1),
('A',2,0,3),
('A',3,1,6),
('A',4,1,6),
('A',5,2,2),
('A',6,2,6),
('A',7,2,2),
('A',8,2,3),
('B',0,0,1),
('B',1,1,2),
('B',2,2,3)


-- SwePeso's solution
;WITH cteSource(ID, Code, MajorVersion, MinorVersion, StatusCode, rn)
AS (
	SELECT	ID,
		Code,
		MajorVersion,
		MinorVersion,
		StatusCode,
		CASE 
			WHEN StatusCode = 6 THEN 0
			WHEN MajorVersion = 0 THEN 0
			WHEN MinorVersion = 0 THEN 0
			ELSE ROW_NUMBER() OVER (PARTITION BY Code, MinorVersion ORDER BY MajorVersion DESC)
		END AS rn
	FROM	@aTable
)
SELECT	ID,
	Code,
	MajorVersion,
	MinorVersion,
	StatusCode,
	CASE rn
		WHEN 1 THEN 'This one'
		ELSE ''
	END AS Yak
FROM	cteSource
order by Code, MajorVersion desc, MinorVersion desc

-- My solution
select ID, Code, MajorVersion, MinorVersion, StatusCode ,
       case when t.rwn = 1 and (t.rnk = 1 or t.rnk = 2) and StatusCode <> 6 then 'This one' else '' end
from
(
  select *, 
         ROW_NUMBER() over (partition by Code, MinorVersion
                            order by
                            case when StatusCode <> 6 then 0 else 1 end, 
                            Code, 
                            MajorVersion desc, 
                            MinorVersion desc) as rwn,
         DENSE_RANK() over (order by MinorVersion desc) as rnk
  from @aTable
) t
order by t.Code, t.MajorVersion desc, t.MinorVersion desc

With this test data, I would like to get the following result (the green 'This one' is missing)

ID	Code	Major	Minor	StatusCode	
9	A	8	2	3	This one
8	A	7	2	2	
7	A	6	2	6	
6	A	5	2	2	
5	A	4	1	6	
4	A	3	1	6	
3	A	2	0	3	This one
2	A	1	0	1	
1	A	0	0	0	
12	B	2	2	3	This one
11	B	1	1	2	This one
10	B	0	0	1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources