SQL Statement

[Former Member]

Hello All,

KeyField1	KeyField2	KeyField3	date	Time	Field4
F11	F21	1	20150101	131000	F4A
F11	F21	2	20150201	104000	F4B
F11	F21	3	20150301	112000	F4C
F11	F21	4	20150301	172000	F4D
F12	F22	5	20150103	154000	F4L
F12	F22	6	20141012	045300	F4S
F12	F22	7	20150606	224000	F4C
F12	F22	8	20150509	113700	F4d
F13	...
F14	...
.......	...
.......	.....

I have above fields in my table.

User can give input of Key fields 'Key Field1' and 'KeyField2' .

For example user has passed

Key Field1 in('F11', 'F12')  and

Key Field2 in('F21', 'F22')

I require the latest one in these based on date and time i.e records highlighted in red with bigger font.

I know this can be done by getting the records with input key fields into internal table then sorting them by

KeyField1 Keyfield2 ASCENDING 

   Date Time Descending

and deleting adjacent duplicates comparing KeyField1 and KeyField2.

there are many similar way of dealing with this after bringing after bringing it into internal table.

Is there any way in Open SQL where we can get the exact required data(i.e the two records highlighted)

without getting additional records into application layer. The filtering sould occur at DB layer itself.

With regards,

Sandeep Akella

[Former Member]

I am afraid, you will have to go via the internal table processing way only.

Couldn't find a way to select the highlighted records via a SQL statement.

[Ankit_Maskara]

Hi,

You can try the subquery option.

write select as below -

select key_field1 key_field2 key_filed3 max(date) max(time) field4 from <table name>

into corresponding fields of table <tablename>

where key_field1 in ( select key_field1  where key_field1 in s_key_field1)

and     key_field2 in ( select key_field2  where key_field1 in s_key_field2).

Assumptions : Only one unique combination of key_field1 and key_field2 will exist as shown by your data sample.

Also i have denoted the input values via select options s_key_field1 and s_key_field2. The aggregation function works with Date and Time type fields.

BR.

[SwadhinGhatuary]

Hi,

A dummy code

TABLES vbap.

TYPES:BEGIN OF ty_vbap,
  vbeln TYPE vbeln,
  posnr TYPE posnr,
  END OF ty_vbap.

DATA  lt_vbap TYPE TABLE OF ty_vbap.

SELECT-OPTIONS s_vbeln FOR vbap-vbeln.

SELECT vbeln MAX( posnr ) FROM vbap
  INTO TABLE lt_vbap
  WHERE vbeln IN s_vbeln
  GROUP BY vbeln.

BREAK-POINT.

But I suggest go for Internal table to get Highlighted row instead  of aggregation function,.

[deepan_v_s]

Hello Sandeep,

You can use a subquery to solve this.

Select * from table where

     key1 = 'value' AND

     key2 = 'value' AND

     date IN (Select MAX (date) from table)

     into INTERNAL_TABLE.

And sort the resulting IT based on TIME and KEY fields and pick up the latest record based on Date and Time.

This approach will reduce the number of records and load on the DB. As far i researched this is a good approach.

If you have Timestamp field in the table then it can be done efficiently based on MAX (Timestamp).

Hope this helps !!!

Regards,

Deepan Swaminathan.

[Former Member]

It worked well for me.

Thank you.