How to pick First Record

[amerbaig]

My query returns 4 records, with one column is datetime. I want to get first record from the set of records. Query is

============ Query ==============
select Last_Modified, Service_Name, Service_Type_ID, Service_Subtype, Service_Status_Code, Active_Date
from service_history
where to_date (Last_Modified) <= '20-aug-01' and Service_Type_ID = 1845 and Service_Name = '03335100094'
and (Service_Status_Code in (3, 6, 9))
order by Service_Name, Last_Modified desc
=========== End Query ==========

If I include rownum = 1 then it doest not show accurate results as the required rownum is 4 rather then to be 1. Also I want to get record from that query with highest date.


Please help Amer

 

[johpje]

Amer,

There are a few ways to solve the problem
You can just add a phrase to the WHERE clause in which you specify the date to be the highest.

This is probably the shortest and easiest choice. but in case you want to try something else:

You could also use the Max() function in combination with a GROUP BY clause. This should be possible to include this in the above statement using the HAVING clause. Although it's the most complicated way, this should also be the most performant way if the select is carefully writen.

Third possibility is to make your query from above into a view, and just get the record with the highest date out of this view.

Regards,
Johpje

 

[sswartzl]

I usually just put the query into a subquery and apply the rownum outside of it. In your case:

select * from
(
select Last_Modified, Service_Name, Service_Type_ID, Service_Subtype, Service_Status_Code, Active_Date
from service_history
where to_date (Last_Modified) <= '20-aug-01' and Service_Type_ID = 1845 and Service_Name = '03335100094'
and (Service_Status_Code in (3, 6, 9))
order by Service_Name, Last_Modified desc
) a
where rownum = 1;

This forces the order by to run before the rownum is applied. Also, I think in 9i that you can just use your original query and it will work as you intend.

 

[amerbaig]

Error confronted --
ERROR at line 7:
ORA-00907: missing right parenthesis

If I remove order by then it runs fine. But I need to have order by. What to do?


Amer

 

[jee]

What version of Oracle do you have? The syntax of putting an Order By within an embedded view (the subquery within the From clause) was new with maybe 8.1.6 (not sure of the exact version). In earlier versions you get the error you are seeing.

I think you will have to use MAX (LAST_MODIFIED) in a subquery in the Where clause - if your version of Oracle doesn't support Order By in embedded views.

 

[amerbaig]

Yes, this is not working in Oracle 8.0.5 but working in 8.1.7

So whats solution in Oracle 8.0.5

Amer

 

[johpje]

You can:

or ommit the order by in the view, as it's not needed since you will get the wanted record with a Max() function anyway.

or order by date first and then get the first row (rownum=1) as you stated in your first post

Johpje