How to group this query to get required results

user_12447454 0
Viewed 29

I have a table of tasks. Each task can have multiple versions (including a draft version).

  1. Each task will share a GUID
  2. Each task can have multiple version
  3. Drafts are marked by version = 0

I am trying to come up with a query that will give me one record per task.

  1. I should always only get the highest version of a task.
  2. If a task has a draft version, I should only see THAT.

I don't have any control over the structure of the tables or data.

So far I am trying to use the ROW_NUMBER function but am struggling to come up with the right solution.

declare @SWs TABLE
(
    Guid nvarchar(36),
    Version int,
    Title nvarchar(50),
    IsLatestVersion bit
)

INSERT INTO @SWs
select 'guid1', 1, 'StandardWork1', 1
union
select 'guid2', 1, 'StandardWork2', 0
union
select 'guid3', 1, 'StandardWork3', 1
union
select 'guid4', 1, 'StandardWork4', 1
union
select 'guid1', 0, 'StandardWork1(Draft)', 0
union
select 'guid5', 0, 'StandardWork5(Draft)', 0
union
select 'guid2', 2, 'StandardWork2.2', 1

--QUERY SHOULD RETURN ONLY ONE ROW PER GUID
--ALWAYS SHOW MAX VERSION UNLESS THERE IS DRAFT, THEN SHOW DRAFT
select 
ROW_NUMBER() OVER(PARTITION BY Guid ORDER BY Version, IsLatestVersion desc) as Row#
,Guid
,Version
,Title
,IsLatestVersion
from @SWs

But this gives me the result:

Results

This is almost correct but the task with guid2 is not correctly numbered. Because it doesn't have a draft and also have multiple versions I can't get the Version 2 to show up first. Any help with this query would be appreciated.

window-functions sql-order-by tsql sql-server sql
1 Answers

I think you want a conditional sort as first criteria to put rows whose version is 0 first, and then a desceding sort on the version and on islatestversion:

row_number() over(
    partition by guid 
    order by 
        case when version = 0 then 0 else 1 end, 
        version desc,
        islatestversion desc
) as row#

Demo on DB Fiddle:

row# | Guid  | Version | Title                | IsLatestVersion
---: | :---- | ------: | :------------------- | :--------------
   1 | guid1 |       0 | StandardWork1(Draft) | False          
   2 | guid1 |       1 | StandardWork1        | True           
   1 | guid2 |       2 | StandardWork2.2      | True           
   2 | guid2 |       1 | StandardWork2        | False          
   1 | guid3 |       1 | StandardWork3        | True           
   1 | guid4 |       1 | StandardWork4        | True           
   1 | guid5 |       0 | StandardWork5(Draft) | False
user_106767160
Related