Need a SQL query to get a single, max date when the max date is not unique

My apologies if this has been asked and wasn't able to find this. I've searched for a long time, with no luck.

My table is named RESULT -

DATE                     TEST_NUM   RESULT_NUM
11/16/2010 09:27:11 AM   123456      123111      
11/16/2010 09:27:11 AM   123456      123222
11/16/2010 09:27:11 AM   123456      123333

For a given TEST_NUM, I only want to return the max date just one time, and it doesn't matter what the other column entries are -

11/16/2010 09:27:11 AM 123456 123111

SELECT  RESULT.DATE,
        RES开发者_StackOverflow社区ULT.RESULT_NUM,
        RESULT.TEST_NUM
 FROM RESULT
 WHERE RESULT.TEST_NUM = 123456 
 AND RESULT.DATE =  (SELECT  MAX(R1.DATE)
                      FROM RESULT R1
                      WHERE r1.TEST_NUM = RESULT.TEST_NUM)

But as you guessed, I don't get 1 result - I get all three. I've tried everything! Please help this newbie!

---

To get the maximum date:

SELECT MAX(RESULT.DATE) FROM RESULT

To get an entire row for any one of the rows that hold the maximum date:

SELECT * FROM
(
    SELECT *
    FROM RESULT
    WHERE RESULT.TEST_NUM = 123456
    ORDER BY RESULT.DATE DESC
)
WHERE rownum = 1

---

UPDATE : -

select max(DATE) from  RESULT where RESULT.TEST_NUM = 123456 group by RESULT.TEST_NUM

Here is an example -

select id,max (date_column) from your_table group by id

where id is the column you want the values for max date for, this will give you max date for each of the unique entries for id in that table out of the many date entries, since what I understood was that you have multiple dates and want the max.

---

Why won't this work

SELECT MAX(DATE) FROM TABLE

---

SELECT date_column FROM table GROUP BY date_column HAVING COUNT(*)>1 ORDER BY date_column DESC LIMIT 1

This will return the highest date_column ( ORDER BY date_column DESC ) that is not unique ( HAVING COUNT(*) > 1 )

---

select distinct max(date_column) 
into variable
from table
where condition;

Adding the distinct will ensure that if the data is the same then it will only return one record.