select query

[Former Member]

I have a table with composite primary key as follows

Field 1 Field2

AAA 01

AAA 02

AAA 03

BBB 01

BBB 02

Now I want to select records with max(field2) for a given field1, ie

AAA 03

BBB 02

One way would be to kae it eo an internal table sort by second column descending and take the first record, is there a more elegant way using the MAX clause of the open SQL

[Former Member]

You may use subquery for that

SELECT fielda fieldb INTO (wa_fielda, wa_fieldb)  
  FROM tablename AS t
  WHERE fieldb EQ ( SELECT MAX( fieldb ) 
    FROM tablename WHERE fielda = t~fielda )

Regards

[narin_nandivada3]

Hi,

Please check this thread to know how to do with MAX

SELECT Single FIELD1 MAX( FIELD2 ) from......

Hope this solves your issue.

Good luck

Narin

[Former Member]

hii

you can use query like

Select MAX(VERSION) from ztable into temp1
where OBJECT = temp1
group by version
ordey by version descending.

regards

twinkal

[RaymondGiuseppi]

You may use subquery for that

SELECT fielda fieldb INTO (wa_fielda, wa_fieldb)  
  FROM tablename AS t
  WHERE fieldb EQ ( SELECT MAX( fieldb ) 
    FROM tablename WHERE fielda = t~fielda )

Regards

[Former Member]

hi

select the data from database and put it into Internal table first

then

sort itab field1 field2 by descending

loop at itab.

at new

end at

endloop

Regards

Deva