topic Re: select statement in Application Development and Automation Discussions

select statement

Former Member — Mon, 04 Dec 2006 04:37:45 GMT

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

Re: select statement

anversha_s — Mon, 04 Dec 2006 04:39:19 GMT

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.

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

Re: select statement

jayanthi_jayaraman — Mon, 04 Dec 2006 04:43:05 GMT

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.

Re: select statement

Former Member — Mon, 04 Dec 2006 04:48:45 GMT

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

Re: select statement

jayanthi_jayaraman — Mon, 04 Dec 2006 04:51:30 GMT

Hi,

THen use delete adjacent duplicates in itab comparing marks.

Re: select statement

anversha_s — Mon, 04 Dec 2006 05:03:26 GMT

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