oracle query help

Question

i have the following:

CREATE TABLE R_TEST
(
   PROJECT_ID  NUMBER,
   VERSION     NUMBER,
   READY_DATE  DATE,
   ESTATE_NO   VARCHAR2(1 BYTE)
  )
  TABLESPACE vvvvvvvvv
  PCTUSED    0
  PCTFREE    10
  INITRANS   1
  MAXTRANS   255
  STORAGE    (
        INITIAL          64K
        NEXT             1M
        MINEXTENTS       1
        MAXEXTENTS       UNLIMITED
        PCTINCREASE      0
        BUFFER_POOL      DEFAULT
       )
 LOGGING 
 NOCOMPRESS 
 NOCACHE
 NOPARALLEL
 MONITORING;
 SET DEFINE OFF;

 Insert into R_TEST
    (PROJECT_ID, VERSION, READY_DATE, ESTATE_NO)
 Values
    (1345, 1, NULL, 'a');
 Insert into R_TEST
    (PROJECT_ID, VERSION, READY_DATE, ESTATE_NO)
 Values
    (1345, 2, NULL, 'a');
 Insert into R_TEST
   (PROJECT_ID, VERSION, READY_DATE, ESTATE_NO)
 Values
   (1345, 3, TO_DATE('07/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'a');
 Insert into R_TEST
   (PROJECT_ID, VERSION, READY_DATE, ESTATE_NO)
 Values
   (1345, 4, TO_DATE('07/29/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'a');
 Insert into R_TEST
   (PROJECT_ID, VERSION, READY_DATE, ESTATE_NO)
  Values
  (1059, 1, NULL, 'b');
 Insert into R_TEST
   (PROJECT_ID, VERSION, READY_DATE, ESTATE_NO)
  Values
   (1059, 2, TO_DATE('06/27/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'b');
  Insert into R_TEST
    (PROJECT_ID, VERSION, READY_DATE, ESTATE_NO)
 Values
    (2326, 1, NULL, 'b');
 Insert into R_TEST
    (PROJECT_ID, VERSION, READY_DATE, ESTATE_NO)
  Values
    (2326, 2, NULL, 'b');
 Insert into R_TEST
    (PROJECT_ID, VERSION, READY_DATE, ESTATE_NO)
 Values
    (2326, 3, TO_DATE('08/29/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'b');
 Insert into R_TEST
    (PROJECT_ID, VERSION, READY_DATE, ESTATE_NO)
 Values
    (998, 1, NULL, 'c');
  Insert into R_TEST
    (PROJECT_ID, VERSION, READY_DATE, ESTATE_NO)
  Values
   (998, 2, TO_DATE('07/27/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'c');
  Insert into R_TEST
    (PROJECT_ID, VERSION, READY_DATE, ESTATE_NO)
  Values
   (998, 1, NULL, 'c');
  COMMIT;

I am trying to get how many active vs completed projects exist per estate_no. According to above data: I should have

one completed project on 7/27/2011 and one active project for estate_no C. two completed projects on 8/29/2011 and 6/27/2011 for estate_no B. one completed project on 7/29/2011 for estate_no A.

the problem i am having is that estate_no A has two dates for the same project_id with 4 different versions. They marked it ready but realized that it wasn't actually ready, did some more work and then marked it ready one more time.

any help will be appreciated.

Answer 1

You should pick the row with the maximum version for an estate and a project.

one way is to join a subquery containg the rows with maximum version:

SELECT  r_test.project_id, r_test.version, r_test.ready_date, r_test.estate_no
FROM r_test INNER JOIN (SELECT  project_id, estate_no, max(version) as max_version
                        FROM r_test
                        GROUP BY project_id,estate_no
                       ) sub_r_test 
ON (r_test.project_id = sub_r_test.project_id
    and r_test.estate_no = sub_r_test.estate_no
    and r_test.version = sub_r_test.max_version
   )

another way is to use oracle analytical functions to give row number on each line according to its group where the group is sorted in descending order of versions and then picking the lines that have the number 1 (they will be lines with max version)

SELECT  project_id, version, ready_date, estate_no
FROM (SELECT project_id,
       version,
       ready_date,
       estate_no,
       row_number() over (partition by project_id,estate_no order by version desc) rn 
      FROM r_test
     )  
WHERE rn=1

Answer 2

  select  project_id, ready_date, version
  from r_test r
  where r.estate_no =  'c'
  and ready_date is not null
  and r.version = (select max(r2.version) from r_test r2 where r.project_id = r2.project_id )
  group by project_id, version, estate_no, ready_date

this seems to work as long as the project has a ready date.