[solved] Get highest version, status 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)

[solved] Get highest version, status not cancelled

Author

Topic

Zifter
Yak Posting Veteran

51 Posts

Posted - 2011-09-06 : 07:39:25

Hi all,

This is my first post here.
Probably the Subject isn't making clear what I'm struggling with, but I didn't know how to explain in such few words. Let me clarify.

Let's say I have a table with four columns.
Code, Major Version, Minor Version, StatusCode.

Example of data:

Code    Major    Minor    StatusCode
A       0        0        0 
A       0        1        0
A       1        1        3
A       1        2        6
A       2        2        2
B       0        1        0
B       1        1        1
B       1        2        6
C       2        2        2
C       2        3        6
C       3        3        6

I would like to have a query which returns all rows, indicating which row, per Code, has the highest Major-Minor version, and has a status different from 'Cancelled' (StatusCode <> 6).

Expected result

Code    Major    Minor    StatusCode    <HighestNotCancelled>
A       0        0        0
A       0        1        0
A       1        1        3
A       1        2        6
A       2        2        2             ThisOne
B       0        1        0
B       1        1        1             ThisOne
B       1        2        6
C       2        2        2             ThisOne
C       2        3        6
C       3        3        6

I tried using RANK() over partition, but the result is not what I need.

select t.Code, t.Major, t.Minor, t.StatusCode, t.Ranking
from
(
  select i.Code, i.Major, i.Minor, i.StatusCode, RANK() over (partition by i.Code order by i.StatusCode) as ranking
  from MyTable i
) t
order by t.Major desc, t.Minor desc

Any help would be appreciated.
Thanks in advance

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2011-09-06 : 07:52:26

post some sample data and the expected result data.

Karthik
http://karthik4identity.blogspot.com/

Zifter
Yak Posting Veteran

51 Posts

Posted - 2011-09-06 : 07:56:29

quote:
Originally posted by karthik_padbanaban

post some sample data and the expected result data.

Karthik
http://karthik4identity.blogspot.com/

I was Editing the post, adding that.
Hope it clarifies what I'm trying to do.
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-06 : 07:59:09

[code]select t.Code, t.Major, t.Minor, t.StatusCode,
case when t.rn=1 then 'This one'else '' end as [<HighestNotCancelled>]
from
(
select i.Code, i.Major, i.Minor, i.StatusCode, ROW_NUMBER() over (partition by i.Code order by [Major Version] desc,[Minor Version] desc) as rn
from MyTable
where i.StatusCode<>6
) t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Zifter
Yak Posting Veteran

51 Posts

Posted - 2011-09-06 : 08:16:40

quote:
Originally posted by visakh16

select t.Code, t.Major, t.Minor, t.StatusCode,
case when t.rn=1 then 'This one'else '' end as [<HighestNotCancelled>]
from
(
select i.Code, i.Major, i.Minor, i.StatusCode, ROW_NUMBER() over (partition by i.Code order by [Major Version] desc,[Minor Version] desc) as rn
from MyTable
where i.StatusCode<>6
) t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks visakh16 for the quick reply!

It is almost what I need. The query does indeed put the 'ThisOne' on the correct rows, but is skipping the rows where StatusCode = 6.
I would like a query which returns all rows.
Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-06 : 08:28:20

[code]select t.Code, t.Major, t.Minor, t.StatusCode,
case when t.rn=1 then 'This one'else '' end as [<HighestNotCancelled>]
from
(
select i.Code, i.Major, i.Minor, i.StatusCode, ROW_NUMBER() over (partition by i.Code order by case when i.StatusCode<>6 then 0 else 1 end,[Major Version] desc,[Minor Version] desc) as rn
from MyTable
) t

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Zifter
Yak Posting Veteran

51 Posts

Posted - 2011-09-06 : 08:33:11

Visakh16, Thank You!!

I didn't know we could put a case structure in the order by. That did the trick.

Thanks a ton!

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2011-09-06 : 08:36:31

quote:
Originally posted by visakh16

select t.Code, t.Major, t.Minor, t.StatusCode,
case when t.rn=1 then 'This one'else '' end as [<HighestNotCancelled>]
from
(
select i.Code, i.Major, i.Minor, i.StatusCode, ROW_NUMBER() over (partition by i.Code order by case when i.StatusCode<>6 then 0 else 1 end,[Major Version] desc,[Minor Version] desc) as rn
from MyTable
) t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

This is fantastic part of work.. I learnt new too.

Karthik
http://karthik4identity.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-06 : 08:38:34

quote:
Originally posted by Zifter

Visakh16, Thank You!!

I didn't know we could put a case structure in the order by. That did the trick.

Thanks a ton!

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-06 : 08:39:27

quote:
Originally posted by karthik_padbanaban

quote:
Originally posted by visakh16

select t.Code, t.Major, t.Minor, t.StatusCode,
case when t.rn=1 then 'This one'else '' end as [<HighestNotCancelled>]
from
(
select i.Code, i.Major, i.Minor, i.StatusCode, ROW_NUMBER() over (partition by i.Code order by case when i.StatusCode<>6 then 0 else 1 end,[Major Version] desc,[Minor Version] desc) as rn
from MyTable
) t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

This is fantastic part of work.. I learnt new too.

Karthik
http://karthik4identity.blogspot.com/

Glad that i added something new to your knowledgebase

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources