This website is not affiliated with, sponsored by, or approved by SAP AG.

Select most recent record based on two columns

Development (ABAP Development WorkBench, ABAP/4 programming)

Moderators: Snowy, thx4allthefish, YuriT, Gothmog

Select most recent record based on two columns

Postby Eggy » Thu Oct 23, 2014 2:14 am

In my mind, this has to be possible using a single query, without branching off into sub-queries. Please correct my mind if wrong :)

I have a database table that contains testing scores. It consists of the following columns:
COMPANY, ID, DATE, TIME, SCORE (bold = key)

COMPANY is a given value. For this company I would like to retrieve the most recent testing scores for all available ID's.

Code: Select all
SELECT DISTINCT id
       MAX( date )
       MAX( time )
       score
       FROM  <table>
       INTO  <internal_table>
       GROUP BY date
                time
                score
       ORDER BY id ASCENDING.


Without the MAX( time ) part, this does return the correct amount of records, with indeed the highest available date. With the MAX( time ) part, it will return records for the same date for multiple times. Have been playing around with SELECTS, GROUPS, HAVING and sub-queries... although I'd hate to have to use those.

My guess it that it tries to group by a unique combination of all fields in the GROUP BY clause. And indeed the combination of the three is unique... but I just want the record with the highest date, when there are multiple with the highest time.

Can this be done in a single query? Thanks in advance!
Eggy
 
Posts: 309
Joined: Wed Jan 11, 2006 8:50 am

Re: Select most recent record based on two columns

Postby Grogan » Sun Oct 26, 2014 2:54 pm

This is a surprisingly difficult question.

My understanding is that when using aggregate expressions every result column must be either listed in the GROUP BY clause, or be the subject of an aggregate expression. So the code example you have shown above should give a syntax error, as ID is neither in the GROUP BY or aggregated.

You are correct that the system produces a group of records for each unique combination of values in the GROUP BY fields, and then for each group calculates the aggregate expressions.

So the best that a simple SELECT ID MAX( DATE ) MAX( TIME ) can give you is the maximum date and time for a group of records, regardless of whether the time corresponds with the date.

You would need to use some nasty looking sub-query, something like this:
Code: Select all
SELECT DISTINCT id date time score
  FROM zscore AS s1
  INTO TABLE lt_score
  WHERE date EQ ( SELECT MAX( s2~date )
                    FROM zscore AS s2
                    WHERE s2~id EQ s1~id )
    AND time EQ ( SELECT MAX( s3~time )
                    FROM zscore AS s3
                    WHERE s3~id   EQ s1~id
                      AND s3~date EQ s1~date )
  ORDER BY id ASCENDING.
Grogan

Award cash if useful.
Grogan
 
Posts: 365
Joined: Thu Aug 04, 2005 7:16 pm
Location: Australia

Re: Select most recent record based on two columns

Postby Eggy » Wed Oct 29, 2014 3:19 am

Thanks for the insight! The actual query is a bit longer, so I tried to simplify it to make the problem clear, might have missed the ID in the group by there. Or perhaps it worked because of the DISTINCT, which should group the ID anyway.

I think you're right that using the subquery is the only way of managing the required result in only "one" query. Was hoping for a more elegant solution ;)

What I've done to get around the issue is selecting all records (while still filtering as much as possible), ordering by id ASCENDING, date DESCENDING and time DESCENDING and after the query perform a DELETE ADJACENT DUPLICATES FROM <table> COMPARING id. That also leaves me with the most recent record for every id.
Eggy
 
Posts: 309
Joined: Wed Jan 11, 2006 8:50 am


Return to ABAP

Who is online

Users browsing this forum: No registered users and 10 guests



cron


loading...


This website is not affiliated with, sponsored by, or approved by SAP AG.