Query help- check min(column1) , if column1 is same take max( column 2 )

  • sql_learner29

    SSCrazy Eights

    Points: 9107

    Hi all,

    I have a question , I was trying to get a result based on 2 different conditions that I have to place in ORDER..
    first test for MIN of priority ...then only if priorities are same go and check for max of startdate to get final doc value in result..

    ..

    I have to get for each ID final doc value, based on 1 rule or the other
    first check MIN(priority) and get finaldoc, if priority is same then check max(start date) and get the finaldoc
    the grouping has to be done by docname..

    Here is the SQL sample code:

    CREATE TABLE #t(
        [ID] [varchar](10) NULL,
        [finalDoc] [int] NULL,
        [DocType] [varchar](5) NULL,
        [DocName] [varchar](50) NULL,
        [Priority] [int] NULL,
        [StartDate] [datetime] NULL
    ) ON [PRIMARY]

    INSERT INTO #t
    SELECT      555,    12075    ,'BEN',    'Benefit doc'    ,101,    '2016-01-01 00:00:00.000'
    UNION
    SELECT      555,    12314    ,'BEN',    'Benefit doc'    ,101,    '2017-01-01 00:00:00.000'
    UNION
    SELECT      555,    12503    ,'BEN',    'Summary doc'    ,100,    '2016-01-01 00:00:00.000'
    union
    SELECT      555,    13391    ,'BEN',    'Coverage doc'    ,200,    '2015-01-01 00:00:00.000'
    union
    SELECT      555,    12075    ,'BEN',    'Coverage doc'    ,300,    '2017-01-01 00:00:00.000'

    select * from #t order by docname

    --Expected result

    --id    Doctype        DocName            PRIO    STDT                    FinalDOC
    --555        BEN        Benefit doc        101        2017-01-01 00:00:00.000        12314
    --555        BEN        Coverage doc    200        2015-01-01 00:00:00.000        13391
    --555        BEN        Summary doc        100        2016-01-01 00:00:00.000        12503

    I wrote this select using row number but it doesn't work for coverage doc for min priority:

    SELECT * ,ROW_NUMBER() OVER (PARTITION BY [Priority]

    ORDER BY [Priority], [StartDate] desc) RN into #u FROM #t

    select * from #u

    select * from #u where RN = 1

    Any help on this..
    Thanks in advance..

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • anthony.green

    SSC Guru

    Points: 112438

    You would need to do a sort of subquery to get the minimum priority value for each id and doc type, something like this

    select u1.* from #u u1 inner join (SELECT min(priority) as minpriority, id from #u u2 GROUP BY u2.docname, u2.id) dev1 on u1.id = dev1.id and u1.Priority = dev1.minpriority where RN = 1

  • sql_learner29

    SSCrazy Eights

    Points: 9107

    thanks @anthony.green
    it works..

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • jyothi.pragada

    SSC Veteran

    Points: 200

    with cte
    as
    (
    select id,Doctype,DocName,[Priority], StartDate,[finalDoc],RANK() over (partition by DocName order by [Priority] asc,StartDate desc) as r_no
    from #t
    ) SELECT id,Doctype,DocName,[Priority], StartDate,[finalDoc] from cte where r_no=1[f

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply