Optimized query

[Former Member]

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

[Former Member]

HI

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

[Former Member]

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

[Former Member]

Hi,

Goto SE11><b>utilities</b>><b>database utility</b>

click on the <b>storage parameters</b> push button

click on <b>Indexes</b> push button

there in the <b>index definition</b> U give the field

names and check the check box for <b>DESCENDING</b>

Award Points if this helps...

Regards,

GSR.

[Former Member]

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.

[Former Member]

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

[Former Member]

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.