get the latest changed records

[varmamkm]

Hi,

I hava a table with the following information
[code=sql]
CREATE TABLE TEMP1 (REFID INT, REVISION INT, FIELDNAM VARCHAR(10), VALUE VARCHAR(10));
INSERT INTO TEMP1 VALUES(1001, 0, 'A', 'A2');
INSERT INTO TEMP1 VALUES(1001, 0, 'C', 'C2');
INSERT INTO TEMP1 VALUES(1001, 0, 'E', 'E2');
INSERT INTO TEMP1 VALUES(1002, 0, 'A', 'A3');
INSERT INTO TEMP1 VALUES(1002, 0, 'B', 'B2');
INSERT INTO TEMP1 VALUES(1002, 0, 'E', 'E3');
INSERT INTO TEMP1 VALUES(1001, 1, 'A', 'A4');
INSERT INTO TEMP1 VALUES(1001, 1, 'E', 'E4');
[/code]
Here based on latest revision and refid I should get the fieldnam and value.
Expected output:
REFID FIELDNAM VALUE REVISION
1001 A A4 1
1001 E E4 1
1002 B B2 0
1001 C C2 0

[amitpatel66]

Hi,

I hava a table with the following information
[code=sql]
CREATE TABLE TEMP1 (REFID INT, REVISION INT, FIELDNAM VARCHAR(10), VALUE VARCHAR(10));
INSERT INTO TEMP1 VALUES(1001, 0, 'A', 'A2');
INSERT INTO TEMP1 VALUES(1001, 0, 'C', 'C2');
INSERT INTO TEMP1 VALUES(1001, 0, 'E', 'E2');
INSERT INTO TEMP1 VALUES(1002, 0, 'A', 'A3');
INSERT INTO TEMP1 VALUES(1002, 0, 'B', 'B2');
INSERT INTO TEMP1 VALUES(1002, 0, 'E', 'E3');
INSERT INTO TEMP1 VALUES(1001, 1, 'A', 'A4');
INSERT INTO TEMP1 VALUES(1001, 1, 'E', 'E4');
[/code]
Here based on latest revision and refid I should get the fieldnam and value.
Expected output:
REFID FIELDNAM VALUE REVISION
1001 A A4 1
1001 E E4 1
1002 B B2 0
1001 C C2 0

Try this:

[code=sql]

SELECT x.refid, x.fieldnam, t.value, x.revision FROM
(SELECT MAX(revision),r efid,fieldnam FROM temp1 GROUP by refid,fieldnam) x, temp1 t
WHERE t.refid = x.refid
AND t.revision = x.revision
AND t.fieldnam = x.fieldnam

[/code]