CODEWITHSUNDEEP
oracle-databaseoracle11g
Random guy
Random guy

Reputation: 923

What is the use of oracle first_Value analytical function?

I have a query which has first_value() as a analytic function.

SELECT 
    product_id, 
    product_name,
    list_price, 
    FIRST_VALUE(product_name) 
     OVER (ORDER BY list_price) first_product,
     category_id
FROM 
    ot.products
WHERE 
    category_id = 1;

The output I got was: enter image description here

Here I got 70 rows.

So,I tried new query as:

SELECT product_id, 
       product_name, 
       list_price 
FROM   ot.products 
WHERE  category_id = 1 
ORDER  BY list_price;

The output I got is:

enter image description here

In this query I again got 70 rows.So,I am not understanding what is the use of fisrt_value() in the first query?Since the data I am getting is same what is the use of this analytical function?

Upvotes: 0

Views: 536

Answers (1)

xQbert
xQbert

Reputation: 35323

So,I am not understanding what is the use of first_value() in the first query?

Well, it's simply listing the cheapest product on every record in your result set in the column called "First Product"

Since the data I am getting is same what is the use of this analytical function?

But it's different. you have a "First product column" in your first query. Maybe someone's using it for a comparison later on. Maybe it's to quickly link to the cheapest product within the result set later.... Can't say WHY someone coded it this way; but I can come up with a few guesses.

Note that 'Intel Core i7-5930K is the cheapest product in the result set; and thus gets listed on every other record in the result set due to the analytical function use.

Upvotes: 1

Related Questions