Display most recent date?

Hi,
I have a report I need to design which gets it's data form several tables in
my SQL database. There are roughly 2300 records which are displayed but for
some of them there could be people that display more than once, is there
anyway in that instance where I can just get it to bring back the most
recent date i.e
Mrs June Smith 03/04/2004
Mrs June Smith 15/07/2004
Mrs June Smith 12/09/2004 - Just bring back this record.
Is this possible?
Appreciate any help on this.
Thanks
DamonSELECT name, MAX(date)
FROM your_table
GROUP BY name
Jacco Schalkwijk
SQL Server MVP
"Damon" <nonsense@.nononsense.com> wrote in message
news:5YHLd.225$p86.30@.newsfe4-gui.ntli.net...
> Hi,
> I have a report I need to design which gets it's data form several tables
> in
> my SQL database. There are roughly 2300 records which are displayed but
> for
> some of them there could be people that display more than once, is there
> anyway in that instance where I can just get it to bring back the most
> recent date i.e
> Mrs June Smith 03/04/2004
> Mrs June Smith 15/07/2004
> Mrs June Smith 12/09/2004 - Just bring back this record.
> Is this possible?
> Appreciate any help on this.
> Thanks
> Damon
>|||Hi Damon you can use the max function. ie
select max(date),name
from table1
group by name
Tom
"Damon" wrote:

> Hi,
> I have a report I need to design which gets it's data form several tables
in
> my SQL database. There are roughly 2300 records which are displayed but f
or
> some of them there could be people that display more than once, is there
> anyway in that instance where I can just get it to bring back the most
> recent date i.e
> Mrs June Smith 03/04/2004
> Mrs June Smith 15/07/2004
> Mrs June Smith 12/09/2004 - Just bring back this record.
> Is this possible?
> Appreciate any help on this.
> Thanks
> Damon
>
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:503754
Hi,
Thank you for your reply. I can't seem to get it to work as I am
referencing 6 tables for this report. Here is my stored procedure, you will
have to excuse me, am not that proficient @. SQL.
SELECT MAX(dbo.tbl_support.s_contract_startdate) AS Expr1,
dbo.tbl_referral_name.rn_id, dbo.tbl_referral_name.rn_forename,
dbo.tbl_referral_name.rn_surname,
dbo.tbl_referral_add.ra_add1, dbo.tbl_referral_add.ra_add2,
dbo.tbl_support_provider.sp_company,
dbo.tbl_support.s_options,
dbo.tbl_support.s_contract_enddate, dbo.tbl_support.s_review_date,
dbo.tbl_officer.off_full_name
FROM dbo.tbl_referral_name INNER JOIN
dbo.tbl_referral_add ON dbo.tbl_referral_name.rn_id =
dbo.tbl_referral_add.ra_rn_id INNER JOIN
dbo.tbl_referral_info ON dbo.tbl_referral_add.ra_id =
dbo.tbl_referral_info.ri_ra_id INNER JOIN
dbo.tbl_support ON dbo.tbl_referral_info.ri_id =
dbo.tbl_support.s_ri_id INNER JOIN
dbo.tbl_support_provider ON dbo.tbl_support.s_sp_id =
dbo.tbl_support_provider.sp_id INNER JOIN
dbo.tbl_officer ON dbo.tbl_referral_info.ri_off_id =
dbo.tbl_officer.off_id
GROUP BY dbo.tbl_referral_name.rn_id, dbo.tbl_referral_name.rn_forename,
dbo.tbl_referral_name.rn_surname, dbo.tbl_referral_add.ra_add1,
dbo.tbl_referral_add.ra_add2,
dbo.tbl_support_provider.sp_company, dbo.tbl_support.s_options,
dbo.tbl_support.s_contract_startdate,
dbo.tbl_support.s_contract_enddate,
dbo.tbl_support.s_review_date, dbo.tbl_officer.off_full_name
Really appreciate the help.
Damon
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:928E029A-DE89-4033-A644-CB322E7EBECC@.microsoft.com...
> Hi Damon you can use the max function. ie
> select max(date),name
> from table1
> group by name
>
> Tom
> "Damon" wrote:
>
tables in
for|||You are grouping by tbl_support.s_contract_startdate which is what is
stopping you getting just the MAX date as grouping by will give you a record
for each different value.
I have removed the grouping for you in the code below.
If you find you are still getting too many records, try removing the
grouping for the other dates you have in the select statement and replacing
the selections with MAX also (ie MAX(tbl_support.s_contract_enddate) As
EndDate)
SELECT
MAX(tbl_support.s_contract_startdate) AS StartDate,
tbl_referral_name.rn_id,
tbl_referral_name.rn_forename,
tbl_referral_name.rn_surname,
tbl_referral_add.ra_add1,
tbl_referral_add.ra_add2,
tbl_support_provider.sp_company,
tbl_support.s_options,
tbl_support.s_contract_enddate,
tbl_support.s_review_date,
tbl_officer.off_full_name
FROM tbl_referral_name
INNER JOIN
tbl_referral_add
ON tbl_referral_name.rn_id = tbl_referral_add.ra_rn_id
INNER JOIN
tbl_referral_info
ON tbl_referral_add.ra_id = tbl_referral_info.ri_ra_id
INNER JOIN
tbl_support
ON tbl_referral_info.ri_id = tbl_support.s_ri_id
INNER JOIN
tbl_support_provider
ON tbl_support.s_sp_id = tbl_support_provider.sp_id
INNER JOIN
tbl_officer
ON tbl_referral_info.ri_off_id = tbl_officer.off_id
GROUP BY
tbl_referral_name.rn_id,
tbl_referral_name.rn_forename,
tbl_referral_name.rn_surname,
tbl_referral_add.ra_add1,
tbl_referral_add.ra_add2,
tbl_support_provider.sp_company,
tbl_support.s_options,
tbl_support.s_contract_enddate,
tbl_support.s_review_date,
tbl_officer.off_full_name
Hope this has helped.
Paula
"Damon" wrote:

> Hi,
> I have a report I need to design which gets it's data form several tables
in
> my SQL database. There are roughly 2300 records which are displayed but f
or
> some of them there could be people that display more than once, is there
> anyway in that instance where I can just get it to bring back the most
> recent date i.e
> Mrs June Smith 03/04/2004
> Mrs June Smith 15/07/2004
> Mrs June Smith 12/09/2004 - Just bring back this record.
> Is this possible?
> Appreciate any help on this.
> Thanks
> Damon
>
>|||What can you not get to work? That statement parses fine, and without
further information it is impossible to tell what is wrong with it. See
www.aspfaq.com/5006 for information on how to post a good test scenario.
Jacco Schalkwijk
SQL Server MVP
"Damon" <nonsense@.nononsense.com> wrote in message
news:UKILd.331$p86.195@.newsfe4-gui.ntli.net...
> Hi,
> Thank you for your reply. I can't seem to get it to work as I am
> referencing 6 tables for this report. Here is my stored procedure, you
> will
> have to excuse me, am not that proficient @. SQL.
> SELECT MAX(dbo.tbl_support.s_contract_startdate) AS Expr1,
> dbo.tbl_referral_name.rn_id, dbo.tbl_referral_name.rn_forename,
> dbo.tbl_referral_name.rn_surname,
> dbo.tbl_referral_add.ra_add1, dbo.tbl_referral_add.ra_add2,
> dbo.tbl_support_provider.sp_company,
> dbo.tbl_support.s_options,
> dbo.tbl_support.s_contract_enddate, dbo.tbl_support.s_review_date,
> dbo.tbl_officer.off_full_name
> FROM dbo.tbl_referral_name INNER JOIN
> dbo.tbl_referral_add ON dbo.tbl_referral_name.rn_id =
> dbo.tbl_referral_add.ra_rn_id INNER JOIN
> dbo.tbl_referral_info ON dbo.tbl_referral_add.ra_id =
> dbo.tbl_referral_info.ri_ra_id INNER JOIN
> dbo.tbl_support ON dbo.tbl_referral_info.ri_id =
> dbo.tbl_support.s_ri_id INNER JOIN
> dbo.tbl_support_provider ON dbo.tbl_support.s_sp_id =
> dbo.tbl_support_provider.sp_id INNER JOIN
> dbo.tbl_officer ON dbo.tbl_referral_info.ri_off_id =
> dbo.tbl_officer.off_id
> GROUP BY dbo.tbl_referral_name.rn_id, dbo.tbl_referral_name.rn_forename,
> dbo.tbl_referral_name.rn_surname, dbo.tbl_referral_add.ra_add1,
> dbo.tbl_referral_add.ra_add2,
> dbo.tbl_support_provider.sp_company, dbo.tbl_support.s_options,
> dbo.tbl_support.s_contract_startdate,
> dbo.tbl_support.s_contract_enddate,
> dbo.tbl_support.s_review_date, dbo.tbl_officer.off_full_name
> Really appreciate the help.
> Damon
>
> "Tom" <Tom@.discussions.microsoft.com> wrote in message
> news:928E029A-DE89-4033-A644-CB322E7EBECC@.microsoft.com...
> tables in
> for
>|||Paula you are an absolute star *****. I removed the grouping on the other
dates and included MAX as yyu mentioned and it worked perfectly.
Thank you very much
Damon
"PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
news:EB01F9E7-EEA7-4FEC-9DF4-58E242E763A9@.microsoft.com...
> You are grouping by tbl_support.s_contract_startdate which is what is
> stopping you getting just the MAX date as grouping by will give you a
record
> for each different value.
> I have removed the grouping for you in the code below.
> If you find you are still getting too many records, try removing the
> grouping for the other dates you have in the select statement and
replacing
> the selections with MAX also (ie MAX(tbl_support.s_contract_enddate) As
> EndDate)
> SELECT
> MAX(tbl_support.s_contract_startdate) AS StartDate,
> tbl_referral_name.rn_id,
> tbl_referral_name.rn_forename,
> tbl_referral_name.rn_surname,
> tbl_referral_add.ra_add1,
> tbl_referral_add.ra_add2,
> tbl_support_provider.sp_company,
> tbl_support.s_options,
> tbl_support.s_contract_enddate,
> tbl_support.s_review_date,
> tbl_officer.off_full_name
> FROM tbl_referral_name
> INNER JOIN
> tbl_referral_add
> ON tbl_referral_name.rn_id = tbl_referral_add.ra_rn_id
> INNER JOIN
> tbl_referral_info
> ON tbl_referral_add.ra_id = tbl_referral_info.ri_ra_id
> INNER JOIN
> tbl_support
> ON tbl_referral_info.ri_id = tbl_support.s_ri_id
> INNER JOIN
> tbl_support_provider
> ON tbl_support.s_sp_id = tbl_support_provider.sp_id
> INNER JOIN
> tbl_officer
> ON tbl_referral_info.ri_off_id = tbl_officer.off_id
> GROUP BY
> tbl_referral_name.rn_id,
> tbl_referral_name.rn_forename,
> tbl_referral_name.rn_surname,
> tbl_referral_add.ra_add1,
> tbl_referral_add.ra_add2,
> tbl_support_provider.sp_company,
> tbl_support.s_options,
> tbl_support.s_contract_enddate,
> tbl_support.s_review_date,
> tbl_officer.off_full_name
> Hope this has helped.
> Paula
> "Damon" wrote:
>
tables in
for