[cfaussie] RE: SQL Query

[Leon Seremelis]

Title: Message

try:

 

SELECT     MAX(CHIPTRAN.TR_DATE), CHIPTRAN.SERIALNO, USERCARD.CARDSTATUS, USERCARD.CREATEDATE, USERCARD.HOLDNAME, USERCARD.HOLDID

FROM         CHIPTRAN 

                   

JOIN USERCARD ON CHIPTRAN.SERIALNO = USERCARD.SERIALNO

 

GROUP BY   CHIPTRAN.SERIALNO, USERCARD.CARDSTATUS, USERCARD.CREATEDATE, USERCARD.HOLDNAME, USERCARD.HOLDID

---

From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Clifton Steve
Sent: Friday, 19 March 2004 9:21 AM
To: CFAussie Mailing List
Subject: [cfaussie] SQL Query

Hey everyone,

 

i have a table (chiptran) containing 800,000 transactions and a user table (usercard) containing 3000 users.

 

i am trying to join the two tables and perform a query where ONLY the last transaction (tr_date) for each user is returned.

 

this query is failing miserably, but it gives you an idea what i am trying to do:

 

SELECT     MAX(CHIPTRAN.TR_DATE), CHIPTRAN.SERIALNO, USERCARD.CARDSTATUS, USERCARD.CREATEDATE, USERCARD.HOLDNAME, USERCARD.HOLDID
FROM         CHIPTRAN, USERCARD

WHERE   

 

does this make sense? any help appreciated.

 

steve c

---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
http://www.mxdu.com/ + 24-25 February, 2004

---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
http://www.mxdu.com/ + 24-25 February, 2004