transaction_dinesh.sql

-- create
CREATE TABLE TRANSACTION (
  id INTEGER NOT NULL,
  TRANS VARCHAR(3) NOT NULL,
  CODE INTEGER NOT NULL
);

-- insert
INSERT INTO TRANSACTION VALUES (1001, 'TE', 9000);
INSERT INTO TRANSACTION VALUES (1002, 'TE', 9000);
INSERT INTO TRANSACTION VALUES (1002, 'CC', 8999);
INSERT INTO TRANSACTION VALUES (1003, 'TE', 9000);
INSERT INTO TRANSACTION VALUES (1003, 'CC', 8999);
INSERT INTO TRANSACTION VALUES (1003, 'CAN', 8998);
INSERT INTO TRANSACTION VALUES (1004, 'TE', 9000);
INSERT INTO TRANSACTION VALUES (1004, 'CAN', 8999);
INSERT INTO TRANSACTION VALUES (1004, 'CC', 8998);
INSERT INTO TRANSACTION VALUES (1004, 'CAN', 8997);

select * from transaction;
-- fetch 
-- select * from transaction where code = (SELECT min(code) FROM TRANSACTION group by id order by id) and trans = 'CAN' order by id;
-- select * from transaction where 
-- SELECT id, min(code) FROM TRANSACTION group by id order by id;
Select a.* from (SELECT id, min(code) as code FROM TRANSACTION group by id) as a left join transaction as b on a.id=b.id and a.code=b.code where b.trans='CAN'