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 6 of 6

Thread: Advice needed!

  1. #1
    New to the CF scene
    Join Date
    May 2007
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Advice needed!

    I have one table with a field called prospective clients and sometimes the same client appears more than once, but with different dates
    (yes i know it should be two tables joined by SQl but im learning).

    Now at present all listings show i.e.
    ADZ 10/05/07
    ADZ 11/05/07
    ADZ 12/05/07

    now if client name appears more than once I only want to show the record with the latest date

    any ideas, I tried grouping them in Select statement, but would not work - would this be done in the Select statement or can it be achieved in an IF statement after the select?

    Need to convert "if client appears more than once, select client where date=date()" into proper ASP code

  • #2
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    maybe...

    select top 1 client order by date desc

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #3
    New to the CF scene
    Join Date
    May 2007
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    that would not work.....

  • #4
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Since this is more of a Last Record Per Group query, please see Guelphdad's post on this very subject. (Gueplhdad is a SQL master and can help out a lot with queries like these).

    Here's the post: http://guelphdad.wefixtech.co.uk/sql...romgroup.shtml

  • #5
    New to the CF scene
    Join Date
    May 2007
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks!

  • #6
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    Note that this could probably also be done with an inner join on itself rather than a subselect in the where clause. That may get your better performance on a large database.


  •  

    Posting Permissions

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