Last Time Offered (when applicable)

[kernal]

How do you create a select statement so it retrieves a term that you have prompted for and the last term (when applicable) the same test was offered?(Example:

Fall 2005 TEST 1
Summer 2005 TEST 1

Fall 2005 TEST 2
Spring 2005 TEST 2
Fall 2004 TEST 2

Fall 2005 TEST 3

Results wanted:

Term Test Last Time Offered
Fall 2005 TEST 1 Summer 2005
Fall 2005 TEST 2 Spring 2005
Fall 2005 TEST 3 Blank

Thanks for your help.

 

[carp]

Unless you have some means of associating a date with each row of data, there is no way.

 

[carp]

Although I suppose if you could associate ascending numbers with each row of data and had a way of inferring relative times with the number (e.g., a sequence), you could do it. But the point is, you need to be able to determine that Summer occurs before Fall, etc. This could either be done by assigning a sequence to each row of data and making sure the rows are created chronologically, or building a lookup table that would associate a value with a season to indicate their relative occurrences:

Occurrence Season
1 Winter
2 Spring
3 Summer
4 Fall

With something like this, you could look at Occurrence and Year for two rows of data and determine their temporal relationship.

 

[kernal]

Thanks for the quick reply Carp. There is actually a date associated with the term. Since this is the case, what would the select statement look like. Example:

Fall 2005 08/25/2005
Summer 2005 05/24/2005
Spring 2005 01/09/2005

Thanks

 

[carp]

In that case, the query might look something like:

SELECT test_name, max(test_date)
FROM my_tests
WHERE test_name = <name_supplied_at_prompt>
  AND test_date < SYSDATE 
GROUP BY test_name;