Query records for the date closest to today

[amillia]

I have thousands of records. 3 or 4 of each have like serial numbers. Of those 3 or 4 with like serial numbers I need to query out the one with the newest date. How do I do this?

 

[fredericofonseca]

more information needed, but you need to look at a select using the group and max function, with either an "exists" or a "in" or a "join"

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[r937]

select serialno
     , date_updated
     , other_stuff
  from yourtable as foo
 where date_updated
     = ( select max(date_updated) 
           from yourtable
          where serialno = foo.serialno )

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)

 

[PHV]

Another way:
SELECT A.*
FROM yourTable AS A INNER JOIN (
SELECT [serial number], Max([Date]) AS NewestDate
FROM yourTable GROUP BY [serial number]
) AS B ON A.[serial number] = B.[serial number] AND A.[Date] = B.NewestDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[amillia]

Okay, I tried this I am getting a syntax error in the FROM clause maybe because I am joining tables also. Here is the query:

SELECT tbl_bld.serial_NO, Tbl_MRPii.WorkCenter, Tbl_MRPii.WoNbr, Tbl_MRPii.OrgCode, Tbl_MRPii.StartDte, tbl_bld.TAT_START_DATE, tbl_bld.LETTER_DISTRIB_DATE, tbl_bld.TEST_POC
FROM tbl_bld as money, Tbl_MRPii as manuf
WHERE (((tbl_bld.TAT_START_DATE)>=10/1/2002) AND ((tbl_bld.LETTER_DISTRIB_DATE) Is Null) AND ((tbl_bld.TEST_POC)='Lori Price')) and (tbl_bld.WORK_ORDER_NUM = Tbl_MRPii.WoNbr) and (Tbl_MRPii.StartDte = (Select max(TBL_MRPii.StartDte) from tbl_MRPii where tbl_bld.cont_no = money.cont_no))

 

[PHV]

Money is a reserved keyword.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886