Post reply

Table Self Join

  • June 23, 2008 at 7:45 am

    #193090

    Dear All

    I have a table as follows for a basic bid system

    SourceID / Prod ID / Bidder / Bid Value / Bid Date

    1 / AB / KIM / 2000/ 12-12-2008 12:19:12

    1 / AB / TIM/ 2000/ 12-12-2008 12:14:11

    2 / RC / JIM / 5000/ 12-12-2008 12:15:00

    1 / AB / JOE/ 2000/ 12-12-2008 12:16:00

    I'm trying to do a SQL to select the person who placed the first highest bid for a product (Since multiple ppl may have placed same bid value). I have tried self joins to no avail.

    For the data above I would want the result to be

    SourceID / Prod ID / Bidder / Bid Value / Bid Date

    1 / AB / TIM/ 2000/ 12-12-2008 12:14:11

    2 / RC / JIM / 5000/ 12-12-2008 12:15:00

    Any help would be extremely appreciated.

    Maby thanks in advance

    Peter

  • June 23, 2008 at 7:50 am

    #832675

    I would love to help, but I don't feel like typing in test data today since right in the newsletter is an article on how to post. Please read this:

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • June 23, 2008 at 8:02 am

    #832696

    I totally agree with Michael allthough he should use the URL format 😉

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • June 23, 2008 at 8:23 am

    #832727

    SELECTSourceID,

    ProdID,

    Bidder,

    BidValue,

    BidDate

    FROM(

    SELECTSourceID,

    ProdID,

    Bidder,

    BidValue,

    BidDate,

    ROW_NUMBER() OVER (PARTITION BY SourceID ORDER BY BidValue DESC, BidDate) AS RecID

    FROMTable

    ) AS d

    WHERERecID = 1

    N 56°04'39.16"
    E 12°55'05.25"

  • June 23, 2008 at 9:15 am

    #832788

    MANY MANY THANKS

Viewing 5 posts - 1 through 4 (of 4 total)

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