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
Avatar of Bernard Savonet
Bernard Savonet
Flag of France image

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

Avatar of myyis
myyis

ASKER

Returned the same value

      ORID      value1                               value2
      988             2013-08-19 13:48:43      2013-08-19 13:48:43
ASKER CERTIFIED SOLUTION
Avatar of Bernard Savonet
Bernard Savonet
Flag of France image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of myyis
myyis

ASKER

Worked!
B-) Glad I could help. Thx for the grade and points
MySQL Server
MySQL Server

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.

49K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo