Re: Slick Query
From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Fri, 25 Jul 2008 21:16:25 +0200
Message-ID: <488A2689.2070304@gmail.com>
>
>
> Hi Dan, thanks for replying.
>
> I will order the records in decending order when they are selected.
> Then, if an Active or Pending status exists in the result set, return
> the first one that is encountered. Otherwise return the first
> Complete or Cancelled status encountered.
>
> 01/05/08 Complete
> 02/15/09 Complete
> 02/18/08 Active
> 05/10/08 Cancelled
> 06/07/08 Pending
> 02/19/08 Complete
>
> In this case, once ordered, I'd encounter the Active first and return
> that.
>
> 02/15/09 Complete
> 05/10/08 Cancelled
> 01/05/08 Complete
> 02/19/08 Complete
>
> In this case, since there is no Active or Pending, I'll return the
> Complete on 1/5/08.
>
> Again, I'll oredr the dates decending......
>
> Thanks Dan!
Date: Fri, 25 Jul 2008 21:16:25 +0200
Message-ID: <488A2689.2070304@gmail.com>
artmerar_at_yahoo.com schrieb:
> On Jul 25, 1:14 pm, t..._at_panix.com (Dan Blum) wrote:
>> artme..._at_yahoo.com wrote: >>> I want to see if I can create a slick query to do this and cut down on >>> some code. >>> I have a list of dates & statuses like this: >>> 01/05/08 Complete >>> 02/15/09 Complete >>> 02/18/08 Active >>> 05/10/08 Cancelled >>> 06/07/08 Pending >>> 02/19/08 Complete >>> I'll order them in the query. What I need to do is first check to see >>> if there is a status of Active or Pending and return the first one I >>> encounter. If those do not exist, I need to return the first Complete >>> or Cancelled I encounter. >>> In this case Active would be returned. >>> Right now I have a cursor and some PL/SQL going through 2 loops >>> checking for Active/Pending, then Complete/Cancelled. I'd like to see >>> if I can do it all in one query. >>> Any hopes of doing this? >> What do you mean by the "first one I encounter?" The records you show >> are not in any kind of order. If you mean the EARLIEST, then you can >> do it like so (there are other ways, too): >> >> select * from >> (select date_col, status >> from foo >> order by decode(status, 'Active', 1, 'Pending', 1, 2), date_col) >> where rownum = 1; >> >> If that is not what you mean, you need to explain the problem in more detail. >> >> -- >> _______________________________________________________________________ >> Dan Blum t..._at_panix.com >> "I wouldn't have believed it myself if I hadn't just made it up."- Hide quoted text - >> >> - Show quoted text -
>
>
> Hi Dan, thanks for replying.
>
> I will order the records in decending order when they are selected.
> Then, if an Active or Pending status exists in the result set, return
> the first one that is encountered. Otherwise return the first
> Complete or Cancelled status encountered.
>
> 01/05/08 Complete
> 02/15/09 Complete
> 02/18/08 Active
> 05/10/08 Cancelled
> 06/07/08 Pending
> 02/19/08 Complete
>
> In this case, once ordered, I'd encounter the Active first and return
> that.
>
> 02/15/09 Complete
> 05/10/08 Cancelled
> 01/05/08 Complete
> 02/19/08 Complete
>
> In this case, since there is no Active or Pending, I'll return the
> Complete on 1/5/08.
>
> Again, I'll oredr the dates decending......
>
> Thanks Dan!
And if you add a "desc" just after date_col, would Dan's solution not solve your problem?
Best regards
Maxim Received on Fri Jul 25 2008 - 14:16:25 CDT