Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
What happens if a customer purchases two items the first day they visit? You won't have a well-defined "first purchased item". –  Mat Apr 26 '13 at 4:37
add comment

4 Answers

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;
share|improve this answer
add comment

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);
share|improve this answer
add comment

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.

share|improve this answer
add comment

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
share|improve this answer
    
That's not a valid Oracle query –  a_horse_with_no_name Apr 26 '13 at 6:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.