Post reply

query for getting distinct id in contact log

  • February 16, 2009 at 3:26 pm

    #129120

    I have contact and contact_log tables. They can be joined via contactid. As you can imagine it is 1 to many relationship. What I want is to get "distinct" contactid based on the most recent contact date in contact_log table. Can any one help?

    Thanks,

  • February 16, 2009 at 4:34 pm

    #944904

    Please try:

    WITH TestCTE AS(SELECT contactID

    ,logDate

    ,ROW_NUMBER() OVER (PARTITION BY contactID

    ORDER BY logDate DESC) AS rowNumber

    FROM contact_log)

    SELECT contactID

    ,logDate

    FROM TestCTE

    WHERE rowNumber = 1

  • February 16, 2009 at 4:50 pm

    #944906

    [font="Verdana"]Thanks Milla. You read the question way better than I did, and I think your solution will be more appropriate.[/font]

  • February 17, 2009 at 6:29 am

    #945130

    Milla, thanks for the beautiful code. I appreciate it. By joining the contact table to the first select statement I got what I want.

    Bruce, I appreciate your help as well, but I could not have a chance to look at your idea (no matter it is good or not good). You took it down too fast 😉

    Thanks, guys. Good day!

  • February 17, 2009 at 7:14 am

    #945167

    When I copied Milla's code to a stored procedure, I needed to use a dynamic sql, then an error is raised: The identifier ... maximum is 128. I added a join statement to the code, so it became long enough to be over 128. Any smart idea?

    Never mind. I figured it out. I made a mistake of using double quotes instead of single quotes in dynamic sql.

    Also I was able to create a view instead of a named query. In this case I have a warning on OVER keyword, but I still could run it. Why? I don't know. I just tried and it worked. For anyone who is interested... 🙂

    Thanks guys...

  • February 17, 2009 at 8:53 am

    #945252

    Could you please post the code that you are trying to execute?

    This might help: http://www.sqlservercentral.com/Forums/Topic208737-8-1.aspx

  • February 17, 2009 at 9:06 am

    #945264

    Milla,

    Thanks so much again. Yes, the link helped and he found the same mistake as I did. 🙂

    Good day!

Viewing 7 posts - 1 through 6 (of 6 total)

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