topic select distinct in Application Development and Automation Discussions

select distinct

Former Member — Mon, 13 Oct 2008 08:46:31 GMT

Dear experts,

I have an internal table called i_maintable.

i_maintable has records assuming

5168

5172

How to count distinct elements (most optimally):

Ex in this case 2.

Re: select distinct

JozsefSzikszai — Mon, 13 Oct 2008 08:50:24 GMT

one possible way:

SORT itab.
DELETE ADJACENT DUPLICATES FROM itab.
distinct_values = lines( itab ).

Re: select distinct

Former Member — Mon, 13 Oct 2008 08:51:48 GMT

Another method.

DATA count TYPE i.
sort itab by ebeln.
LOOP AT itab.
  
  at NEW ebeln.
    count = count + 1.
  endat.
ENDLOOP.

WRITE count.

Regards

Sathar

Re: select distinct

Former Member — Mon, 13 Oct 2008 08:52:11 GMT

Hi,

Loop at i_maintable into wa_maintable.

at new field1.

count = count + 1.

endat.

endloop.

or else while extracting from the data base table use select query to get the count.

for Eg: Select Distinct (field1) count.............

With Regards,

Dwaraka.S

Re: select distinct

Former Member — Mon, 13 Oct 2008 08:55:05 GMT

Hi,

Take care of a few things :

1. Make sure that your itab is sorted in ascending order of the key.

2. Then loop at itab, compare the current key value with the previous key value and keep incremementing until they differ.

3. Remember to use field symbol instead of work area.

regards,

Advait.

Re: select distinct

Former Member — Mon, 13 Oct 2008 08:56:49 GMT

SORT itab by number.
DELETE ADJACENT DUPLICATES FROM itab comparing number.
data l1 type i.
describe TABLE itab lines l1.

now l1 will be 2.

Re: select distinct

Former Member — Mon, 13 Oct 2008 09:08:18 GMT

Hi Aditya,

You can use the following code snippet to count the distinct elements:

DATA: l_index TYPE sy-tabix.

SORT i_maintable BY <fieldname>.

DELETE ADJACENT DUPLICATES FROM i_maintable.

LOOP AT i_maintable INTO wa_maintable.

l_index = sy-tabix.

ENDLOOP.

after this loop, you can use the value of l_index as the number of distinct elements in your i_maintable.

To use the above code snippet this you need to make a work area also for i_maintable.

Hope it will help you,

Regards,

Nikita

Re: select distinct

Former Member — Mon, 03 Nov 2008 09:02:43 GMT

asas