Add data from recordset to array and sort by date

NJPhillips
NJPhillips used Ask the Experts™
on
I have a recordset, "rsTaskinfo" which has a field titled "StatusDateTime" which lists the date/time stamp a person updated their status. What I would like to do is find the last entry entry.... the kicker, is the recordset is already sorted by a persons name, otherwise it would be easy.

My thoughts were to add all of the records to an array and sort that way... but I am not sure if that is very efficient.

What say you experts?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Developer & EE Moderator
Fellow 2018
Most Valuable Expert 2013
Commented:
What tables and fields are in recordset rsTaskinfo?

Is rsTaskinfo made of one record per contact or many records per contact?  

I think it sounds like you have may records per contact.  In that case you could either update your view/query to a group by or maybe easier to think about is create a 2nd recordset that just has the contactID and StatusDateTime and make it a "group by" and get the max time.

Start with a new view something like below

SELECT     ID, MAX(StatusDateTime) AS LastUpdate
FROM         dbo.tby_tClass
WHERE     (ContactID = 'current_contactid')
GROUP BY ID

Then either  add this view to the view that makes up rsTaskinfo and link on ContactID to pick out which record has the most recent update or add the new recordset to your asp page and as you loop through rsTaskinfo, link on the contactID and for each record you will either get a null value or a match. If match, then that is the last updated record.

If you can post some sample fields and tables I can be more detailed.

Author

Commented:
Your solution is do-able, and was my first thought... I was just hoping to minimize trips to the DB is possible... however, it might really be more efficient to do that.

Thanks!
Scott FellDeveloper & EE Moderator
Fellow 2018
Most Valuable Expert 2013

Commented:
There is a more efficient way as far as the database to throw it into an array but the trade off is a little more complex code.  

Do you have 100,000's rows in the database?  Do you have 100's of people per minute accessing database? Does the query contain a lot of wild card searches?    I think what is "better" is personal preference and use.  If the actual usage is small, I think it is easier to go with the simple code.  

Without knowing what datafields you need it is hard to give you that option.  In short, you would create a dynamic array.


<%
' open rs
    Dim demoArray()
    Dim counter
     counter = 0
    do until rs.eof
   
        ReDim Preserve demoArray(i)
        DemoArray(counter) = rs.field
       if not rs.eof then
          counter = counter +1
      end if
    rs.movenext
   loop
'close rs
' now that you have your array, create a function to check the array with each row of data in your display

Function  functionName( checkID)
      matchID = 0
     for each x in demoArray
         if x = checkID then
            macthID = checkID
        end if
     next
end function
' the returned matchID can then be used to mark the record with the last update
%>

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial