0

Is it possible to get last but one value of sorted variable using SQL function FIRST_VALUE? Here is a way to get the last value. 

select
[MyVal_Last] = FIRST_VALUE([MyValue]) OVER (PARTITION BY [Category] ORDER BY [MyValue] DESC)  
from tbl

Is it possible to modify it to get second last value?

Update. I am familiar with different ways of getting the last but one value mentioned i.e. here Those answers are old and have grown long beards. I wonder if new version of SQL Server can do it in a more elegant way, using new functions.

Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191

3 Answers3

5
SELECT
  *
FROM
(
  SELECT
    *, 
    ROW_NUMBER() OVER (PARTITION BY [Category]
                           ORDER BY [MyValue] DESC)   AS ordinal
  FROM
    tbl
)
  ordered_tbl
WHERE
  ordinal = 2
MatBailie
  • 83,401
  • 18
  • 103
  • 137
  • Thank you. Please see update. Don't you think that your answer, though correct, seems too long for the year 2017. – Przemyslaw Remin Nov 14 '17 at 10:49
  • No, I don't. It's the most direct expression of your problem statement using the syntactic structure available in SQL. It is also the most performant, given correct indexes. If SQL SERVER had the QUALIFY keyword (present in Teradata) the sub query could be removed to reduce the amount of text, but the execution plan would be Identical. – MatBailie Nov 14 '17 at 11:05
  • Shouldn't the ordering in ROW_NUMBER be DESC to find the last but one? – Przemyslaw Remin Nov 14 '17 at 12:08
  • My mistake. I read your post as "this gets the first one, I want the second last one", so I reversed the order you were using to then pick the 2nd from last. Turns out that you had already reversed the order yourself so as to use `FIRST_VALUE()`, which was slightly counter intuitive *(not sure why you didn't just use `LAST_VALUE()` and `ORDER BY ASC`)*. – MatBailie Nov 14 '17 at 15:25
2

you can also use OFFSET FETCH function to get second last row.

CREATE TABLE Cinema
    ([CinemaID] int, [Name] varchar(7))
;

INSERT INTO Cinema
    ([CinemaID], [Name])
VALUES
    (1, 'Odeon'),
    (2, 'Mercury'),
    (3, 'Apollo'),
    (4, 'Venus')
;

 SELECT c.* 
    FROM dbo.Cinema AS c                    
    ORDER BY CinemaID DESC
    OFFSET 1 ROW
    FETCH FIRST 1 ROW ONLY ;
SqlKindaGuy
  • 3,501
  • 2
  • 12
  • 29
  • That looks good. Praise for avoiding dreaded sub query like in row_number() method. Does it work with category groups? – Przemyslaw Remin Nov 14 '17 at 10:53
  • What do you mean by "Category groups" - That you want the second row for each category you have? – SqlKindaGuy Nov 14 '17 at 10:58
  • Say Odeon and Mercury are Category1. Apollo and Venus is Category2. And I want last but one value for each category. – Przemyslaw Remin Nov 14 '17 at 11:01
  • No, then you have to use subqueries, like partition by. – SqlKindaGuy Nov 14 '17 at 11:04
  • 2
    The notion of a "dreaded sub query" is based on a misunderstanding of how SQL (a declarative language) is translated into an execution plan. Where sub queries have preformance issues it is due to misuse that is equally possible without sub queries. Such as obfuscation search predicates in joins, preventing the user of indexes for Seek operations. – MatBailie Nov 14 '17 at 11:04
0

OK, I have found it. The function LAG does the job in year 2017. I do not know if it is most performant but it is good enough as simplicity counts. 

[MyVal_Last] = LAG([MyValue],1) OVER (PARTITION BY [Category] ORDER BY [MyValue] ASC)
Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191
  • 1
    That doesn't do what you described. It gets the previous row's value. So in a set of 10 rows, the first row has NULL, the 2nd row has the value from the 1st row, the 3rd row has the value from the 2nd row....the 10th row has the 9th row's value. – MatBailie Nov 14 '17 at 11:30