OraFAQ Forum: SQL & PL/SQL » nested sql help

Home » SQL & PL/SQL » SQL & PL/SQL » nested sql help

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

nested sql help [message #2995]

Wed, 28 August 2002 12:46

WALID
Messages: 31
Registered: August 2002

Member

Can you help me find data in one table - each row is a historical record of an units with serial numbers as primary key - there is a column that has a flag = Y/N and time seqno =0/1/2/3/4/5/... There should only be one record in the sequence of events with flag=Y. I want to find all units that might have two or more lastflag='Y' and not to include the row with only one lastflag='Y'

I tried this query and I don't think I am getting the right info. Please help.

SELECT serno from TMP_WORK_ARCH t1
where exists (select SERNO from TMP_WORK_ARCH t2
where t2.SERNO = t1.SERNO
and t2.lastflag <> t1.lastflag
and t2.tmseqno > t1.TMSEQNO);
Thanks,

Report message to a moderator

Re: nested sql help [message #2997 is a reply to message #2995]

Wed, 28 August 2002 13:16

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

select serno, count(*)
  from tmp_work_arch
 where lastflag = 'Y'
 group by serno
 having count(*) > 1;

Report message to a moderator

Previous Topic:	extracted report to be mailed in *.csv format
Next Topic:	After triggers

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 07:03:55 CDT 2024