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

From: HansF <News.Hans_at_telus.net>
Date: Tue, 08 Nov 2005 17:20:07 GMT
Message-Id: <pan.2005.11.08.17.20.04.216255@telus.net>

On Tue, 08 Nov 2005 09:04:55 -0800, fmarchioni 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
>
> The Date is date_import.......
> unfortunately that doesn't work..........
> anybody can help me ?
> Thanks
> Francesco

A few questions:

1. What version of Oracle are you running? Although that should not matter *much* here, I encourage you get into the habit of supplying that information.
2. Operating system and version. Same idea.
3. Describe the table or view - are you actually using a date column or just a column that happens to have the word 'date' in it?
4. Provide more detail about why [you think] the query does not work. Often providing the detail will give you your own hint to the solution.
5. Are you familiar with the concept of inline view - a subquery that is used in place of a 'table' in the FROM clause? Basically that can create an 'in memory, and optionally ordered virtual table' which can then be used as the source of your query. The following should get you on your way ...

SQL> create table test as select object_name, object_id from all_objects;

Table created.

SQL> select count(*) from test;

  COUNT(*)

---

     11500

SQL> select substr(object_name, 1, 30)
  2 from ( select object_name

  3             from test
  4            order by object_id desc)

  5 where rownum <2;

SUBSTR(OBJECT_NAME,1,30)

---

TEST SQL>

-- 
Hans Forbrich                           
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com   
*** Top posting relies guarantees I won't respond. ***

Received on Tue Nov 08 2005 - 11:20:07 CST