topic Re: Basic SQL question in Application Development and Automation Discussions

Basic SQL question

oliver_am — Mon, 27 Jul 2020 09:06:11 GMT

Hi, all

I have a table with following fields:

FIELD1 KEY
FIELD2 KEY
FIELD3 (no key but secondary key exists)
DATE
TIME

I would like to select all the records with last date and time grouping only by FIELD2.

I'm trying the following:

SELECT FIELD1 FIELD2 FIELD3 MAX( DATA ) MAX( TIME )
FROM MY_TABLE
WHERE FIELD3 EQ 'TEST'
GROUP BY FIELD2.

I have the following sintax error:

The field "FIELD1" from the SELECT list is is missing in the GROUP BY clause.

But I dont want to group by FIELD1, because can have different values for each FIELD2. I only want to get all the colums for each FIELD2 that have the max date and time.

Is it possible with a single select?

ABAP: 731

Thanks

Re: Basic SQL question

FredericGirod — Mon, 27 Jul 2020 09:08:45 GMT

why do you specify columns FIELD1, FIELD3 if you only want FIELD2 ?

Re: Basic SQL question

Sandra_Rossi — Mon, 27 Jul 2020 09:36:13 GMT

Please format your code with the button CODE.

Something like this?

SELECT CARRID, CONNID, FLDATE
  FROM SFLIGHT AS A
  WHERE EXISTS (
         SELECT B~CARRID FROM SFLIGHT AS B
         WHERE A~CARRID = B~CARRID
           AND A~CONNID = B~CONNID
         GROUP BY B~CARRID
         HAVING A~FLDATE = MAX( B~FLDATE ) )
  INTO TABLE @DATA(SFLIGHT_LINES).

Re: Basic SQL question

michael_piesche — Mon, 27 Jul 2020 10:05:23 GMT

GROUP BY will only select distinct value combinations of all attributes in the group by clause. Those attributes have to be part of the select clause and no aggregation (sum, max, average, ...) can be applied to those.
All other attributes in the select clause have to be part of an aggregation function (sum, max, average, ...)
If you only want to have distinct values for field2, all other selected attributes need to have an aggregation function. If an aggregation is not desired or not possible, those attributes (e.g. field1, field3) cannot be part of the select clause
Getting the latest timestamp for a field2 value:
Using the aggregation max(date) and max(time) seems to make little sense to me, as of right now. This will give you the field2 value with the latest date which I understand, but it will give you also the latest time of any date, which I dont understand. Most likely, you are trying to get the latest date+time of a field2 value, correct? But because two attributes are used for date and time, instead of one (e.g. timestamp), you need a slightly different approach.

Re: Basic SQL question

oliver_am — Mon, 27 Jul 2020 10:22:05 GMT

Thanks Sandra. I think this is what I need.
Freederic, I want all the columns, but only group by FIELD2

Re: Basic SQL question

FredericGirod — Mon, 27 Jul 2020 10:43:03 GMT

oliver.am, so I think the solution of sandra.rossi is the good one.

First select to get all the key needed, second, get the final result.

But, what would be the result if you have

Field1 Field2 Date

A A 01.01.2010

B A 02.01.2010

C A 02.01.2010

Because first select give Field 2: A Date: 02.01.2010

But field1 will have two possible values

Re: Basic SQL question

oliver_am — Mon, 27 Jul 2020 14:05:51 GMT

Hi, Frederic.
That's a good question, I guess depends on the order of the rows.
I'll check with functional team what we need to do in this case.

MAX is returning only one of these rows? or both with the MAX value?

Re: Basic SQL question

Sandra_Rossi — Mon, 27 Jul 2020 18:15:44 GMT

If you want to target someone, if this person has posted an Answer, use the button REPLY, if this person is the Original Poster of the question he/she will be automatically informed, otherwise copy/paste their hyperlinked name so that the person receives a warning (NB: @ doesn't work/but typing this character will suggest hyperlinked names).