topic Re: select query in Application Development and Automation Discussions

select query

Former Member — Mon, 11 Dec 2006 05:50:57 GMT

hi,

My requirement is to select records from a table based on a field (say abc )which has the max value.i.e. if field abc has value 2 & corresponding to this no. of records is 3 & the other value of abc is 4 which has the no. of records corresponding to it as 5 then i have to select first record with field abc = 4 & if two values of abc have equal no. of records then select whichever comes first.

pls guide me for the same.

Helpful answers will be rewarded.

Regards,

Sipra

Re: select query

Former Member — Mon, 11 Dec 2006 06:03:56 GMT

Hi Sipra

Please use a logic similar to below one:

select fld count( * ) into table itab
       from <table>
       where <cond>
       group by fld.
       
sort itab by count fld.

Kind Regards

Eswar

Re: select query

Former Member — Mon, 11 Dec 2006 06:27:03 GMT

try this.

data : v_abc like dbtab-abc.

data : itab like dbtab occurs 0 with header line.

select max( abc ) into v_abc from bdtab group by abc.

endselect.

select single * from dbtab into corresponding fields of itab where abc = v_abc.

append itab.

regards

shiba dutta

Re: select query

Former Member — Mon, 11 Dec 2006 06:35:17 GMT

Hi Eswar,

thnx but how to select record based on this field.

Pls help.

Re: select query

jayanthi_jayaraman — Mon, 11 Dec 2006 06:46:03 GMT

Hi,

If you need field's maximum value,then you need to use group by and max function as suggested.

If you need max no. of occurence of a particular field's value,thne use have to use count(*).

I think if you break the query rather than going for sub query,it would be easy.

Re: select query

Former Member — Mon, 11 Dec 2006 07:07:40 GMT

check this,


DATA : BEGIN OF ITAB OCCURS 0,
       F1 TYPE I,
       F2 TYPE I,
       END OF ITAB.

       ITAB-F1 = 2.
       ITAB-F2 = 3.
       APPEND ITAB.

       ITAB-F1 = 4.
       ITAB-F2 = 5.
       APPEND ITAB.

       ITAB-F1 = 2.
       ITAB-F2 = 7.
       APPEND ITAB.

SORT ITAB BY F1 DESCENDING F2 DESCENDING.
       LOOP AT ITAB.

       WRITE:/ ITAB-F1,
               ITAB-F2.

       ENDLOOP.

O/P IS

4 5 "selection priority to 4 first

2 7

2 3

NOW BY CHANGING SORT ITAB BY F2 DESCENDING F1 DESCENDING

TO SORT ITAB BY F1 DESCENDING F2 DESCENDING

CASE B.

DATA : BEGIN OF ITAB OCCURS 0,
       F1 TYPE I,
       F2 TYPE I,
       END OF ITAB.

       ITAB-F1 = 2.
       ITAB-F2 = 3.
       APPEND ITAB.

       ITAB-F1 = 4.
       ITAB-F2 = 5.
       APPEND ITAB.

       ITAB-F1 = 2.
       ITAB-F2 = 7.
       APPEND ITAB.

SORT ITAB BY F2 DESCENDING F1 DESCENDING.  "IF U WANT TO GIVE F2 PRIORITY
       LOOP AT ITAB.

       WRITE:/ ITAB-F1,
               ITAB-F2.

       ENDLOOP.

O/P IS 2 7 " if i make f2 priority

4 5

2 3

i think u r option is case A .

hope this helps just try to rearrange the format in ur value selection based on the sorttation .

regards,

vijay

like try to fetch the values of ur field ABC into an internal table as shown above and make a selection from the other table by using for all entries .

select ,,,,from XXXX

for all entries where f1 = itab-f1 .

is this case not possible in ur criteria.

the only thing is just the operation on the itab has to be sorted down .

hope this helps .

Message was edited by:

vijay k

Re: select query

Former Member — Mon, 11 Dec 2006 07:21:30 GMT

Hope this example can give you some idea:

tables: vbap.
select-options: s_aedat for vbap-aedat,
                s_vbeln for vbap-vbeln.

types: begin of t_data,
         vbeln type vbeln,
         cnt type i,
       end of t_data.
data: it_data type table of t_data,
      wa_data type t_data.
data: it_vbap type table of vbap.


select vbeln count( * ) into table it_data
       from vbap
       where aedat in s_aedat
       and   vbeln in s_vbeln
       group by vbeln.
sort it_data by cnt descending vbeln.

read table it_data into wa_data index 1.

select * into table it_vbap from vbap where vbeln = wa_data-vbeln.

Kind Regards

Eswar