CODEWITHSUNDEEP
mysqlsql
Philip
Philip

Reputation: 3799

MySQL Query for finding a "LAST" row, based on two fields

I have the following MySQL table to log the registration status changes of pupils:

CREATE TABLE `pupil_registration_statuses` (
`status_id` INT(11) NOT NULL AUTO_INCREMENT,
`status_pupil_id` INT(10) UNSIGNED NOT NULL,
`status_status_id` INT(10) UNSIGNED NOT NULL,
`status_effectivedate` DATE NOT NULL,
PRIMARY KEY (`status_id`),
INDEX `status_pupil_id` (`status_pupil_id`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

Example data:

INSERT INTO `pupil_registration_statuses` (`status_id`, `status_pupil_id`, `status_status_id`, `status_effectivedate`) VALUES
    (1, 123, 1, '2013-05-06'),
    (2, 123, 2, '2014-03-15'),
    (3, 123, 5, '2013-03-15'),
    (4, 123, 6, '2013-05-06'),
    (5, 234, 2, '2013-02-02'),
    (6, 234, 4, '2013-04-17'),
    (7, 345, 2, '2014-02-01'),
    (8, 345, 3, '2013-06-01');

It is possible that statuses can be inserted, thus the sequence of dates does not necessarily follow the same sequence of IDs.

For example: status_id 1 might has a date of 2013-05-06, but status_id 3 might have a date of 2013-03-15.

status_id values are, however, sequential within any particular date. Thus if a pupil's registration status changes multiple times on one day then the last row will will reflect their status for that date.

It is necessary to find out a particular student's registration status on a particular date. The following query works for an individual pupil:

SELECT * 
FROM pupil_registration_statuses 
WHERE status_pupil_id = 123
    AND status_effectivedate <= '2013-05-06'
ORDER BY status_effectivedate DESC, status_id DESC
LIMIT 1;

This returns the expected row of status_id = 4

However, I now need to issue a (single) query to return the status for all pupils on a particular date.

The following query is proposed, but doesn't obey the "last status_id in a day" requirement:

SELECT *
FROM pupil_registration_statuses prs
    INNER JOIN (SELECT status_pupil_id, MAX(status_effectivedate) last_date
        FROM pupil_registration_statuses
        WHERE status_effectivedate <= '2013-05-06'
        GROUP BY status_pupil_id) qprs ON prs.status_pupil_id = qprs.status_pupil_id AND prs.status_effectivedate = qprs.last_date;

This query, however, returns 2 rows for pupil 123.

EDIT

To clarify, if the input is the date '2013-05-06', I expect to get the rows 4 and 6 from the query.

http://sqlfiddle.com/#!2/68ee6/2

Upvotes: 0

Views: 94

Answers (3)

Strawberry
Strawberry

Reputation: 33935

Is this what you're after?

SELECT a.*
  FROM pupil_registration_statuses a
  JOIN
     ( SELECT prs.status_pupil_id
            , MIN(prs.status_id) min_status_id
         FROM pupil_registration_statuses prs
         JOIN 
            ( SELECT status_pupil_id
                   , MAX(status_effectivedate) last_date
                FROM pupil_registration_statuses
               WHERE status_effectivedate <= '2013-05-06'
               GROUP
                  BY status_pupil_id
            ) qprs 
           ON prs.status_pupil_id = qprs.status_pupil_id 
          AND prs.status_effectivedate = qprs.last_date
        GROUP
           BY prs.status_pupil_id
     ) b
    ON b.min_status_id = a.status_id;

http://sqlfiddle.com/#!2/68ee6/7

(Incidentally, there's an ugly and undocumented hack for this kind of problem which goes something like this:

SELECT x.* FROM (SELECT * FROM prs WHERE status_effectivedate <= '2013-05-06' ORDER BY status_pupil_id, status_effectivedate DESC, status_id)x GROUP BY status_pupil_id;

...but I didn't tell you that! ;) )

Upvotes: 1

AK47
AK47

Reputation: 3797

I have changed where clause, please try it. 

SELECT *
    FROM pupil_registration_statuses prs
        INNER JOIN (SELECT status_pupil_id, MAX(status_effectivedate) last_date
            FROM pupil_registration_statuses
            WHERE Datediff(status_effectivedate, '2013-05-06') <= 0
            GROUP BY status_pupil_id) qprs ON prs.status_pupil_id = qprs.status_pupil_id AND prs.status_effectivedate = qprs.last_date;

EDIT

Try this

SELECT *
    FROM 

        (
            select status_pupil_id,max(status_id) as status_id from pupil_registration_statuses innr
            --where Datediff(dd,status_effectivedate, '2013-05-06') >= 0
            group by status_pupil_id

        )as ca 
        inner join pupil_registration_statuses prs on prs.status_id = ca.status_id
where   Datediff(dd,prs.status_effectivedate, '2013-05-06') >= 0

Upvotes: 0

Hector Ordonez
Hector Ordonez

Reputation: 1104

If I understood right, you want to...

1) Get 1 row per person.

2) Get the status changes from the specific day you manually input.

3) Get the last status changes from within the specific day.

If that's right, you need the query you already have ordering by date and then by id, just with a distinct.

SELECT DISTINCT on status_pupil_id *
FROM pupil_registration_statuses 
WHERE status_pupil_id = 123
    AND status_effectivedate <= '2013-05-06'
ORDER BY status_effectivedate DESC, status_id DESC

Upvotes: 0

Related Questions