Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

select statement

Former Member
0 Kudos
81

Hi All..

There is a data in a database Table about Marks of 100 Students.

I need to pick up the Least score from the table and also need to have second least score from the table..

How to write the Select Statement for this Requirement..???

Can anyone give me the syntax..????

Thanks and Regards

Pavan

1 ACCEPTED SOLUTION

anversha_s
Active Contributor
0 Kudos
57

hi,

1. select all hundred record to an internal tabel.

2. sort internaltable in ascending order.

3. read the index1 and index2.

4. tats ur requirement.

<b>eg code.</b>

select s_name s_marks from ztab

into table itab_marks.

sort itab by marks ascending.

*highest mark.

if itab_marks[] is not inital. "this chk is must

read table itab_marks index 1. "lowest

write itab_marks-name,

itab_marks-marks.

read table itab_marks index 2. "second lowest

write itab_marks-name,

itab_marks-marks.

endif.

rgds

Anver

5 REPLIES 5

anversha_s
Active Contributor
0 Kudos
58

hi,

1. select all hundred record to an internal tabel.

2. sort internaltable in ascending order.

3. read the index1 and index2.

4. tats ur requirement.

<b>eg code.</b>

select s_name s_marks from ztab

into table itab_marks.

sort itab by marks ascending.

*highest mark.

if itab_marks[] is not inital. "this chk is must

read table itab_marks index 1. "lowest

write itab_marks-name,

itab_marks-marks.

read table itab_marks index 2. "second lowest

write itab_marks-name,

itab_marks-marks.

endif.

rgds

Anver

0 Kudos
57

Thanks Anver and Jayant...

If there are more than 1 least score (say..Least score is 2.. If 5 guys have scored 2 Marks..), then how to retrive the least scores and second least scores..???

Please Help me out...

Regards

Pavan

0 Kudos
57

Hi,

THen use delete adjacent duplicates in itab comparing marks.

0 Kudos
57

hi,

chk this.

select s_name s_marks from ztab into itab_marks.

sort itab by marks asccending.

*add this statement
DELETE ADJACENT DUPLICATES FROM ITAB_MARKS COMPARING MARKS.

if itab_marks[] is not inital. "this chk is must

read table itab_marks index 1. "lowest

write itab_marks-name,
        itab_marks-marks.


read table itab_marks index 2. "second lowest

write itab_marks-name,
        itab_marks-marks.

endif.

rgds

Anver

jayanthi_jayaraman
Active Contributor
0 Kudos
57

Hi,

select min( marks ) from dbtable into v_min.

This will give the lease score.

Alternative way,

select * from db into table itab.

sort itab by marks asc

read table itab into wa index 1.--->Least score

read table itab into wa1 index 2.->second least score

Kindly reward points if it helps.