Earliest date

[jino]

Hi there,

I need to write a query that will get me the earliest date for the given conditions.

select i.session_date
from information_session i
where i.session_date = (select min(session_date)from information_session ,
case_applicant_info_sess 
where case_applicant_info_sess.information_session_id = information_session.information_session_id
and information_session.information_session_id = i.information_session_id)

But this query does not give me the earliest date if there are multiple dates for the same criteria.

It also does not return the data if there is only one row (one date) for another applicant. If I do max(session_date) this row is returned.

Any ideas?

Thanks

Jino

 

[LKBrwnDBA]

Why don't you try:

select min(session_date)from information_session
                           , case_applicant_info_sess 
where case_applicant_info_sess.information_session_id = information_session.information_session_id
and information_session.information_session_id = i.information_session_id

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Thargy]

Jino,

since your SQL doesn't bring back any information from the table "case_applicant_info_sess", why are you involving it in your select?

If you only want date information from the "information_session" table, then only select from it, and no others. Keep your SQL as simple as possible.

You state that you need the minimum date for given conditions, what are these conditions? If there may legitimately be more than one date for a set of conditions, you need to decide on your 'tie breaker' logic. If you really do need some information from both tables then how about

SELECT i.session_date
  FROM information_session i
 WHERE i.session_date = 
      (SELECT MIN(session_date)
	     FROM information_session i2
	    INNER JOIN case_applicant_info_sess c USING (information_session_id)
        WHERE i2.information_session_id = i.information_session_id
      )

Note that the above posting still selects from the apparently useless "case_applicant_info_sess" because that's part of your original question.

I'd still like to see your explicit requirements, so that I can help with sorting out multiple dates being returned.

Regards

Tharg

Grinding away at things Oracular