Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

BumbleBee

Status
Not open for further replies.

Guest_imported

New member
Jan 1, 1970
0
Hi everyone,

I've got a problem...
...I'm searching for ab way to make the following sql-statement easier:

select id, MIN(nvl(min_date,'1.1.9999')) min_date
from (
select id, expiry_date min_date, expiry_date, retest_date from item
union
select id, retest_date min_date, expiry_date, retest_date from item
)
group by id
order by min_date asc;

The statement should select the items with the min expiry date resp. min retest date.
Any suggestions?
 
not sure what "the items with the min expiry date resp. min retest date" means -- did you want the lowest expiry or retest date for every item, or the item with the lowest expiry or retest date?

also, "easier" is subjective -- did you want easier to understand, or easier to make changes to later? or better performance?

your query is equivalent to

Code:
select id
     , CASE 
          WHEN expiry_date is null
           AND retest_date is null 
            THEN '1.1.9999'
          WHEN expiry_date < retest_date
            THEN expiry_date
            ELSE retest_date
       END as min_date
  from item
order 
    by min_date

:eek:)

rudy
 
As R937 said, not entirely sure what you need but here's a shot in the dark (dusk?).

Select ID,
MIN(LEAST(EXP_DATE,RETEST_DATE)) MDATE
From The_Tests
Group By
ID
Order By
2

Cheers AA 8~)
 
LEAST() is ANSI SQL?

whoa, i never knew that [upsidedown]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top