Combining the following mulitple SQL queries into 1 with MS Access & ASP

Question

I want to combine

Select top 2 scouting.* From scouting Where scouting.astroLoc Like 'D01%' AND scouting.ownerGuild = 'SWARM' Order By scouting.jumpGate Desc

with

Select top 2 scouting.* From scouting Where scouting.astroLoc Like 'D02%' scouting.ownerGuild = 'SWARM' Order By scouting.jumpGate Desc

with

Select top 2 scouting.* From scouting Where scouting.astroLoc Like 'D03%' scouting.ownerGuild = 'SWARM' Order By scouting.jumpGate Desc

continued until

Select top 2 scouting.* From scouting Where scouting.astroLoc Like 'D79%' scouting.ownerGuild = 'SWARM' Order By scouting.jumpGate Desc

... into 1 SQL query whereby the TOP 3 records are grouped by scouting.astroLoc ascending.

Answer 1

This is the "greatest-n-per-group" problem that is posted frequently on StackOverflow. Here's a solution:

SELECT s1.*
FROM scouting s1
LEFT OUTER JOIN scouting s2
  ON (s1.astroLoc = s2.astroLoc AND s1.jumpGate < s2.jumpGate)
WHERE s1.astroLoc LIKE 'D[3-7][0-9]%' AND s1.astroLoc NOT LIKE 'D3[0-2]%'
GROUP BY s1.* -- here you need to name all fields in the select-list
HAVING COUNT(*) < 2;

This works because the query tries to match a given row s1 to the set of rows s2 that have the same astroLoc and a greater jumpGate value. The HAVING clause restricts the result to s1 rows that match fewer than two, which means that the row would be in the top 2.

This assumes rows are unique over [astroLoc, jumpGate]. If not, you may need to add another term to the join condition to resolve ties.

---

Re your comment, try the following alteration:

SELECT s1.*
FROM scouting s1
LEFT OUTER JOIN scouting s2
  ON (SUBSTRING(s1.astroLoc, 1, 3) = SUBSTRING(s2.astroLoc, 1, 3)
      AND (s1.jumpGate < s2.jumpGate OR (s1.jumpGate = s2.jumpGate AND s1.ID < s2.ID))
WHERE s1.astroLoc LIKE 'D[3-7][0-9]%' AND s1.astroLoc NOT LIKE 'D3[0-2]%'
GROUP BY s1.* -- here you need to name all fields in the select-list
HAVING COUNT(*) < 2;

This compares only the first three characters of astroLoc for purposes of testing a row is in the same "group" as the other, and it also resolves ties in jumpGate by using the primary key.

---

Re your other answer with new requirements:

where does the scouting.ownerGuild = 'SWARM' go?

It's hard to follow what you're asking for, since I don't know what are your table definitions or the meanings of columns. Do you want the outer query to be matched to the top three jumpgates that are owned by the SWARM guild?

SELECT s1.astroLoc, g.[galaxy_aename], s1.jumpGate, s1.ownerGuild
FROM galaxy g INNER JOIN scouting s1 ON g.[galaxy_ID] = s1.galaxy 
WHERE s1.jumpGate IN (SELECT TOP 3 s2.jumpGate FROM scouting AS s2
        WHERE s2.galaxy = g.[galaxy_ID] AND s2.ownerGuild = 'SWARM'
        ORDER BY s2.jumpGate DESC)
ORDER BY scouting.astroLoc DESC, scouting.jumpGate DESC

That would be a different query from this one, which makes the outer query return the jumpgates owned by SWARM that match the top three jumpgates owned by anyone.

SELECT s1.astroLoc, g.[galaxy_aename], s1.jumpGate, s1.ownerGuild
FROM galaxy g INNER JOIN scouting s1 ON g.[galaxy_ID] = s1.galaxy 
WHERE s1.jumpGate IN (SELECT TOP 3 s2.jumpGate FROM scouting AS s2
        WHERE s2.galaxy = g.[galaxy_ID]
        ORDER BY s2.jumpGate DESC)
  AND s1.ownerGuild = 'SWARM'
ORDER BY scouting.astroLoc DESC, scouting.jumpGate DESC

It's possible the second query will return an empty result, if none of the SWARM jumpgates are in the top three.

PS: It's customary on StackOverflow to edit your original question post at the top, when you need to add more detail or followup questions.

Answer 2

The problem has changed only very slightly with more astroloc D[0-7][0-9] values required, constrained by owner.Guild = 'SWARM' ... I did something very similar to splitting astroloc into 2 fields. Instead I created a new table (galaxy) which contains the part of the astroloc data I require (the first 3 characters, minus the 'D'), and I reference this with a unique ID:

Select scouting.astroLoc, galaxy.[galaxy_aename], scouting.jumpGate, scouting.ownerGuild From galaxy Inner Join scouting On galaxy.[galaxy_ID] = scouting.galaxy Where (scouting.jumpGate) In (Select Top 3 scouting.jumpGate From scouting Where scouting.galaxy = galaxy.[galaxy_ID] Order By scouting.jumpGate Desc) Order By scouting.astroLoc Desc, scouting.jumpGate Desc

The problem is it MISSES OUT SOME OF THE CATEGORIES (galaxy.[galaxy_ID]) values. The galaxy table has 0 to 79 entries, and in the scouting table, each galaxy is referenced and cross-linked to at least once, so you would not expect it to miss out a category (galaxy.[galaxy_ID]). Any thoughts?