Hi all, I'm trying to get the la 1

[kenjoswe]

Hi all,

I'm trying to get the latest data (Date) from a Oracle table connected as a linked server to SQL7
Example:

CUSTOMER_ID_ CUST_NAME CUST_ADRESS DATE
1 A B 01
1 B A 02
1 B A 03
1 C A 04

In this exampel should the correct answer be:
1,C,A,04

I have tried with

SELECT
CUSTOMER_ID_,CUST_NAME,CUST_ADRESS,MAX(DATE)
FROM CUSTOMERS
GROUP BY
CUSTOMER_ID_,CUST_NAME,CUST_ADRESS

What have I missed?

/Kent J.

 

[tlbroadbent]

Try something like this.

SELECT
CUSTOMER_ID, CUST_NAME, CUST_ADRESS, [DATE]
FROM CUSTOMERS AS c
WHERE [DATE]=
(SELECT MAX([DATE]) FROM CUSTOMERS
WHERE CUSTOMER_ID=c.CUSTOMER_ID)
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[kenjoswe]

Thanks!