Home » SQL & PL/SQL » SQL & PL/SQL » Trying to get first & last values in one row
Trying to get first & last values in one row [message #220711] Wed, 21 February 2007 12:04 Go to next message
dgoldman
Messages: 5
Registered: February 2007
Junior Member
Good Day. Thanks for your help.

I am developing a report and I am sure I will have several hurdles along the way.

Here is the first:

I am attempting to query for the first code, the last code, & the date of the last code for each cust_no. This is my most recent attempt:

SELECT   cust_no,
         FIRST_VALUE (code) OVER (PARTITION BY cust_no ORDER BY createddt,
          ROWNUM) AS FIRST,
         LAST_VALUE (code) OVER (PARTITION BY cust_no ORDER BY createddt,
          ROWNUM) AS now,
         LAST_VALUE (createddt) OVER (PARTITION BY cust_no ORDER BY createddt,
          ROWNUM) AS tranfer_date
    FROM cust_code
   WHERE code < 5000
ORDER BY cust_no



I end up with this:

CUST_NO	        FIRST	NOW	TRANFER_DATE

100100015	1001	1001	12/16/2005
100100015	1001	1003	1/30/2006
100100023	1001	1001	12/16/2005
100100023	1001	1005	1/30/2006
100100031	1001	1001	12/16/2005
100100049	1001	1001	12/16/2005
100100049	1001	1002	2/28/2006
100100056	1001	1001	12/16/2005
100100064	1001	1001	12/16/2005
100100064	1001	1008	1/30/2006


What I want is this:

CUST_NO	        FIRST	NOW	TRANFER_DATE

100100015	1001	1003	1/30/2006
100100023	1001	1005	1/30/2006
100100031	1001	1001	12/16/2005
100100049	1001	1002	2/28/2006
100100056	1001	1001	12/16/2005
100100064	1001	1008	1/30/2006


My next hurdle will be to make it look like this:

CUST_NO	        FIRST	NOW	TRANFER_DATE

100100015	1001	1003	1/30/2006
100100023	1001	1005	1/30/2006
100100049	1001	1002	2/28/2006
100100064	1001	1008	1/30/2006


If FIRST & NOW are the same I do not want to see them.


CREATE TABLE CUST_CODE
(
  CUST_NO    VARCHAR2(20 BYTE),
  CODE       VARCHAR2(8 BYTE),
  CREATEDDT  DATE);

INSERT INTO CUST_CODE
VALUES('100100015','1001','16-Dec-05');

INSERT INTO CUST_CODE
VALUES('100100015','1003','30-Jan-06');

INSERT INTO CUST_CODE
VALUES('100100023','1001','16-Dec-05');

INSERT INTO CUST_CODE
VALUES('100100023','1005','30-Jan-06');

INSERT INTO CUST_CODE
VALUES('100100031','1001','16-Dec-05');

INSERT INTO CUST_CODE
VALUES('100100049','1001','16-Dec-05');

INSERT INTO CUST_CODE
VALUES('100100049','1002','28-Feb-06');

INSERT INTO CUST_CODE
VALUES('100100056','1001','16-Dec-05');

INSERT INTO CUST_CODE
VALUES('100100064','1001','16-Dec-05');

INSERT INTO CUST_CODE
VALUES('100100064','1008','30-Jan-06');

[Updated on: Wed, 21 February 2007 12:30]

Report message to a moderator

Re: Trying to get first & last values in one row [message #220713 is a reply to message #220711] Wed, 21 February 2007 12:33 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
I'm not sure what you want to see if there are more than 2 rows per cust_no, but I think with the sample data you gave in your post, this would work:

SQL> SELECT *
  2  FROM   (SELECT cust_no
  3                ,MIN(cc.code) FIRST
  4                ,MAX(cc.code) now
  5                ,MAX(cc.createddt) transfer_date
  6          FROM   cust_code cc
  7          GROUP  BY cc.cust_no
  8          ORDER  BY cc.cust_no)
  9  WHERE  FIRST <> now
 10  /

CUST_NO              FIRST    NOW      TRANSFER_DATE
-------------------- -------- -------- -------------
100100015            1001     1003     30-1-2006
100100023            1001     1005     30-1-2006
100100049            1001     1002     28-2-2006
100100064            1001     1008     30-1-2006
Re: Trying to get first & last values in one row [message #220715 is a reply to message #220713] Wed, 21 February 2007 12:45 Go to previous messageGo to next message
dgoldman
Messages: 5
Registered: February 2007
Junior Member
Thanks, but this will not work.
The initial code value could be greater than the last or a greater value could be some where between the first & last values.

Re: Trying to get first & last values in one row [message #220717 is a reply to message #220711] Wed, 21 February 2007 12:52 Go to previous messageGo to next message
pmaupoil
Messages: 40
Registered: February 2007
Location: France
Member
Would that do it then?

SELECT * 
FROM (SELECT cust_no,
             FIRST_VALUE (code) OVER (PARTITION BY cust_no ORDER BY createddt, ROWNUM) AS FIRST,
             LAST_VALUE (code) OVER (PARTITION BY cust_no ORDER BY createddt, ROWNUM) AS now,
             LAST_VALUE (createddt) OVER (PARTITION BY cust_no ORDER BY createddt, ROWNUM) AS tranfer_date
      FROM cust_code
      WHERE code < 5000)
WHERE FIRST != now
ORDER BY cust_no
Re: Trying to get first & last values in one row [message #220719 is a reply to message #220717] Wed, 21 February 2007 13:08 Go to previous messageGo to next message
dgoldman
Messages: 5
Registered: February 2007
Junior Member
Yes that appears to have worked.
Thanks so much. You conquered my first 2 hurdles.
Now I can push ahead.
Re: Trying to get first & last values in one row [message #220782 is a reply to message #220719] Thu, 22 February 2007 02:04 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member

by same example

i want this result is it possible

plz help me

CUST_NO FIRST NOW TRANFER_DATE

100100015 1001 1003 1/30/2006
100100023 1001 1005 1/30/2006
100100031 1001 1001 12/16/2005
100100049 1001 1002 2/28/2006
100100056 1001 1001 12/16/2005
100100064 1001 1008 1/30/2006

Thanks,
Srinivas
Re: Trying to get first & last values in one row [message #220800 is a reply to message #220711] Thu, 22 February 2007 03:25 Go to previous messageGo to next message
pmaupoil
Messages: 40
Registered: February 2007
Location: France
Member
SELECT cust_no, first, now, tranfer_date 
FROM (SELECT cust_no,
             FIRST_VALUE (code) OVER (PARTITION BY cust_no ORDER BY createddt, ROWNUM) AS FIRST,
             LAST_VALUE (code) OVER (PARTITION BY cust_no ORDER BY createddt, ROWNUM) AS now,
             LAST_VALUE (createddt) OVER (PARTITION BY cust_no ORDER BY createddt, ROWNUM) AS tranfer_date,
             COUNT(*) over (PARTITION BY cust_no) tot
      FROM cust_code
      WHERE code < 5000)
WHERE NOT (tot >= 2 AND first = now)
ORDER BY cust_no

[Updated on: Thu, 22 February 2007 03:47]

Report message to a moderator

Re: Trying to get first & last values in one row [message #220808 is a reply to message #220717] Thu, 22 February 2007 04:05 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
Thanks,
srinivas
Re: Trying to get first & last values in one row [message #221416 is a reply to message #220711] Mon, 26 February 2007 11:14 Go to previous messageGo to next message
dgoldman
Messages: 5
Registered: February 2007
Junior Member
I have another question regarding this same issue.

How can I select the next to last record?

Codes do not get entered in any specific order & can repeat.
The code could change on the same date.

Thanks.
Re: Trying to get first & last values in one row [message #221420 is a reply to message #220711] Mon, 26 February 2007 12:34 Go to previous message
dgoldman
Messages: 5
Registered: February 2007
Junior Member
I have discovered other fields in the table that I did not have available in the environment.

One of these might help considerably: uniqueid
This field is numeric & increases with each new entry.

I am in the process of having this (& other fields) made available to me in the correct environment.
Previous Topic: Check Constraints
Next Topic: Explain Plan
Goto Forum:
  


Current Time: Tue Dec 06 16:12:57 CST 2016

Total time taken to generate the page: 0.05264 seconds