Avatar of myyis
myyis asked on

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
MySQL Server

Avatar of undefined
Last Comment
Bernard Savonet

8/22/2022 - Mon
Bernard Savonet

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

ASKER
myyis

Returned the same value

      ORID      value1                               value2
      988             2013-08-19 13:48:43      2013-08-19 13:48:43
ASKER CERTIFIED SOLUTION
Bernard Savonet

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
myyis

Worked!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Bernard Savonet

B-) Glad I could help. Thx for the grade and points