question in view

[cchiara]

This is the query I used in a view. I'd like to view the most recent year of each system and filter out the previous years. The query below not only return the most recent year but previous years as well.

(select max(fiscal_year) from self_assessment group by system_id);
alone gives me the desired output but how can I put it in a view without listing all the system records, only the most recent one.
Please advise...


CREATE OR REPLACE VIEW CURRENT_VIEW
(FISCAL_YEAR,
SA_ID,
SYSTEM_ID,
STATUS_CODE
)
AS
(SELECT fiscal_year,
SA_ID,
SYSTEM_ID,
STATUS_CODE
FROM ASSESSMENT
where
fiscal_year in (select max(fiscal_year) from assessment group by system_id)
order by system_id;

FISCAL_YEAR SA_ID SYSTEM_ID STA
----------- ------ ---------- ---
2005 24 1 2
2004 19 1 1
2005 37 2 1
2004 20 2 3
2004 21 4 1
2005 43 5 3
2007 101 5 1
2006 81 5 3
2004 22 5 4
2004 63 6 1
2004 40 7 1
2004 42 8 1
2005 32 23 2
2004 30 23 3
2005 103 26 1
2004 38 26 3
2004 82 48 1
2006 121 67 1
2006 122 71 1
2006 123 75 1

20 rows selected.
=======================

Desired output as follows:

select system_id, max(fiscal_year) from assessment group by system_id;

SYSTEM_ID MAX(FISCAL_YEAR)
---------- ----------------
1 2005
2 2005
4 2004
5 2007
6 2004
7 2004
8 2004
23 2005
26 2005
48 2004
67 2006
71 2006
75 2006

13 rows selected.

[marist89]

You're joining different years without taking into account the system_id. You need something like:

CREATE OR REPLACE VIEW CURRENT_VIEW 
  (FISCAL_YEAR,
   SA_ID,  
   SYSTEM_ID,
   STATUS_CODE
   )
AS 
   SELECT fiscal_year,
      SA_ID,
      SYSTEM_ID,
      STATUS_CODE
      FROM ASSESSMENT
   WHERE (system_id, fiscal_year) IN 
      (SELECT system_id, max(fiscal_year) 
       FROM assessment 
       GROUP BY system_id)
   ORDER BY system_id

[cchiara]

Thank you very much for your help!

[ddrozdov]

There's a good chance that this is a faster query.
The "in" queries are often slower than joins and correlated with exists (not exists)

CREATE OR REPLACE VIEW CURRENT_VIEW
(FISCAL_YEAR,
SA_ID,
SYSTEM_ID,
STATUS_CODE)
AS
SELECT a.fiscal_year,
a.SA_ID,
a.SYSTEM_ID,
a.STATUS_CODE
FROM
ASSESSMENT a
(SELECT system_id, max(fiscal_year) mfy
FROM assessment
GROUP BY system_id) m
WHERE
a.system_id = m.system_id and
a.fiscal_year = m.mfy
ORDER BY a.system_id;