Link to home
Start Free TrialLog in
Avatar of Member_2_1316035
Member_2_1316035

asked on

Query syntax to return next 3 rows based on criteria from another query

I have a table of semester dates that store past and future dates.

For example:

Sem1     1/1/2010
Sem2     1/1/2011
Sem3     1/1/2012
Sem4     1/1/2013
Sem5     1/1/2014
Sem6     1/1/2015
Sem7     1/1/2016

I have a query that will return to me one row which is the current semester based on the top1 record whose start date is less than today's date .  For this example, my current semester is Sem3.

I need another query that will return to me ALL past semesters, plus TWO semesters in the future (two semesters beyond the current semester value).  So it needs to return this subset;

Sem1
Sem2
Sem3
Sem4
Sem5

New semester values are continually being added to this table, so I need for it to always dynamically determine the current semester plus only two in the future.

Thanks in advance for your help.
SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
biang,

In my testing, both my query from http:#a38570319 and Angelgeo's from http:#a38570788 returned the desired result.

Angelgeo,

I have to admit surprise that your query worked.  SQL Server would have rejected it because in a T-SQL union query, you can only use an ORDER BY clause on the last SELECT statement.  I had been expecting Access's Jet/Ace engine to have the same requirement, but apparently it does not :)

Patrick
Avatar of Member_2_1316035
Member_2_1316035

ASKER

Thanks for everyone's help.  I am testing both methods.

I was able to get matthewspatrick to return the desired results with this sql.  Thanks! However, can you explain what "ORDER BY 2" does?

SELECT qry_SEMESTER.SESSION_CD, qry_SEMESTER.SESS_START_DT
FROM qry_SEMESTER
WHERE (((qry_SEMESTER.SESS_START_DT)<=Date()))

UNION ALL
SELECT z.SESSION_CD, z.SESS_START_DT
FROM
     (SELECT TOP 2 t2.SESSION_CD, t2.SESS_START_DT
     FROM qry_SEMESTER t2
     WHERE t2.SESS_START_DT>Date()
     ORDER BY t2.SESS_START_DT) AS z
ORDER BY 2;

Open in new window


I also tested out Angelgeo query but received a "syntax error".

SELECT *
FROM
     (SELECT TOP 3 SESSION_CD, SESS_START_DT
     FROM qry_SEMESTER
     WHERE (SESS_START_DT)<=Date())
     ORDER BY SESS_START_DT DESC

UNION ALL

     SELECT TOP 2 SESSION_CD, SESS_START_DT
     FROM qry_SEMESTER
     WHERE (SESS_START_DT>Date())
     ORDER BY SESS_START_DT) AS t

ORDER BY t.SESS_START_DT;

Open in new window


Additionally - the SEMESTER table (Table1) will continually be having new future sessions added, so I'm not sure if Angelgeo's will allow it to display all past semesters using Top3?
"ORDER BY 2" sorted by the second field in query,
in this case "qry_SEMESTER.SESS_START_DT"

"I'm not sure if Angelgeo's will allow it to display all past semesters using Top3?"
I'm not correctly understand problem.
Thanks for the explanation on ORDER BY 2.  I didn't realize you could reference a column count - I always thought it had to be a field name.  ALways good to learn new things.

What I meant about the Top3 comment is that if I understand your query, you are getting the top 3 (past semesters) and the top 2 (future semesters), so your list will always only return 5 rows, correct?  I need my list to always display all past semesters, not just the 3 most recent ones.  The data I provided about was just a sample set, but the actual list is very long going back very many years in the past.  I suspect I could just remove the Top 3 clause to get it to display all past semesters.  But since I can't get past the syntax error I can't test it out.