Selection Screen

[Former Member]

What is the use of "Order By" in a select statement? could you tell me small answers

[Former Member]

As I understand, order by sets the "sort by" of selected table.

SELECT - sort_key

Syntax

... ORDER BY { {PRIMARY KEY}

| { {col1|a1} [ASCENDING|DESCENDING]

{col2|a2} [ASCENDING|DESCENDING] ...}

| (column_syntax) } ... .

Alternatives:

1. ... ORDER BY PRIMARY KEY

2. ... ORDER BY {col1|a1} [ASCENDING|DESCENDING]

{col2|a2} [ASCENDING|DESCENDING] ...

3. ... ORDER BY (column_syntax)

Effect:

The addition ORDER BY sorts the resulting set by the content of the specified column. The order of the rows in the result set refers to all columns that are not listed after ORDER BY, is undefined, and can be different in repeated executions of the same SELECT statement.

The addition ORDER BY can only be used with the addition PRIMARY KEY at the same time as the addition FOR ALL ENTRIES of the WHERE condition.

Note:

If a sorted resulting set is assigned to a sorted internal table, the internal table is sorted again according to the sorting instructions.

Alternative 1

... ORDER BY PRIMARY KEY

Effect:

If all columns are specified (by the entry of * after SELECT), and a single database table is specified after FROM (rather than a view or a join expression), the addition PRIMARY KEY can be used to sort the resulting set in ascending order according to the content of the primary key of this database table.

The addition PRIMARY KEY cannot be specified if a view or a join expression is statically specified after FROM. If a view or a join expression is specified after FROM in the dynamic specification dbtab_syntax, the data is sorted by all columns of the resulting set.

Alternative 2

... ORDER BY {col1|a1} [ASCENDING|DESCENDING]

{col2|a2} [ASCENDING|DESCENDING] ...

Effect:

For any column specifications after SELECT, a list of columns can be entered after ORDER BY, by which the resulting set should be sorted. Only columns that are listed after SELECT can be entered. Columns can be specifieddirectly using the column names col1 col2 ..., or the alternative column names a1 a2 .... The latter is required if you want to sort by columns that are specified as aggregate expressions.

The additions ASCENDING and DESCENDING determine whether the rows are sorted in ascending or descendding order. If neither addition is specified, the sorg is performed in ascending order. The priority of sorting is based on the order in which the components col1 col2... or a1 a2 ... are specified.

Pooled and cluster tables cannot be sorted by all types of column. Columns specified after ORDER BY cannot be of the type LCHAR, LRAW, STRING, or RAWSTRING.

Note:

If single columns are specified in the addition ORDER BY, the statement SELECT bypasses the SAP buffering.

Alternative 3

... ORDER BY (column_syntax)

Effect:

As an alternative to static column specification, a bracketed data object column_syntax can be specified, which either contains the syntax of the list of columns or is initial when the statement is executed. The addition PRIMARY KEY cannot be specified in column_syntax. For column_syntax, the same applies as for the dynamic specification of columns after SELECT. If the content of column_syntax is initial, the addition ORDER BY is ignored.

Note:

For performance reasons, sorting should only be performed in tha databases system if the sort is supported by an index.

Example:

Selecting the database table SFLIGHT in a method, whereby the sort criterion is transferred as an input parameter. In this case, the user must enter the criterion using the correct syntax on the selection screen/>. In a proper application, you would normally prepare an input help using a selection list.

TYPES sflight_table_type TYPE TABLE OF sflight.

CLASS handle_sflight DEFINITION.

PUBLIC SECTION.

CLASS-METHODS select_sort_sflight

IMPORTING sort_crit TYPE string

EXPORTING sflight_tab TYPE sflight_table_type

RAISING cx_sy_dynamic_osql_error.

ENDCLASS.

PARAMETERS p_sort(40) TYPE c.

DATA: s_sort TYPE string,

result_tab TYPE sflight_table_type.

TRY.

s_sort = p_sort.

handle_sflight=>select_sort_sflight(

EXPORTING sort_crit = s_sort

IMPORTING sflight_tab = result_tab ).

CATCH cx_sy_dynamic_osql_error.

MESSAGE `Wrong sort criterium!` TYPE 'I'.

ENDTRY.

CLASS handle_sflight IMPLEMENTATION.

METHOD select_sort_sflight.

SELECT *

FROM sflight

INTO TABLE sflight_tab

ORDER BY (sort_crit).

ENDMETHOD.

ENDCLASS.

Message was edited by:

LOS'

[Former Member]

could you tell me ur cel no... plz

[Former Member]

sms me ur cel no

mine 9886635583

[Madhu2004]

it is nothing but applying the sort rule.

[Former Member]

Hi Sahil,

Chk this excellent Link.

http://www.samspublishing.com/library/content.asp?b=STY_Sql_24hours&seqNum=77&rl=1

Rgds

Reshma

[Former Member]

SELECT ORDERED statement (select_ordered_statement:_searched)

The SELECT ORDERED statement ( select_ordered_statement:_searched ) selects the first or last row, or, in relation to a certain position, the next or previous row in an ordered table. The order is defined by a key or by an index. The position is defined by specified key values and index values.

Syntax

<select_ordered_statement:_searched> ::=

<select_ordered_format1:_searched> | <select_ordered_format2:_searched>

<select_ordered_format1:_searched> ::= SELECT <FIRST | LAST> <select_column>,...

INTO <parameter_spec>,... FROM <table_name>

[<pos_spec>] [<where_clause>] [<lock_option>]

<select_ordered_format2:_searched> ::= SELECT <NEXT | PREV> <select_column>,...

INTO <parameter_spec>,... FROM <table_name>

[<index _ pos _ spec>] KEY <key _ spec>,... [<where_clause>] [<lock_option>]

<pos _ spec> ::= INDEX <column _ name> | INDEXNAME <index _ name>

| <index _ pos _ spec> [KEY <key _ spec>,...] | KEY <key _ spec>,...

select_column, parameter_spec, table_name, where_clause, lock_option, index_pos_spec, key_spec, column_name, index_name

Explanation

The select ordered statement is used to access the first or last row of an order defined by the key or a secondary key, or to access the previous or next row starting at a specified position. For tables defined without key columns, there is the implicitly generated column SYSKEY CHAR(8) BYTE which contains a key generated by the database system. The table column SYSKEY, therefore, can be used in the select ORDERED statement to access a specific table row. In a table, the order defined by the ascending values of SYSKEY corresponds to the order of insertions made to the table.

If no index name is specified with INDEX <column_name> or INDEXNAME <index_name> and no index position is specified with ( index_pos_spec ), the order is defined by the key.

If an index name is specified with INDEX <column_name> or INDEXNAME <index_name> or an index position is specified with, the order is defined by the secondary key and the key. The ascending key order then is the second sort criterion.

[Former Member]

HI

<b>ORDER BY PRIMARY KEY</b>

<b>Effect: </b>

If all columns are specified (by the entry of * after SELECT), and a single database table is specified after FROM (rather than a view or a join expression), the addition PRIMARY KEY can be used to sort the resulting set in ascending order according to the content of the primary key of this database table.

The addition PRIMARY KEY cannot be specified if a view or a join expression is statically specified after FROM. If a view or a join expression is specified after FROM in the dynamic specification dbtab_syntax, the data is sorted by all columns of the resulting set.

data: itab type standard tabel of mara. 
select * from  mara into itab order by matnr.

regards

ravish

<b>plz dont forget to reward if useful</b>

[Former Member]

Hi,

Check this link

regards,

Omkar.