October 25, 2014 at 12:39 pm
Comments posted to this topic are about the item FIRST_VALUE
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 27, 2014 at 1:00 am
Hi Steve,
I got the below answers in my SQL 2014 instance.. Could you please explain why this answer was incorrect...
Escobar 2014-04-06 00:00:00
Escobar 2014-04-07 00:00:00
Escobar 2014-04-07 00:00:00
Jeter 2014-04-01 00:00:00
Jeter 2014-04-01 00:00:00
Jeter 2014-04-01 00:00:00
Tulo 2014-04-01 00:00:00
Tulo 2014-04-01 00:00:00
Tulo 2014-04-01 00:00:00
______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂
October 27, 2014 at 1:49 am
Great question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 27, 2014 at 1:54 am
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...
Escobar 2014-04-06 00:00:00
Escobar 2014-04-07 00:00:00
Escobar 2014-04-07 00:00:00
Jeter 2014-04-01 00:00:00
Jeter 2014-04-01 00:00:00
Jeter 2014-04-01 00:00:00
Tulo 2014-04-01 00:00:00
Tulo 2014-04-01 00:00:00
Tulo 2014-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
October 27, 2014 at 2:12 am
Nice question Steve, checked on SQL SERVER 2012 working fine on that, do not know about 2014 thou.
October 27, 2014 at 2:59 am
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: 🙂
October 27, 2014 at 3:07 am
Nice one, excellent for Monday morning, thank you.
Iulian
October 27, 2014 at 3:11 am
This was removed by the editor as SPAM
October 27, 2014 at 5:11 am
Informative question & explanation, thanx.
October 27, 2014 at 5:32 am
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.
October 27, 2014 at 7:46 am
Thanks for the question. I learned something new today.
October 27, 2014 at 7:57 am
Excellent question. Thanks, Steve.
October 27, 2014 at 9:44 am
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!
October 27, 2014 at 12:19 pm
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.
October 27, 2014 at 1:03 pm
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.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply