CODEWITHSUNDEEP
sqlms-accesswhere-clauseoffset
italiansoda
italiansoda

Reputation: 492

MS ACCESS - OFFSET with WHERE clause

Problem

What I am trying to query is the second results of each "grouping" from my data. For example, the rows with asterisks.

I am trying to group on [Awardee], [State], [City], which should be a unique person, and I want to get their 2nd most recent [Year].

An OFFSET function would take care of the problem, but MS ACCESS does not have that in its library. I have looked up workarounds that use TOP n with nested queries, but in using the workaround more problems are created. Can I accomplish this with some type of JOIN statement?

Sample Data

 ------------------------------------
 |_Awardee_|_State_|_City__|_Year___|
 |  John   |  OH   | Cinci | 2015   |
*|  John   |  OH   | Cinci | 2013   |
 |  John   |  OH   | Cinci | 2011   |
 |  Margret|  IN   | Indy  | 2015   |
*|  Margret|  IN   | Indy  | 2012   |
 |  Margret|  IN   | Indy  | 2011   |
 |  Bob    |  IN   | Indy  | 2011   |
*|  Bob    |  IN   | Indy  | 2010   |
 |  Jeff   |  OH   | Cinci | 2016   |
*|  Jeff   |  OH   | Cinci | 2015   |
 |  Jeff   |  OH   | Cinci | 2013   |
 |  Jeff   |  OH   | Cinci | 2012   |
 |  Susan  |  IN   | Indy  | 2012   |
 |  Spencer|  IN   | Indy  | 2016   |
*|  Spencer|  IN   | Indy  | 2015   |
 -------------------------------------

Example OFFSET workaround

By Selecting the top 2 records in descending order, then selecting the top 1 record in ascending order, I am able to OFFSET my results by 1. 

SELECT TOP 1 * FROM 
(SELECT TOP 2 [Awardee], [State], [City], [Year] FROM [Table1]
ORDER BY [Awardee] ASC, [Year] DESC)
ORDER BY [Awardee] ASC, [Year] ASC;

Working SQL

This SQL query will get their most recent Award Year (the records just 1 row above where there are astricks). But as we can see if I were to replace the subquery with the OFFSET workaround from above, I would end up with 2 subqueries. The inner most subquery would not be able to recgonize 'CDFI1' in the WHERE clause. MS ACCESS subqueries cannot see beyone the nearest neighboring query. 

SELECT *
FROM [T09a: CDFI Award Records] AS CDFI1
WHERE CDFI1.[ID] IN
(SELECT TOP 1 ID FROM [T09a: CDFI Award Records] as CDFI2
WHERE CDFI2.[Awardee] = CDFI1.[Awardee] AND CDFI2.[City] = CDFI1.[City] AND CDFI2.[ST] = CDFI1.[ST]
ORDER BY CDFI2.[Awardee] ASC, CDFI2.[Year] DESC)
ORDER BY CDFI1.Awardee, CDFI1.Year DESC;

EDIT: Accepted Answer

Thanks to @krokodilko below for the demo and help in MYSQL. To get this working in ACCESS there were a few little syntax quirks, mainly to use square brackets, shown below.

SELECT t.[Awardee], t.[ST], t.[City], max( t.[Year] ) as Year
FROM ([T09a: CDFI Award Records] as t
INNER JOIN (
  SELECT [Awardee], [ST], [City], max( [Year] ) as maxYear
  FROM [T09a: CDFI Award Records]
  GROUP BY Awardee, ST, City
) as x
ON x.Awardee = t.Awardee 
  AND x.[ST] = t.[ST]
  AND x.[City] = t.[City]
  AND t.[Year] < x.maxYear)
GROUP BY t.[Awardee], t.[ST], t.[City]

Upvotes: 0

Views: 4416

Answers (1)

krokodilko
krokodilko

Reputation: 36107

I haven't access to MS access,
but I'll show you hot to do it on MySql using basic SQL features: JOIN+GROUP BY + MAX:

SELECT t.Awardee, t.State, t.City, max( t.Year ) as Year
FROM table1 t
JOIN (
  SELECt Awardee, State, City, max( Year ) as Year
  FROM table1
  GROUP BY Awardee, State, City
) x
ON x.Awardee = t.Awardee 
   AND x.State = t.State
   AND x.City = t.City
   AND t.Year < x.year
GROUP BY t.Awardee, t.State, t.City

Demo: http://sqlfiddle.com/#!9/690e47/7

I am sure you will manage to get it work on MS Access.

Upvotes: 1

Related Questions