SQL: How to select highest PK out of multiple records returned

Question

Hello all,

I have a Journal_Entry table, with a Primary Key of Journal_Entry_ID, and (among other columns) an Entry_Date column.

I'm trying to do a query that selects the most recent Entry_Date -- via a SELECT MAX(Entry_Date) -- but the problem is that the user may have logged more than one entry on a given date. So if the user logged a journal entry twice today, this SELECT statement could return more than one row because the same MAX Entry_Date has been logged more than once.

So what I'd like to do is, if the SELECT MAX statement returns more than one record, choose the record that has the highest Journal_Entry_ID of the ones returned.

Right now my query looks like this:

SELECT Journal_Entry_ID, Entry_Date
FROM Journal_Entry
WHERE Entry_Date = (SELECT MAX(Entry_Date) FROM Journal_Entry);

I'm using SQL SERVER. Any help would be greatly appreciated.

Thanks.

EDIT: I'm using SQL SERVER. Not My SQL as I had originally reported.

Answer 1

SELECT TOP 1 Journal_Entry_ID, Entry_Date
FROM Journal_Entry
WHERE Entry_Date = (SELECT MAX(Entry_Date) FROM Journal_Entry)
ORDER BY Journal_Entry_ID DESC;

Answer 2

SELECT TOP 1
       Journal_Entry_ID, Entry_Date
FROM Journal_Entry
ORDER BY Entry_Date DESC, Journal_Entry_ID DESC

Note that the WHERE clause is not necessary here since we are ordering by Entry_Date already.

Answer 3

Assuming Entry_Date is a Date/Time, wouldn't something like this work?

select top 1 Journal_Entry_ID
from JournalEntry
order by Journal_Entry_ID desc, Entry_Date desc

If Entry_Date is only a Date field (no time), is Journal_Entry_ID an auto-increment numeric field? If so, then maybe this:

select top 1 Journal_Entry_ID
from  JournalEntry
where Entry_Date = (select max(Entry_Date) from JournalEntry)
order by Journal_Entry_ID desc