Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Nov 2006
    Location
    Bristol, UK
    Posts
    85
    Thanks
    6
    Thanked 6 Times in 6 Posts

    SQL Statement with DISTINCT data

    Hi All,

    I've got a SQL Server database with the following structure in a table called fieldlog:

    logID | DateVisited | DealerVisited
    1 | 20/10/2010 | 123456
    2 | 21/10/2010 | 123456
    3 | 21/10/2010 | 456789

    I'm trying to create a recordset that return the last visit to a particular dealer. I've tried using DISTINCT, MAX and a nested SELECT but so far haven't managed to get the right set of data out. It always only returns either one row or all 3.

    egs:
    Code:
    SELECT logID, DateVisited, DealerVisited FROM dbo.fieldlog WHERE DateVisited IN (SELECT MAX(DateVisited) FROM dbo.fieldlog GROUP BY DateVisited) ORDER BY DealerVisited ASC
    Code:
    SELECT logID, DateVisited, DealerVisited FROM dbo.fieldlog A WHERE DateVisited = (SELECT MAX(DateVisited) FROM dbo.fieldlog WHERE DateVisited = A.DateVisited) ORDER BY DealerVisited ASC
    Both of these still return all 3 rows...

    I'm sure it's something simple I'm missing but I'm getting a little close to this to notice.

    Can someone please help?

    Thanks.
    Never argue with an idiot... they'll bring you down to their level and beat you with experience...

  • #2
    Regular Coder
    Join Date
    Apr 2005
    Location
    Ohio
    Posts
    254
    Thanks
    1
    Thanked 63 Times in 63 Posts
    *Scrapped*. Flawed logic from my part.
    Last edited by shadowmaniac; 10-21-2010 at 11:40 PM. Reason: flawed logic

  • #3
    Regular Coder
    Join Date
    Jul 2010
    Posts
    149
    Thanks
    0
    Thanked 5 Times in 5 Posts

    DISTINCT with GROUP BY tale field

    Try below mysql query :

    SELECT DISTINCT DealerVisited, DateVisited, logID FROM dbo.fieldlog group by DealerVisited order by DateVisited desc

  • #4
    New Coder
    Join Date
    Nov 2006
    Location
    Bristol, UK
    Posts
    85
    Thanks
    6
    Thanked 6 Times in 6 Posts
    Thanks for the replies.

    I've got the answer now, tested and working.

    Code:
    SELECT t.logid, t.DealerVisited, t.DateVisited FROM ( SELECT DealerVisited, MAX(DateVisited) AS latest_visit FROM dbo.fieldlog GROUP BY DealerVisited ) AS m INNER JOIN dbo.fieldlog AS t ON t.DealerVisited = m.DealerVisited AND t.DateVisited   = m.latest_visit
    The offering from phpdeveloper will still return all rows as they all fall under the DISTINCT category.

    Thanks all for your time.
    Never argue with an idiot... they'll bring you down to their level and beat you with experience...


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •