locked
Creating index or filter to show just one record for each group RRS feed

  • Question

  • I know there must be an easy way to do this, but so far it evades me.

    I am working with a temp table that contains two fields.

    ID- C, 10

    Date- Datetime

    There may be multiple records with the same ID, but all will have different date/times.

    _AHYDGTRE1 11/28/2011 08:01:13

    _AHYDGTRE1 11/28/2011 08:23:09

    _AHYDGTRE1 11/28/2011 08:44:47

    _AHYDGTET0 11/28/2011 05:28:17

    _AHYDGTET0 11/28/2011 07:08:57

    I need to only show the most recent entry for each group of ID's.  This is a temporary free table.
    Any thoughts on the easiest way to only show the most recent record of each ID group in a grid?
    Thanks!


    Dick Day
    Tuesday, November 29, 2011 11:50 PM

Answers

  • SELECT Id, MAX(Date) AS MaxDate ;
      FROM TempTable ;
     GROUP BY Id ;
      INTO CURSOR cFiltered
    
    

    • Proposed as answer by Tom BorgmannEditor Wednesday, November 30, 2011 3:22 PM
    • Marked as answer by DickDay Wednesday, November 30, 2011 3:23 PM
    Wednesday, November 30, 2011 12:10 AM

All replies

  • SELECT Id, MAX(Date) AS MaxDate ;
      FROM TempTable ;
     GROUP BY Id ;
      INTO CURSOR cFiltered
    
    

    • Proposed as answer by Tom BorgmannEditor Wednesday, November 30, 2011 3:22 PM
    • Marked as answer by DickDay Wednesday, November 30, 2011 3:23 PM
    Wednesday, November 30, 2011 12:10 AM
  • Thank you so much, that is exactly what I needed. Have a great day!
    Dick Day
    Wednesday, November 30, 2011 3:20 PM