topic Re: select query in Application Development and Automation Discussions

select query

Former Member — Fri, 29 Aug 2008 07:39:48 GMT

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

Re: select query

narin_nandivada3 — Fri, 29 Aug 2008 07:49:45 GMT

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

Re: select query

Former Member — Fri, 29 Aug 2008 07:51:41 GMT

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

Re: select query

RaymondGiuseppi — Fri, 29 Aug 2008 07:58:49 GMT

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

Re: select query

Former Member — Fri, 29 Aug 2008 11:08:11 GMT

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