Oracle-L: (no subject)

From: Viktor <stant_98_at_yahoo.com>
Date: Tue, 19 Mar 2002 13:52:40 -0800
Message-ID: <F001.0042DD75.20020319135240@fatcity.com>

Hi all,

I've got a query that returned more rows than needed.

Here is the query:

SELECT distinct
m.journal_id||m.yr_of_issue||m.ms_sequence_no||m.check_char MSNO,

       M.RECEIVED_DATE "RECEIVED DATE",
       M.EDITOR_NO "EDITOR NUMBER",
       S.STATHIST_CODE "STATUS CODE",
       ST.STATUS_DESC  "STATUS DESCRIPTION",
       count(*) "REVIEWER COUNT"
FROM mscript m,
     stathist s,
     reviewms r,
     statcode st
WHERE M.JOURNAL_ID = 'ES'
  AND M.EDITOR_NO = 31
  AND S.STATHIST_CODE <> 'z'
  AND S.STATHIST_CODE = ST.STATUS_CODE
  AND M.JOURNAL_ID = s.shist_ms_jcode     --- primary

key
  AND M.YR_OF_ISSUE = s.shist_ms_yrissue --- primary key
  AND M.MS_SEQUENCE_NO = s.shist_ms_ms_seqno --- primary key
  AND M.CHECK_CHAR = s.shist_ms_ckchar --- primary key
  AND s.shist_ms_jcode = r.msnumber_jcode --- primary key
  and s.shist_ms_yrissue = r.msnumber_yrissue --- primary key
  and s.shist_ms_ms_seqno = r.msnumber_ms_seqno --- primary key
  and s.shist_ms_ckchar = r.msnumber_ckchar --- primary key
GROUP BY
m.journal_id||m.yr_of_issue||m.ms_sequence_no||m.check_char,

       M.RECEIVED_DATE,
       M.EDITOR_NO,
       S.STATHIST_CODE,
       ST.STATUS_DESC

order by 1
/

And the query displays this :

ES9507372 10/03/1995            31 b accepted with one
revision         01/10/2002
ES9507372 10/03/1995            31 d author revision
returned           
ES9507372 10/03/1995            31 e all reviews
returned               
ES9507372 10/03/1995            31 f sent to editor   
                 
ES9507372 10/03/1995            31 g proofs sent to
author              
ES9507372 10/03/1995            31 j Cols receipt and
processing        
ES9507372 10/03/1995            31 l acceptance
pending materials       
ES9507372 10/03/1995            31 m manuscript
initially received      
ES9507372 10/03/1995            31 p sent to Cols.
journals office      
ES9507372 10/03/1995            31 q sent for review

I only want it to select the first record all the way on top because it has the most current data with the most corrent DATE.

Records in STATHIST TABLE ARE LIKE where stathist_date and stathist_code are the last 2 columns.

ES9507372  01/10/2002 b 
ES9507372  01/05/2002 d 
ES9507372  01/02/2002 e

Unique for each row.
I know I am missing something in the query, but I've got a "brain freeze"; I basically need to have the query select only records that have LATEST stathist_date and NOT repating all recs there are.

Thanks so much in advance!!!

Do You Yahoo!?
Yahoo! Sports - live college hoops coverage http://sports.yahoo.com/
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Viktor
INET: stant_98_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Mar 19 2002 - 15:52:40 CST