Question Re: Optimized query in Technology Q&A

Optimized query

Former Member — Thu, 09 Feb 2006 12:06:09 GMT

Hi ,

I have table in the following format

USERID LDATE LTIME

0000000001 2006.02.09 172005

0000000001 2006.02.09 172055

0000000001 2006.02.09 172224

I need to get the users earlier logon date & time(Only single record). I this case I have to pick up the third record ie 0000000001 2006.02.09 172224

For getting this record how we can use SELECT Single or SELECT UPTO 1 Rows.

My WHERE condition will be like this,

where userid eq lv_userid and

ldate le sy-datum and

ltime lt sy-uzeit.

I Dont want to read all the record by SELECT ENDSELECT and then validate to pickup the latest one ( Since this table will contain huge number of records).

It would be great if anyone higlight a optimized select statement.

BR,

Raj

Re: Optimized query

Former Member — Thu, 09 Feb 2006 12:09:09 GMT

Hi Raja,

You can use sort statment by Comparing the log on time field. Then you delete the other unwanted entries. So u will have single record with earlier log on time.

Thanks,

Srinivas.

Re: Optimized query

Former Member — Thu, 09 Feb 2006 12:19:29 GMT

Hi raja,

1. U may have to use something like this.

SELECT EBELN MIN( AEDAT ) FROM EKKO

INTO CORRESPONDING FIELDS OF TABLE EKKO

GROUP BY EBELN AEDAT.

in your case.

SELECT USERID MIN( LDATE ) MIN( LTIME )

FROM DBTABLE

GROUP BY USERID.

3. U can insert your where conditions .

regards,

amit m.

Message was edited by: Amit Mittal

Re: Optimized query

Former Member — Thu, 09 Feb 2006 12:20:55 GMT

Hi,

When it is returning a single record selectsingle is preferred to select . The syntax of using selectsingle is similar to select statement except it will not have endselect.Tell me if you are not able to do can give you more information.

Regards,

Sudheer.

Re: Optimized query

Former Member — Thu, 09 Feb 2006 12:39:57 GMT

I would prefer sort the table based on the logon time in desending which will return the last logon time from which you can use select single which will retrieve the data required.

regards

kishore

reward if helpful

Re: Optimized query

Former Member — Thu, 09 Feb 2006 12:58:47 GMT

Hi,

Can you please highlight how to sort a database table by certain fields . I this case I have to sort my database table by date and time .

Regards,

Raj

Re: Optimized query

Former Member — Thu, 09 Feb 2006 13:13:06 GMT

Hi,

Goto SE11~~>utilities~~>database utility

click on the storage parameters push button

click on Indexes push button

there in the index definition U give the field

names and check the check box for DESCENDING

Award Points if this helps...

Regards,

GSR.