Hello Victor Try to add and your_date in (select max(your_date) from whatever)
Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] > -----Original Message----- > From: Viktor [SMTP:[EMAIL PROTECTED]] > 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: [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). -- 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: [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).
