Fields of last 2 records

Using the solution here http://stackoverflow.com/questions/10314953/diff-value-last-two-record-by-datetime I am trying to find the values of the last 2 records,
but it turns out that it gives me the last and the first record.

Can anybody correct that for me?
Thank you


SELECT ORID, value1,value2
FROM (  SELECT h.ORID, h.DATETIME AS value1, h2.DATETIME AS value2
        FROM (  SELECT id, ORID, DATETIME
                FROM PAYMENT
                GROUP BY ORID
                ORDER BY ID DESC) AS h
        INNER JOIN (    SELECT id, ORID, DATETIME
                        FROM PAYMENT
                        ORDER BY ID DESC) AS h2
        ON h.ORID = h2.ORID AND h.id != h2.id AND h2.ORID=988
        GROUP BY ORID) AS h3
LVL 1
myyisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bernard S.CTOCommented:
Trying to understand the query.
First reactions:
- why h2.orid=988?
- why sorting on id? although this is probably correlating to datetime, it is datetime that should be used
- why the 2 group by, 1 should be enough
Can you test the result of
SELECT h3.ORID, h3.value1, h3.value2
FROM (
  SELECT h.ORID, MAX(h.DATETIME) AS value1, MAX(h2.DATETIME) AS value2
  FROM (
    SELECT ID, ORID, DATETIME
    FROM PAYMENT
    ORDER BY DATETIME DESC
    ) AS h
  INNER JOIN (
    SELECT ID, ORID, DATETIME
    FROM PAYMENT
    ORDER BY DATETIME DESC
    ) AS h2
  ON h.ORID = h2.ORID AND h.ID != h2.ID AND h2.ORID=988
  GROUP BY h.ORID
  ) AS h3 

Open in new window

0
myyisAuthor Commented:
Returned the same value

      ORID      value1                               value2
      988             2013-08-19 13:48:43      2013-08-19 13:48:43
0
Bernard S.CTOCommented:
I presume you have checked there are several datetime values for this ORID.


A slight change seems needed:

SELECT h3.ORID, h3.value1, h3.value2
FROM (
  SELECT h.ORID, MAX(h.DATETIME) AS value1, MAX(h2.DATETIME) AS value2
  FROM (
    SELECT ID, ORID, DATETIME
    FROM PAYMENT
    ORDER BY DATETIME DESC
    ) AS h
  INNER JOIN (
    SELECT ID, ORID, DATETIME
    FROM PAYMENT
    ORDER BY DATETIME DESC
    ) AS h2
  ON h.ORID = h2.ORID AND h.DATETME > h2.DATETIME AND h2.ORID=988
  GROUP BY h.ORID
  ) AS h3 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
myyisAuthor Commented:
Worked!
0
Bernard S.CTOCommented:
B-) Glad I could help. Thx for the grade and points
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.