Latest time of a date in SQL

There is a column (REC_CREATE_TIMESTAMP) in a table which shows date in format YY-MM-DD. I would like select the latest (by time) of a certain date (let's say Aug 31, 2011). The first column should 开发者_运维知识库be the timestamp in hh24:mm:ss and the rest should be all columns (*) of this table. How do I do it?

Using Oracle 2.1.1.64

REC_CREATE_TIMESTAMP - NOT NULL - DATE

---

SELECT * FROM Table WHERE REC_CREATE_TIMESTAMP IN
(  
    SELECT
       MAX(REC_CREATE_TIMESTAMP) as REC_CREATE_TIMESTAMP
    FROM
       Table
    WHERE
       TO_CHAR(REC_CREATE_TIMESTAMP, 'YYYYMMDD') = '20110831'
)

EDITED for Oracle

---

SELECT *
FROM tableName As tbl1
WHERE EXISTS
(
  SELECT NULL
  FROM tableName as tbl2
  WHERE
    tbl2.rec_create_timestamp > '2001-08-30'
    tbl2.rec_create_timestamp < '2001-09-01'
  HAVING MAX(tbl2.rec_create_timestamp) = tbl1.rec_create_timestamp
)

---

For a generalised answer that can work with Sets of data rather than individual dates...

WITH
  sequenced_data AS
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY TRUNC(rec_create_timestamp) ORDER BY rec_create_timestamp DESC) AS daily_sequence_id,
    TRUNC(rec_create_timestamp) as rec_create_date,
    *
  FROM
    yourData
)
SELECT
  *
FROM
  sequenced_data
WHERE
  daily_sequence_id = 1
  AND rec_create_date = '20110831'