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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
