CODEWITHSUNDEEP
mysql
Jared Meyering
Jared Meyering

Reputation: 1321

SQL to select max record that is not in the future

My table structure looks like this

tblclients

clientid
clientname

tblfacilities

facilityid
facilityname

tblclientfacilities

clientid
facilityid
moveindate

I allow my users to enter a future date as a move in date, what I want to do is select the current facility that my client resides in. I've tried

SELECT 
 clientid,
 facilityid,
 moveindate

FROM 
 tblclientfacilities
WHERE 
 MAX(tblclientfacilities.moveindate) <= now()
GROUP BY
  tblclientfacilities.clientid

Obviously that's not working. Could someone get me on the right track here? Thanks!

Upvotes: 1

Views: 96

Answers (5)

Quassnoi
Quassnoi

Reputation: 425341

SELECT  clientId,
        (
        SELECT  facilityId
        FROM    tblclientfacilities cf
        WHERE   cf.clientId = c.clientId
                AND cf.moveDate <= NOW()
        ORDER BY
                cf.clientId DESC, moveDate DESC
        LIMIT 1
        ) AS currentFacility
FROM    tblclients c

Create a unique index on tblclientfacilities (clientId, moveDate, facilityId) for this to work fast.

Upvotes: 1

pilcrow
pilcrow

Reputation: 58534

Join your client/facility/date records against the records of each client's most recent move-in:

    SELECT tcf.*
      FROM tblclientfacilities tcf
INNER JOIN (  SELECT clientid, MAX(moveindate) AS moveindate
                FROM tblclientfacilities
               WHERE moveindate < CURRENT_DATE
            GROUP BY 1, 2) most_recent_moveins
        ON tcf.clientid = most_recent_moveins.clientid
           AND
           tcf.moveindate = most_recent_moveins.moveindate)

Upvotes: 0

Barranka
Barranka

Reputation: 21047

You are confusing the usage of where with the usage of having.

A quick reference:

SELECT # Desired columns and expressions
FROM # Source tables, and possible joins
WHERE # Filter conditions on the raw data
GROUP BY # Fields you want your data to be grouped-by
HAVING # Filter conditions on the grouped data

Upvotes: 0

TheEwook
TheEwook

Reputation: 11117

If you use the group by clause consider using the having clause if you want to filter on the aggregated results

SELECT 
 clientid,
 moveindate,
 facilityid
FROM 
 tblclientfacilities
GROUP BY
  tblclientfacilities.clientid
HAVING MAX(tblclientfacilities.moveindate) <= now()

Doc: http://www.w3schools.com/sql/sql_having.asp

Upvotes: 0

Brad M
Brad M

Reputation: 7898

Try using HAVING for aggregate functions.

HAVING MAX(tblclientfacilities.moveindate) <= now()

Upvotes: 0

Related Questions