[cfaussie] RE: SQL Query

[James Macpherson]

Title: Message

I might be completely off the mark here but

 

SELECT     TOP 1 CHIPTRAN.TR_DATE, CHIPTRAN.SERIALNO, USERCARD.CARDSTATUS, USERCARD.CREATEDATE, USERCARD.HOLDNAME, USERCARD.HOLDID
FROM         CHIPTRAN, USERCARD

WHERE   CHIPTRAN.SERIALNO = USERCARD.SERIALNO
ORDER BY CHIPTRAN.TR_DATE DESC

 

Should be okay in MS SQL Server

 

- J

 -----Original Message-----
From: Clifton Steve [mailto:[EMAIL PROTECTED]
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   CHIPTRAN.SERIALNO = USERCARD.SERIALNO
GROUP BY   CHIPTRAN.SERIALNO

 

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