Wow, we have a winner there, Stephen!!!! Works great! I know for sure I don't use the 'top 1' command as often as I should!
Karen -----Original Message----- From: Stephen Markson <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Tue, Jun 2, 2015 10:05 am Subject: [RBASE-L] - Re: Need SQL help I think this is simpler and works: SELECT TOP 1 DateVersion,SeqNo INTO vDV,vSN + FROM ActionsTable + WHERE SuppID=.vSuppID + ORDER BY DateVersion DESC,SeqNo DESC Regards, Stephen Markson The Pharmacy Examining Board of Canada 416.979.2431 x251 From: [email protected] [mailto:[email protected]] On Behalf Of Karen Tellef Sent: June-01-15 5:58 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Need SQL help I was hoping you would chime in on this one! Didn't think about reversing it -- yes if both are DESC then the "last" row would always be at the top! I will test this tomorrow! Karen -----Original Message----- From: Bill Downall <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Mon, Jun 1, 2015 4:04 pm Subject: [RBASE-L] - Re: Need SQL help Karen, As long as "last" is determined by the sorting of the data, you can do this with SQL. If you were to sort by DateVersion DESC, SeqNum DESC, would your "last" rows always be on top? If so, here it is in pure SQL, I think: SELECT * FROM ActionsTable + WHERE SuppID = .vSuppID + AND SeqNo = + (SELECT MAX(SeqNo) + FROM ActionsTable t2 + WHERE SuppID = .vSuppID AND DateVersion = + (SELECT MAX (DateVersion) FROM ActionsTable t3 WHERE t3.SuppID = .vSuppID)) + AND DateVersion = + ( (SELECT MAX (DateVersion) FROM ActionsTable t4 WHERE t4.SuppID = .vSuppID)) (But you might want to do it in steps and store the MAX (DateVersion) in a variable, and then the MAX (SeqNo) for that MaxDateVersion in a variable.) Bill On Mon, Jun 1, 2015 at 4:47 PM, Karen Tellef <[email protected]> wrote: Trying to think of an easy way to do this. I have many rows per SuppID. When a form comes up that shows actions for the supplier, it sorts by DateVersion then by SeqNo. There is an autonumber PK field, but it cannot be relied on for being the exact order, nor are the SeqNo's sequential in this table. I am designing a report showing supplier information, and for some of the data I need to find the LAST action row for each supplier. I would be fine with defining a temp table before the report comes up, doing the calc in the table, then looking up data from the report. For example, the actions form would come up like this. I would want to grab the PK of the LAST row I'm showing DateVersion SeqNo 03/01/15 1 03/15/15 4 04/01/15 2 04/01/15 3 Any clues? Thanks! Karen
