topic Re: SElect.. statement in Application Development and Automation Discussions

SElect.. statement

Former Member — Mon, 31 Jul 2006 05:11:42 GMT

Hi Folks,

A transparent table contains 10000 student’s name and marks in it. Please write only one SELECT statement to pick top 10 students (having maximum marks).

Its an urgent requirement.

Thanks in Advance,

Re: SElect.. statement

Former Member — Mon, 31 Jul 2006 05:14:46 GMT

databse will be sorted by the key fields.

Hence select up to 10 records would give you the first 10 records(Sorted in ascending order by the key fields).

EX----

TABLES:MARA.
DATA: BEGIN OF ITAB OCCURS 0,
  MATNR LIKE MARA-MATNR,
  END OF ITAB.
  DATA: BEGIN OF ITAB1 OCCURS 0,
  MATNR LIKE MARA-MATNR,
  END OF ITAB1.


SELECT MATNR FROM MARA INTO TABLE ITAB1 .
SORT ITAB1 DESCENDING BY MATNR.

SELECT MATNR FROM MARA INTO TABLE ITAB UP TO 10 ROWS FOR  ALL ENTRIES IN ITAB1
                                                   WHERE MATNR = ITAB1-MATNR.
LOOP AT ITAB.
WRITE:/ ITAB-MATNR.
ENDLOOP.

Re: SElect.. statement

Former Member — Mon, 31 Jul 2006 05:17:54 GMT

hi Avinash,

write a select statement which selects all the entries of students with their marks in to one internal table....

then now sort that internal table in descending order using sort statment...

loop at that itab and collect the first 10 entries of the same

Re: SElect.. statement

former_member186741 — Mon, 31 Jul 2006 05:22:04 GMT

the requirement specifies to use ONE SELECT so I think you need something like:

data tm type table of mara with header

select * from mara up to 10 rows

into corresponding fields of table tm

order by brgew descending.

loop at tm.

write:/ tm-brgew.

endloop.

Obviously you will have to change it to use the 'student' table and the marks field but the technique is identical.

Re: SElect.. statement

Former Member — Mon, 31 Jul 2006 05:23:19 GMT

Kindly try this code:

SELECT marks FROM dbTable into Internal_Table.

SORT Internal_Table BY marks DESCENDING.

LOOP AT Internal_Table.
...
...
   IF SY-TABIX EQ 11.
      EXIT.
   ENDIF.
ENDLOOP.

Best regards,

Thangesh

Re: SElect.. statement

Former Member — Mon, 31 Jul 2006 05:29:59 GMT

Hi Avinash,

Use a Select Query and fill the internal table with all the records, then use,

SORT ITAB BY MARKS DESCENDING

and use

LOOP AT ITAB FROM 1 TO 10.

WRITE statement.

ENDLOOP..

Regards:-

Santosh.

Re: SElect.. statement

Former Member — Mon, 31 Jul 2006 05:30:56 GMT

Hi Avinash,

You can try this code:

select * from <database table> up to 10 rows

into corresponding fields of table <internaltable>

order by <field for marks> descending.

Reagrds

amit

Re: SElect.. statement

Former Member — Mon, 31 Jul 2006 05:33:22 GMT

Hi,

Use this query :

Select Marks

into table it_marks

up to 10 rows

from ZMARKS

order by Marks descending.

Best regards,

Prashant

Re: SElect.. statement

Former Member — Mon, 31 Jul 2006 05:46:40 GMT

Hi Avinash,

Hope the following logic helps you.



data : int_table  type table of table_name .
 
<b>
select * from table_name up to 10 rows 
         into table int_table 
         order by field_name descending. 
</b>

Table_name and the field_name has to be adjusted ,accordingto your requirement.

Regards,

Anirban.