RE: SQL Max Statement Help

[Mark Leder]

G,


Thanks,
Your simplified code works except the display results still states that paid
thru is undefined.


Here's the current, working code:


SELECT M.firstName, M.middleName, M.lastName, M.email, M.company, M.city,
M.zip, M.memberID, M.memberLevelID, MAX(T.paidThru)
FROM #REQUEST.prefix#_Members_List M JOIN
#REQUEST.prefix#_Members_TransactionLog T ON M.memberID = T.memberID
GROUP BY M.firstName, M.middleName, M.lastName, M.email, M.company, M.city,
M.zip, M.memberID, M.memberLevelID

Thanks, Mark

  _____  

From: G [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 15, 2004 12:40 PM
To: CF-Talk
Subject: Re: SQL Max Statement Help

Hmmmm....just off the top of my head Mark (completely untested/unverified),
does this return what you want?

SELECT M.firstName, M.middleName, M.lastName, M.email, M.company, M.city,
M.zip, M.memberID, M.memberLevelID, max(T.paidThru)
FROM Membership M JOIN Transaction T ON M.memberID = T.memberID
GROUP BY M.firstName, M.middleName, M.lastName, M.email, M.company, M.city,
M.zip, M.memberID, M.memberLevelID

  ----- Original Message -----
  From: Mark Leder
  To: CF-Talk
  Sent: Tuesday, June 15, 2004 11:15 AM
  Subject: SQL Max Statement Help

  Hi All,

  Two tables I'm trying to query:
  1) Membership table, each with a unique "memberID"
  2) Transaction table, could be many records associated with one member ID
  (one-to-many).  Has a unique ID (transaction ID, along with "memberID"
  column(for joining) and a "paidThru" column.

  I want to query so that each memberID record will only call the most
recent
  "paidThru".  Trouble I'm having is I can't get the SELECT version to work
at
  all, the WHERE version brings back the correct record count, but I can't
  display the "PaidThru" either as a true column name, nor as an alias.

  Any ideas on how to get either one of these to work?

  Here's the select statement version: =====

    SELECT M.firstName, M.middleName, M.lastName, M.email, M.company,
M.city,
  M.zip, M.memberID, M.memberLevelID, T.memberID, T.paidThru, (SELECT
  MAX(T.paidThru) FROM #REQUEST.prefix#_Members_TransactionLog T) AS
paidThru

   
    FROM #REQUEST.prefix#_Members_List M,
  #REQUEST.prefix#_Members_TransactionLog T
   
    WHERE M.memberID = T. memberID

  Here's the WHERE statement version: =====

  SELECT M.firstName, M.middleName, M.lastName, M.email, M.company, M.city,
  M.zip, M.memberID, M.memberLevelID
  FROM #REQUEST.prefix#_Members_List M  

  WHERE EXISTS (SELECT MAX(paidThru) AS paidThruDate, memberID FROM
  #REQUEST.prefix#_Members_TransactionLog WHERE memberID = M.memberID GROUP
BY
  memberID)

  Thanks, Mark
  _____

---

[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]

---