FIRST_VALUE

Question

Post reply

FIRST_VALUE

Viewing 6 posts - 16 through 20 (of 20 total)

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

tabinsc SSCommitted Points: 1812 More actions · Answer 1

Still on 2008, so I have to still use rankings and such to do the same thing. It will be nice when we finally upgrade!

Tony
------------------------------------
Are you suggesting coconuts migrate?

rfreiberg001 Mr or Mrs. 500 Points: 533 More actions · Answer 2

Koen Verbeeck (10/27/2014)
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;

I'm getting the same results when using your code to insert the data. I'm returning 9 rows.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 3

rfreiberg001 (10/27/2014)
I'm getting the same results when using your code to insert the data. I'm returning 9 rows.

Nine rows sounds right, since there is no GROUP BY. But those nine rows should have just two distinct dates.

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/

TomThomson SSC Guru Points: 104773 More actions · Answer 4

rfreiberg001 (10/27/2014)
Koen Verbeeck (10/27/2014)
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;
I'm getting the same results when using your code to insert the data. I'm returning 9 rows.

This discussion interested me, although I had the right answer without running anything. So I tried running it. I get 9 rows using SQL 2014, and the same using SQL 2012. This is of course correct behaviour: one row for each row in the table, with the player name and the corresponding date. So 6 occurrences of 2014-04-01, and 3 of 2014-04-06. It tok me a long time to convince myself this was correct, probably because the idea there should only be three rows seemed so plausible until it clicked that there was no reason not to have one result row per source row.

Tom

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

Thanks Tom for trying to justify my ignorance!!!

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

Andre Ranieri SSCrazy Points: 2819 More actions · Answer 6

Andre Ranieri

SSCrazy

Points: 2819

January 26, 2015 at 3:24 pm

#1773248

Thanks for the great question.