FIRST_VALUE

Question

Post reply

FIRST_VALUE

Steve Jones - SSC Editor

SSC Guru

Points: 734418
More actions
October 25, 2014 at 12:39 pm

#75700

Comments posted to this topic are about the item FIRST_VALUE
October 27, 2014 at 3:11 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

karthik babu Hall of Fame Points: 3335 More actions · Answer 1

Hi Steve,

I got the below answers in my SQL 2014 instance.. Could you please explain why this answer was incorrect...

Escobar2014-04-06 00:00:00

Escobar2014-04-07 00:00:00

Jeter2014-04-01 00:00:00

Tulo2014-04-01 00:00:00

______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 2

Great question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

karthik babu (10/27/2014)
Hi Steve,
I got the below answers in my SQL 2014 instance.. Could you please explain why this answer was incorrect...
Escobar2014-04-06 00:00:00
Escobar2014-04-07 00:00:00
Escobar2014-04-07 00:00:00
Jeter2014-04-01 00:00:00
Jeter2014-04-01 00:00:00
Jeter2014-04-01 00:00:00
Tulo2014-04-01 00:00:00
Tulo2014-04-01 00:00:00
Tulo2014-04-01 00:00:00

I'm not sure what you did, but I get the correct results.

CREATE TABLE #hits (team CHAR(3),player VARCHAR(15),hitdate DATE,hits TINYINT);

INSERT INTO #hits

VALUES ('NYY','Jeter' ,'2014-04-01',1)

,('NYY','Jeter' ,'2014-04-03',1)

,('NYY','Jeter' ,'2014-04-06',2)

,('COL','Tulo' ,'2014-04-01',1)

,('COL','Tulo' ,'2014-04-02',1)

,('COL','Tulo' ,'2014-04-03',2)

,('KC' ,'Escobar' ,'2014-04-06',1)

,('KC' ,'Escobar' ,'2014-04-07',1)

,('KC' ,'Escobar' ,'2014-04-08',2);

SELECT

player

,FIRST_VALUE(h.hitdate) OVER (PARTITION BY player ORDER BY hitdate)

FROM #hits AS h;

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Umer Akram SSC-Insane Points: 22209 More actions · Answer 4

Nice question Steve, checked on SQL SERVER 2012 working fine on that, do not know about 2014 thou.

karthik babu Hall of Fame Points: 3335 More actions · Answer 5

Its my bad... I inserted a space in one of the column...

Sorry guys..!! :satisfied:

______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 6

Nice one, excellent for Monday morning, thank you.

Iulian

Hany Helmy SSChampion Points: 13921 More actions · Answer 7

Informative question & explanation, thanx.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Ed Wagner SSC Guru Points: 287024 More actions · Answer 8

Thanks Steve. A good one to wake up the brain on a Monday morning. I learned something from it, so it's a good way to start the day.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Dana Medley SSCertifiable Points: 6764 More actions · Answer 9

Thanks for the question. I learned something new today.

Everything is awesome!

stephen.long.1 SSCrazy Points: 2582 More actions · Answer 10

stephen.long.1

SSCrazy

Points: 2582

October 27, 2014 at 7:57 am

#1754937

Excellent question. Thanks, Steve.

Kick6Tiger SSCrazy Points: 2380 More actions · Answer 11

Nice question. It had me stumped for a while since I was looking for an answer with 3 dates. It made me pay attention to the distinct values of the results.

Aigle de Guerre!

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 12

Nice reminder. I had actually completely forgotten that this function exists. (Perhaps for good reasons?)

If "syntax error, no such function exists" had been included in the answer options, I might have fallen for it.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 13

I'm not familiar with FIRST_VALUE. But I guessed it meant the first value that you run across. Since the data was partitioned by planer and ordered by date (for that player's dates) it seemed reasonable that it would display the first date for each player.