FIRST_VALUE

  • 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?

  • 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.

  • 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/

  • 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

  • Thanks Tom for trying to justify my ignorance!!!

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

  • Thanks for the great question.

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

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