how to select the ID of the latest modified record

[christer99]

How would I select the ID of the latest modified record? I have tried max(OPMarketing.MID), but that just gives me the latest ID (MID) and not the ID of the latest modified record. Select TOP 1 will not working as I am looking for multiple rows (grouped by BCOID. Any help would be appreciated.

SELECT TOP 100 OPMarketing.MID , OPMarketing.BCOID, MAX(OPMarketing.MODIFYDATE) AS MM
FROM OPMarketing LEFT OUTER JOIN
Markets ON OPMarketing.MARKET = Markets.BCO
WHERE (OPMarketing.OPID IN ('216', '217')) AND quote > 0
GROUP BY OPMarketing.BCOID, OPMarketing.MID
ORDER BY MM DESC

 

[bborissov]

christer99,
It would be very helpful if when you ask questions put a simple data and desired result from that data. I don't know your Database so it is hard to me to understand how it is structured and what are the relations between tables in it.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[dhulbert]

Christer.

You could either use a sub select to pull out the
MAX(OPMarketing.MODIFYDATE)

then link this back to your report or have a look at the Rank command, you can then rank the results based on a criteria and select where rank = 1.


I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[r937]

SELECT O.MID 
     , O.BCOID
     , O.MODIFYDATE
  FROM OPMarketing as O
LEFT OUTER 
  JOIN Markets as M
    ON M.BCO = O.MARKET
 WHERE O.OPID IN ('216','217')
   AND O.quote > 0
   AND O.MODIFYDATE =
       ( select max(MODIFYDATE)
           from OPMarketing
          where OPID IN ('216','217')
            and quote > 0
            and BCOID = O.BCOID )
ORDER 
    BY O.MODIFYDATE DESC

r937.com | rudy.ca