Selecting the first purchase item of each customer

I have a table like this:

CREATE TABLE SALES
(
CID NUMBER(4),
PDATE DATE,
ITEMCODE NUMBER(4),
ITEMNAME VARCHAR2(20)
);

INSERT INTO SALES VALUES (1111,'15-JAN-2011',4001,'LUX');
INSERT INTO SALES VALUES (1111,'17-JAN-2011',4002,'DRY FRUIT');
INSERT INTO SALES VALUES (1111,'20-JAN-2011',4003,'ARIEL');
INSERT INTO SALES VALUES (1111,'03-JAN-2011',4003,'ARIEL');
INSERT INTO SALES VALUES (1111,'04-JAN-2011',4010,'TELENOR CARD');

INSERT INTO SALES VALUES (2222,'05-FEB-2011',4005,'APPLE');
INSERT INTO SALES VALUES (2222,'06-FEB-2011',4001,'LUX');
INSERT INTO SALES VALUES (2222,'09-FEB-2011',4006,'ORS');

INSERT INTO SALES VALUES (3333,'03-FEB-2009',4007,'CHOCOLATE');
INSERT INTO SALES VALUES (3333,'09-MAR-2011',4001,'LUX');
INSERT INTO SALES VALUES (3333,'15-APR-2011',4008,'MILK');
INSERT INTO SALES VALUES (3333,'03-JAN-2011',4010,'TELENOR CARD');

INSERT INTO SALES VALUES (4444,'11-JAN-2011',4009,'TAPAL MIXTURE');
INSERT INTO SALES VALUES (4444,'19-JAN-2011',4010,'SUGAR');
INSERT INTO SALES VALUES (4444,'21-JAN-2011',4001,'LUX');
INSERT INTO SALES VALUES (4444,'05-JAN-2011',4005,'APPLE');
INSERT INTO SALES VALUES (4444,'15-JAN-2011',4001,'LUX');

INSERT INTO SALES VALUES (5555,'01-JAN-2011',4009,'TAPAL MIXTURE');
INSERT INTO SALES VALUES (5555,'03-JAN-2011',4010,'TELENOR CARD');
INSERT INTO SALES VALUES (5555,'22-JAN-2011',4005,'APPLE');

I need to fetch the first purchased item name by all the id individually.

How can I do this?

select cid,
       pdate,
       itemcode,
       itemname
from (
  select sales.*,
         rank() over (partition by cid order by pdate) as rnk
  from sales
) t
where rnk = 1
order by cid;

Alternatively

select s1.cid,
       s1.pdate,
       s1.itemcode,
       s1.itemname
from sales s1
where pdate = (select min(s2.pdate)
               from sales s2
               where s1.cid = s2.cid)
order by cid;

I am assuming by “all the id” you mean for each CID. There are many ways to do this. One way is:

select cid, itemname from sales where (cid, pdate) in
(select cid, min(pdate) from sales group by cid);

I would like to add something to @a_horse_with_no_name’s answer:

SELECT cid,
       pdate,
       itemcode,
       itemname
FROM (
  SELECT sales.*,
         ROW_NUMBER() OVER (PARTITION BY cid ORDER BY pdate) AS rn
  FROM sales
) t
WHERE rn = 1
ORDER BY cid;

If you use rank and there’s no fine detail about hours in the datetime column, with rank you could end up with more than one row if date columns were the same value.

The following query will do this for you. What it does is create a Rank partitioned by each customer and ordered by Pdate

SELECT CID,PDATE,ITEMCODE,ITEMNAME
FROM sales
ORDER BY CID,PDATE
QUALIFY RANK() OVER (PARTITION BY CID ORDER BY PDATE) = 1