Re: [Beginner] Problem with Order by of a date

fmarchioni_at_libero.it wrote:
: Hi all,
: I'm building a query where I need to retrieve just 1 one row,
: which must be that with the highest Date (I mean that which
: is closest to the moment where the query is issued)

: SELECT a.lista_campi_tpr, date_import
: FROM CELL_PR cp
: WHERE cp.id_cella = 'PZ81U1'
: AND ROWNUM < 2
: ORDER BY date_import DESC

Rownum is assigned to each row _before_ the data is sorted. You are getting the first row that oracle finds, and then sorting it.

Three techniques come to mind...

Someone suggested using analytical functions, that is probably the best technique to use, at least it will be the most educational.

You can also use a query within a query

	select * 
	from
	( 
	  select stuff from the_table order by the_date desc
	)
	where rownum < 2
	;

Or PL/SQL can loop over the results, but exit the loop after the first row is returned. Some versions of Oracle allow a hint to return the first row as quickly as possible, which might make this efficient if the date is indexed as the first row could be found and returned straight away (as opposed to the hint that asks the total elapsed time of the query to be minimized).

--

This programmer available for rent.