View Full Version : Advice needed!

05-15-2007, 02:08 PM
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

05-15-2007, 03:11 PM

select top 1 client order by date desc

05-15-2007, 03:22 PM
that would not work.....

05-15-2007, 03:49 PM
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/sqlhelp/latestfromgroup.shtml

05-15-2007, 04:30 PM

05-15-2007, 08:07 PM
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.