distinct keyword

[Former Member]

Hi All,

can anyone tell me the use of keyword distinct with a simple example.

I did f1 on distinct , but i guess i din't understood the exact functionality

Thanks

[Former Member]

Hi Preeti,

SELECT [DISTINCT] <cols> ... WHERE ...

If you do not use DISTINCT (<lines> is then empty), the system reads all of the lines that satisfy the WHERE condition. If you use DISTINCT, the system excludes duplicate entries.

http://help.sap.com/saphelp_nw2004s/helpdata/en/fc/eb3990358411d1829f0000e829fbfe/content.htm

Regards

Sudheer

[Former Member]

Are you talking about DISTINCT in select statement

Duplicate entries in the result set are

automatically deleted.

TABLES SPFLI.

DATA TARGET LIKE SPFLI-CITYTO.

SELECT DISTINCT CITYTO

INTO TARGET FROM SPFLI

WHERE

CARRID = 'LH ' AND

CITYFROM = 'FRANKFURT'.

WRITE: / TARGET.

ENDSELECT.

[Former Member]

See the example :

data: begin of itab1 occurs 0,

lifnr like lfa1-lifnr,

end of itab1.

*i_bsik internal table for bsik.

data: begin of i_bsik occurs 0,

lifnr like bsik-lifnr,

dmbtr like bsik-dmbtr,

end of i_bsik.

• first condition for lifnr from tables lfa1 & lfb1.

select distinct a~lifnr into itab1-lifnr

from lfa1 as a

inner join lfb1 as b on a_{lifnr = b}lifnr

where a_{land1 ne 'xx' and b}bukrs between '1000' and '5151'

order by a~lifnr.

*write:/ itab1.

endselect.

select lifnr dmbtr from bsik

into corresponding fields of table i_bsik

for all entries in itab1

where lifnr = itab1-lifnr.

loop at i_bsik.

write:/ i_bsik-lifnr, i_bsik-dmbtr.

endloop.

*----

---

Reward Points if it is helpful

Thanks

Seshu

[Former Member]

Hi,

The DISTINCT clause allows you to remove duplicates from the result set. The DISTINCT clause can only be used with select statements.

The syntax for the DISTINCT clause is:

SELECT DISTINCT columns

FROM tables

WHERE predicates;

Example #1

Let's take a look at a very simple example.

SELECT DISTINCT city

FROM suppliers;

This SQL statement would return all unique cities from the suppliers table.

Example #2

The DISTINCT clause can be used with more than one field.

For example:

SELECT DISTINCT city, state

FROM suppliers;

This select statement would return each unique city and state combination. In this case, the distinct applies to each field listed after the DISTINCT keyword.

Regards,

Bhaskar

[Former Member]

the distinct addition is useful to select the unique result rows from the select statement .....for ex: if ur select stmt is

select distinct <result> from <source> into <target>........

the <result> is the fields u select from the source table like 'EKKO", 'SFLIGHT', ...etc

if two or more records have the same content for the fields retrived in <result> ...then only one row is fetched from the database...

for ex: select distinct carrid connid from sflight ino table itab.

for the above stmt if a particular "carrid and connid" are same it will only fetch one row but not the other rows from the table sflight.

[Former Member]

Hi

Just check this example where you have 4 fields f1, f2, f3 . Below is the value table.

Records:

A B C

B C D

A B D

Select distinct f1 from table...

you will get 2 records in the output i.e. record 1 and 2 as A is different from B.

select distinct f1 f2 from table...

you will get 2 records in the output i.e. record 1 and 2 as record 1 and 3 will be same (A and B)

select f1 f2 f3 ....

will give you all the 3 records, the third record has D which is different from C.

Hope this is clear

Regards

Navneet