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]

  • ZZartin

    SSC-Dedicated

    Points: 30413

    What about this?

    select id ,Doctype, [DocName] , min(priority) PRIO,max(startdate) STDT , LAST_DOC.LAST_DOC
    from #t
    CROSS APPLY(
    SELECT LAST_VALUE(FinalDOC) OVER(PARTITION BY id,Doctype, [DocName] ORDER BY [priority] DESC, startdate ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) LAST_DOC FROM #t GET_LAST
    WHERE
    GET_LAST.id = #t.id AND GET_LAST.DocType = #t.DocType AND GET_LAST.DocName = #t.DocName
    ) LAST_DOC
    group by   id,Doctype, [DocName], LAST_DOC.LAST_DOC

  • sql_learner29

    SSCrazy Eights

    Points: 9107

    it's giving error
    'LAST_VALUE' is not a recognized built-in function name.

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

    Thanks [/font]

  • ZZartin

    SSC-Dedicated

    Points: 30413

    Heh.. you posted this in the 2012 forum as well, i though i responded there.  It looks like there's an answer there that'll work in 2008 as well.

  • seshagirikudaravalli

    Old Hand

    Points: 362

    select *
    from (select ROW_NUMBER()over(partition by docname order by priority,startdate desc) rownumber,* from #t)a
    where rownumber=1

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

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