Geek asks and answers

SQL TOP results grouped by field

I have a problem with SQL query. I have a list of 145 site names. At each site, certain species were found. I created an account of how many times each species was seen on each site. I want to find the 5 most common species observed on each site. I currently have:

SELECT TOP 5 Count([bird point counts bound query].[Group size]) AS [CountOfGroup size], [bird point counts bound query].site, [bird point counts bound query].Species
FROM [bird point counts bound query]
GROUP BY [bird point counts bound query].site, [bird point counts bound query].Species
ORDER BY Count([bird point counts bound query].[Group size]) DESC;

this returns only the 5 most common species from all sites. To clarify, with 5 results for each site and 145 sites, the final table should contain 725 entries. I am currently working in Access.

any help rated as SQL is not my forte.

+3
source share
1 answer

As far as I can do this, you only get this with the temp table in Access.

Something like that?

CREATE TABLE ##SiteSpecies (
  id            int IDENTITY (1,1),
  Site          <whatever>,
  Species       <whatever>,
  Observations  int
)

INSERT INTO ##SiteSpecies
SELECT   [bird point counts bound query].site, [bird point counts bound query].Species, Count([bird point counts bound query].[Group size])
FROM     [bird point counts bound query]
GROUP BY [bird point counts bound query].site, [bird point counts bound query].Species
ORDER BY [bird point counts bound query].site, [bird point counts bound query].Species, Count([bird point counts bound query].[Group size])

SELECT
  results.*
FROM
  ##SiteSpecies        AS results
INNER JOIN
(
  SELECT Site, MIN(id) AS FirstID FROM ##SiteSpecies GROUP BY Site
)
  AS SiteMarkers
    ON  results.Site  = SiteMarkers.Site
    AND results.id   <= SiteMarkers.FirstID + 4
0
source

More articles:


All Articles