topic Re: Order by in Application Development and Automation Discussions

Order by

Former Member — Thu, 26 Apr 2007 16:11:54 GMT

In this code :

DATA T100_WA TYPE T100.

SELECT * FROM T100

INTO T100_WA

WHERE SPRSL = SY-LANGU AND

MSGNR < '010'

ORDER BY PRIMARY KEY.

ON CHANGE OF T100_WA-ARBGB.

ULINE.

WRITE: / '**', T100_WA-ARBGB, '**'.

ENDON.

WRITE: / T100_WA-MSGNR, T100_WA-TEXT.

ENDSELECT.

What ll be the diff if we dont the order by clause.

And wat is the use of order by clause.

when exactly do we use it ?

Re: Order by

Former Member — Thu, 26 Apr 2007 16:17:55 GMT

HI ravinder,

We would get the records in a sorted order if we do it this way.

the problem in this approach is that it takes a lot of time.(Performance issue).

Regards,.

ravi

Re: Order by

Former Member — Thu, 26 Apr 2007 16:18:21 GMT

T100 stores all messages

When you use SORT BY all the key field values are sorted and displayed in the alphabetical order

if you don't use ORDER BY

they will come without order, as they are stored in table.

menas sorting will not be done.

reward points if useful

regards,

Anji

Re: Order by

Former Member — Thu, 26 Apr 2007 16:18:30 GMT

Hi,

Sorts the lines of the selection.

Syntax

... ORDER BY PRIMARY KEY |... <si> [ASCENDING|DESCENDING]...

Sorts the selection in ascending or descending order according to the primary key or the contents of the fields listed.

Regards,

Bhaskar

Re: Order by

Former Member — Thu, 26 Apr 2007 16:19:04 GMT

Records in a DB table are not necesarily stored in an "orderly" fashion - by the primary key, for example.

The ORDER BY clause allows you to sequence/order the returned DB records in a fashion/order that is most efficient for your application code. It simply reduces extra application code that you might need to write.... and it makes your application prog run faster. It is ALWAYS faster to have the DB records returned to the app prog in a pre-ordered fashion... rather than having the app prog do the ordering after receiving the records from the DB.

Re: Order by

Former Member — Thu, 26 Apr 2007 16:19:09 GMT

Ravinder,

Just press F1 on ORDER BY and i'm sure you will get the answer.

Regards,

Amey

Re: Order by

Former Member — Thu, 26 Apr 2007 16:19:58 GMT

Hi,

The ORDER BY clause is used to give the records in the sorted manner...

In the database table it is not necessary the records will be stored in the sorted order..

IN your case...There are just displaying the unique message IDs ...otherwise..

THE ON CHANGE OF ...will trigger for every change in the message id..

If it is is not sorted then it will show duplicate records in the output..

If it is sorted then it will not show duplicate records..

SELECT * FROM T100

INTO T100_WA

WHERE SPRSL = SY-LANGU AND

MSGNR < '010'

ORDER BY PRIMARY KEY.

ON CHANGE OF T100_WA-ARBGB.

ULINE.

WRITE: / '**', T100_WA-ARBGB, '**'.

ENDON.

WRITE: / T100_WA-MSGNR, T100_WA-TEXT.

ENDSELECT.

Thanks,

Naren

Re: Order by

Former Member — Thu, 26 Apr 2007 16:22:56 GMT

Order by is used for sorting.

then even the key Sort does the same job .

what is the diff b/w order by and sort ?

Re: Order by

Former Member — Thu, 26 Apr 2007 16:24:23 GMT

Order by operates on database table.

sort operates on internal tables.

But , order by is an obsolete statement and hence use sort statement on internal tables.

Regards,

Ravi

Re: Order by

Former Member — Thu, 26 Apr 2007 16:25:02 GMT

ORDER BY is performed by the Database server.

SORT BY is performed by the Application server.

Doing at the DB server is much faster.

Re: Order by

Former Member — Thu, 26 Apr 2007 16:29:57 GMT

Ravi,

<b>"But , order by is an obsolete statement "</b>

I hope this statement is not true. ORDER BY is part of SQL natively.

Where have you seen that SAP has made it obsolete in OpenSQL?