none
Get the last but one value

    Question

  • I would like to get the last row from every order and the quantity of the last row but one:

    SELECT o.order_id, o.order_user, o.Q,
    Q_before=(SELECT TOP 1 Q FROM dbo.orders WHERE order_id=o.order_id AND order_date<o.order_date 
    ORDER BY order_date DESC) FROM dbo.Orders AS o

    How can this be done with new functions in SQL 2012?

    Thursday, June 26, 2014 10:43 AM

Answers

  • Hello,

    Did you means the FIRST_VALUE() function in SQL Server 2012? If so, please refer to the following statements:

    SELECT order_id, order_user, Q,
    FIRST_Value(Q)over (Partition by Order_id order by Order_date DESC) as
    Q_before
    FROM dbo.Orders 

    Reference:http://technet.microsoft.com/en-us/library/hh213018(v=sql.110).aspx

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    Sunday, June 29, 2014 4:32 AM
    Moderator
  • Service Broker?

    You description does not really match the query, and the query looks funny. Isn't order_id the primary key of Orders? In such the subquery will always return NULL...

    But assuming that order_id is not the PK, the translation is:

    SELECT o.order_od, o.order_user, o.Q,
           Q_before = LAG(Q) OVER(PARTITION BY o.order_id ORDER BY o.order_date)
    FROM   dbo.Orders AS o


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by simonxy Tuesday, July 22, 2014 9:31 AM
    Sunday, June 29, 2014 8:59 AM

All replies

  • Hello,

    Did you means the FIRST_VALUE() function in SQL Server 2012? If so, please refer to the following statements:

    SELECT order_id, order_user, Q,
    FIRST_Value(Q)over (Partition by Order_id order by Order_date DESC) as
    Q_before
    FROM dbo.Orders 

    Reference:http://technet.microsoft.com/en-us/library/hh213018(v=sql.110).aspx

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    Sunday, June 29, 2014 4:32 AM
    Moderator
  • Service Broker?

    You description does not really match the query, and the query looks funny. Isn't order_id the primary key of Orders? In such the subquery will always return NULL...

    But assuming that order_id is not the PK, the translation is:

    SELECT o.order_od, o.order_user, o.Q,
           Q_before = LAG(Q) OVER(PARTITION BY o.order_id ORDER BY o.order_date)
    FROM   dbo.Orders AS o


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by simonxy Tuesday, July 22, 2014 9:31 AM
    Sunday, June 29, 2014 8:59 AM