none
tsql max

    Question

  • Hi

    In the below scenario i need to update the

    I need to update the flag value to 'Y' for each ticket which has highest price

    Create table #SLA ( Ticket varchar(20),sdate datetime,price decimal(8,2),Item varchar(20),flag varchar(10) null) INSERT INTO #SLA(Ticket,sdate,price,Item,flag) Select 'FV92337','2014-02-12',16.94 ,'611379',null union Select 'FV92337','2014-02-11',120.94 ,'551356',null union Select 'FV92337','2014-02-10',20.94 ,'551356',null UNION Select 'FV92222','2014-02-12',30.94 ,'287181',null union Select 'FV92222','2014-02-11',23.94 ,'611382',null union Select 'FV92222','2014-02-10',3.94 ,'11382',null SELECT * FROM #SLA



     
    Thursday, February 20, 2014 9:43 PM

Answers

  • Use this code:

    ;WITH cte
    AS (
    	SELECT *
    		,ROW_NUMBER() OVER (
    			PARTITION BY Ticket ORDER BY Price DESC
    			) AS Rn
    	FROM #Sla
    	)
    UPDATE cte
    SET Flag = 'Y'
    WHERE Rn = 1
    
    SELECT *
    FROM #SLA


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, February 20, 2014 9:49 PM
  • hi,

         I need to update the flag value to 'Y' for each ticket which has highest date and highest price.

    suppose we have two same dates then highest price row show get updated to "Y"

    • Marked as answer by superuser33 Sunday, February 23, 2014 3:32 PM
    Thursday, February 20, 2014 10:40 PM
  • If you need highest date first and then highest price, then just change the ORDER BY condition to be

    ORDER BY sDate DESC, Price DESC


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by superuser33 Thursday, February 20, 2014 11:01 PM
    Thursday, February 20, 2014 10:54 PM

All replies

  • Use this code:

    ;WITH cte
    AS (
    	SELECT *
    		,ROW_NUMBER() OVER (
    			PARTITION BY Ticket ORDER BY Price DESC
    			) AS Rn
    	FROM #Sla
    	)
    UPDATE cte
    SET Flag = 'Y'
    WHERE Rn = 1
    
    SELECT *
    FROM #SLA


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, February 20, 2014 9:49 PM
  • hi,

         I need to update the flag value to 'Y' for each ticket which has highest date and highest price.

    suppose we have two same dates then highest price row show get updated to "Y"

    • Marked as answer by superuser33 Sunday, February 23, 2014 3:32 PM
    Thursday, February 20, 2014 10:40 PM
  • If you need highest date first and then highest price, then just change the ORDER BY condition to be

    ORDER BY sDate DESC, Price DESC


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by superuser33 Thursday, February 20, 2014 11:01 PM
    Thursday, February 20, 2014 10:54 PM