CODEWITHSUNDEEP
sql
Jornia Kabakum
Jornia Kabakum

Reputation: 463

Get First VALUE

I have table 

ID     VALUE     DATE
1      2         1.1.2013 
1      3         1.9.2013
1      3         1.4.2013
2      1         1.1.2013 
2      1         1.2.2013
3      3         1.3.2013
3      1         1.4.2013
3      3         1.5.2013

I need to get only first value for id and only one row per id.

I try this select:

SELECT ID,
       first_value(VALUE) over(PARTITION BY id ORDER BY DATE) f_value,
       first_value(DATE) over(PARTITION BY id ORDER BY DATE) f_date
FROM table1
GROUP BY ID

But I cannot use GROUP BY with FIRST_VALUE.

Upvotes: 0

Views: 117

Answers (2)

Gaurav Soni
Gaurav Soni

Reputation: 6346

SELECT ID,VALUE,DATE
FROM
--inline view will return the min date for each group based on ID
 (
   SELECT ID,
          value,
          date,
          MIN(DATE) over(PARTITION BY id) f_date
    FROM table1
  )
 WHERE f_date=date; --compare the date and fetch the row with min date

Upvotes: 0

Nazarii Bardiuk
Nazarii Bardiuk

Reputation: 4342

SELECT DISTINCT ID,
   first_value(VALUE) over(PARTITION BY id ORDER BY DATE) f_value,
   first_value(DATE) over(PARTITION BY id ORDER BY DATE) f_date
FROM table1

You don't need GROUP BY because there are already group by and order by in OVER function.

Upvotes: 1

Related Questions