LAST_VALUE() AND FIRST_VALUE() best example

Posted: October 12, 2014 in Netezza, Oracle
Scenario:-

Consider a Phone Log table as below. It records all phone numbers that we dial in a given day.

SOURCE_PHONE_NUMBER
DESTINATION_PHONE_NUMBER
CALL_START_DATETIME
1234
4567
01/07/2011 10:00
1234
2345
01/07/2011 11:00
1234
3456
01/07/2011 12:00
1234
3456
01/07/2011 13:00
1234
4567
01/07/2011 15:00
1222
7890
01/07/2011 10:00
1222
7680
01/07/2011 12:00
1222
2345
01/07/2011 13:00
 Please provide an SQL query to display the source_phone_number and a flag where the flag needs to be set to Y if first called number and last called number are the same and N if the first called number and last called number are different.
Desired Output:
Source Number Is_Match
1222 N
1234 Y
Solution:-
 SELECT source_number,CASE WHEN first_val=last_val THEN ‘Y’  ELSE ‘N’ END AS is_match  FROM (SELECT   source_phone_number AS source_number, First_value(destination_phone_number) over(PARTITION BY source_phone_number ORDER BY call_start_datetime) AS first_val, Last_value(destination_phone_number) over(PARTITION BY source_phone_number ORDER BY call_start_datetime)  AS last_val, Row_number() over(PARTITION BY source_phone_number ORDER BY call_start_datetime) AS id ) WHERE  id=1  FROM   phone_log ) WHERE id=1
 Can test query’s in the blow site:-                                                                                     
Advertisements
Comments
  1. karan says:

    Excellent solutions vamshi! This really helped me out in one of the scenarios I was facing in my project…

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s