Getting first row

[paraglidersd]

I looked this up online, but all of the discussed techniques didnt work. I am using Sybase. I have a stored procedure. I want to run a query on a table that has millions of rows. I expect that my query (with some criteria) will return hundreds of possible answers. I only want the FIRST one (I am going to order it in ascending order and just want the newest one). What is the best way to do this???

thanks,
Bill

 

[PDreyer]

Newest one? Then you should sort desc

select top 1 mydate from mytable order by mydate desc

or

select max(mydate) from mytable

 

[paraglidersd]

Thanks for the response. The 'top' idea doesnt seem to work (syntax error). I should actually clarify what I want. We have a DB with millions of records. The query takes a long time. The query will run until it reads all of the records. What I want is for the query to return as SOON as it finds the first match. I tried using 'set rowcount 1' but that didnt seem to work. The query returned one record, but it took just as long to run as my original query so I assume that it is going through every record even after it finds a match. So, is there any other way?

 

[PDreyer]

The 'top' idea doesnt seem to work (syntax error).

You must be using a very old version then.
The order by will require a sort of all the data before it can select the first one.
It can help if you have an index on the date field
Here are some test from my ASE 12.5.4

1> select max(M_DATE) from TRNPL 
2> go
                            
 -------------------------- 
        May 30 2008 12:00AM 

(1 row affected)
Execution Time (ms.):      1            Clock Time (ms.):      1
1> select top 1 M_DATE from TRNPL order by M_DATE desc
2> go
 M_DATE                     
 -------------------------- 
        May 30 2008 12:00AM 

(1 row affected)
Execution Time (ms.):      1            Clock Time (ms.):      1
1> select count(*) from TRNPL 
2> go
             
 ----------- 
    25164227 

(1 row affected)
Execution Time (ms.):  19381            Clock Time (ms.):  19381
1> set rowcount 1
2> select  M_DATE from TRNPL order by M_DATE desc
3> set rowcount 0
4> go
 M_DATE                     
 -------------------------- 
        May 30 2008 12:00AM 

(1 row affected)
Execution Time (ms.):      2            Clock Time (ms.):      2

 

[paraglidersd]

Yeah, we are kind of stuck here in a government controlled project. They tend to be behind on versions of a lot of software packages. Thanks for your response. Looks like 'top' would be the answer to my situation. Thanks again, Bill

 

[level2programmer]

I think top is a Microsoft item. Try
Set rowcount 1
before the query and then
set rowcount 0
after the query.
Row count of 1 only returns 1 row.
Row count of 0 is back to normal, returns all rows.