find recent date 3

[merlin72]

I am trying to do a query to find the most recent date.<br>Fields are name ,procedure , recertification date.<br>The name and procedure will be duplicate records only the dates will be different.<br>How can i get it to show only the records with the most recent date.

 

[ssingh]

Use Top qualifier . Put a field timestamp in the table

 

[merlin72]

Could you pleasev be more specific. I am new to sql's.<br>Thank's for replying

 

[CRoberts]

If you want the most current date, just use a Group By.&nbsp;&nbsp;SQL has a number of dialects, here is a solution for Oracle. Other databases has a similar functions, but are named differently.&nbsp;&nbsp;This is not the only way, but is, I believe, the easiest.<br><br>SELECT Name,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Procedure, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MAX(Recertification_Date) Recertification_Date<br>FROM&nbsp;&nbsp;&nbsp;Recentification_Table<br>GROUP&nbsp;&nbsp;BY Name, Procedure<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

 

[merlin72]

????? I did what you said and something is funny here. If i use max i get the past date. If i use min i get the most recent date.? Don't understand. All i am showing is the persons name, the procedure number (which are exactly them same with a different recertification date) and the recertification date.<br>Don't understand this.?<br>

 

[carp]

1.&nbsp;&nbsp;Are you using Oracle?&nbsp;&nbsp;It sounds like you are, but I'd like to confirm that.<br>2.&nbsp;&nbsp;Could you post the two dates you mentioned in your last post?<br><br>If you are using Oracle, I've found that NLS_DATE_FORMAT settings can give you nasty surprises like this, depending on your data and how you're querying it up.

 

[merlin72]

Using Acces 97. <br>It looks something like this.<br>&nbsp;&nbsp;&nbsp;&nbsp;Employee&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Procedure&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Date recertified<br>&nbsp;&nbsp;&nbsp;&nbsp;persons name&nbsp;&nbsp;&nbsp;anp-lb08&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;01/18/97<br>&nbsp;&nbsp;&nbsp;&nbsp;persons name&nbsp;&nbsp;&nbsp;anp-lb08&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;01/10/00<br>&nbsp;<br>&nbsp;I just want the up todate date.<br><br>&nbsp;Also, why when i add index id or any other field it throws <br>&nbsp;everything off. Than is all dates show up.<br>

 

[bigscouse]

select name, procedure, recdate<br>from rectable<br>where recdate in (<br>select max(recdate) <br>from rectable); <p>Ged Jones<br><a href=mailto:gedejones@hotmail.com>gedejones@hotmail.com</a><br><a href= > </a><br>