FIRST_VALUE and LAST_VALUE with NULL values

Question

I want to get the top 2 expensive book and least 2 expensive book prices using FIRST_Value & LAST_Value SQL Server

The presence of Nulls is giving incorrect Min price value, I want the Min price to ignore Nulls

Select top 2 FIRST_VALUE(price) Over(Order by price) as MinPrice,
FIRST_VALUE(title) Over (order by price) as MinName,
LAST_VALUE(price) Over (order by price desc) as MaxPrice,
LAST_VALUE(title) over (Order by price desc) as MaxName
from titles;

Getting this output

MINPrice    MINName                        Maxprice           MaxName
NULL       The Psychology of Computer        $22.95      But is it Friendly?
NULL       The Psychology of Computer        $21.59      Computer Phobic and

Where as the result I am expecting should be

Minprice     MinName                        Maxprice          Maxname           
$2.99        The Gourmet Microwave           $22.95       But is it Friendly?
$2.99        You can Combat stress          $21.59       Computer Phobic and

So how do I eliminate NULLs from Min price

Possible duplicate of [Last\_value with IGNORE NULLS in SQL Server](https://stackoverflow.com/questions/44893970/last-value-with-ignore-nulls-in-sql-server) — ahmed abdelqader, May 28 '19 at 02:42

score 0 · Answer 1 · edited May 28 '19 at 00:42

0

SELECT min(value) FROM table WHERE value IS NOT NULL.

It should be something like this.

edited May 28 '19 at 00:42

Dale K

25,246
15
42
71

answered May 28 '19 at 00:37

Jeremy O'Handley

15
7

score 0 · Accepted Answer · answered May 28 '19 at 01:48

You can try with

;WITH ctemin AS 
(
   SELECT TOP 2 price AS minprice, title AS mintitle FROM titles WHERE price IS NOT NULL ORDER BY price 
),
ctemax AS 
(
   SELECT TOP 2 price AS maxprice, title AS maxtitle FROM titles WHERE price IS NOT NULL ORDER BY price DESC
)

SELECT ctemin.minprice,ctemin.mintitle,ctemax.maxprice,ctemax.maxtitle FROM ctemax
INNER JOIN ctemin ON 1=1

FIRST_VALUE and LAST_VALUE with NULL values

2 Answers2