Compare Dates in MySQL where Dates are in the Same Table

Question

0.00/5 (No votes)

See more:

I am trying to compare two dates in the same table. I am trying to compare them to see which is the most recent date. I see the CAST() and MAX(), but I'm not sure as to how to compare them in the manner of which is the most recent.
We have a page in an internal system that displays events. We need to do a reporting page, but we only want to pull the most recent dates based on the item. If we have 3 different instances of that item with different dates, I would need to know its last known record based upon the date. First date field is to the customer and the other is from the customer. Using MAX() is pulls up a record and then sticks the last known date in the date field, which we do not want. The last known date will be displayed, but we are needing to compare the dates for that particular date and then pull the record with that date, not the other two records.

Posted 9-Oct-15 7:05am

Jonathan Cook

Updated 9-Oct-15 7:46am

v2

Add a Solution

Comments

PIEBALDconsult 9-Oct-15 13:42pm

You'll need to Improve the question -- add detail and context, maybe some simplified example data.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2015-10-09T07:55:00

Assuming you're using a DBMS that supports it, the ROW_NUMBER function[^] is what you're looking for:

SQL

WITH cteOrderedResults As
(
    SELECT
        SomeColumns,
        ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY TheDate DESC) As RN
    FROM
        YourTable
)
SELECT
    SomeColumns
FROM
    cteOrderedResults
WHERE
    RN = 1
;

NB: For future reference, always specify which DMBS you are using, including the version number. Also, it helps to include the basic structure of your table, and some example data. If possible, use SQLFiddle[^] to create a small sample of the problem.