RE:

Hello Victor

Try to add
and your_date in (select max(your_date) from whatever)

Yechiel Adar, Mehish Computer Services
adary_at_mehish.co.il

> -----Original Message-----
> From: Viktor [SMTP:stant_98_at_yahoo.com]
> Sent: Tue, March 19, 2002 11:53 PM
> To: Multiple recipients of list ORACLE-L
> Subject:
>
> 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?=
  INET: adary_at_mehish.co.il

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).