SQLite – How to Properly Filter Columns After Applying Aggregate Function

sqlitesyntax

I find that I need to apply an aggregate a lot, but I don't want to show the aggregated value as well. So the best way to do this was to just nest the query to filter it out (like below) but there has to be a better way, how?

select state
from(
    select state, max(cnt)
    from (
        select state, count(*) as cnt
        from customer
        group by state) as t
) as r;

Here I just want to show state without the max value.

Best Answer

I think you can achieve this result without so many subqueries:

Select State
  From Customer
  Group By State
  Order By Count(*) Desc
  Limit 1;

Related Solutions

SQLite – How to Select All Rows with a Minimum Value

As you have seen, a simple GROUP BY will not work because it would return only one record per group.

Your join works fine. For a large table, it will be efficient only if there is an index on the join columns (num and text).

Alternatively, you could use a correlated subquery:

SELECT *
FROM t
WHERE num = (SELECT MIN(num)
             FROM t AS t2
             WHERE t2.text = t.text);

SQLFiddle

When being executed, this query does not require a temporary table (your query does for the result of u), but will execute the subquery for each record in t, so text should be indexed. (Or use an index on both text and num to get a covering index.)

Nesting MAX for SELECT

It can be done with a query:

SELECT 
    s.*
FROM
  ( SELECT DISTINCT series_id
    FROM series
  ) AS ds
  JOIN
    series AS s
      ON  s.series_id = ds.series_id
      AND s.season =
          ( SELECT si.season
            FROM series AS si
            WHERE si.series_id = ds.series_id
            ORDER BY si.season DESC
            LIMIT 1
          ) 
      AND s.episode =
          ( SELECT si.episode
            FROM series AS si
            WHERE si.series_id = ds.series_id
            ORDER BY si.season DESC, si.episode DESC
            LIMIT 1
          ) ;

Tested at SQL-Fiddle

With an index on (series_id, season, episode), it should be quite efficient, if the number of series_id is small.

CREATE UNIQUE INDEX uix
  ON series
  (series_id, season, episode) ;

There are several ways to do this query but all get rather complicated because the maximum needed is based on two columns (season and episode). Here's another one, which is the query above, simplified a bit:

SELECT 
    s.*
FROM
  ( SELECT series_id, MAX(season) AS season
    FROM series
    GROUP BY series_id
  ) AS ds
  JOIN
    series AS s
      ON  s.series_id = ds.series_id
      AND s.season = ds.season
      AND s.episode =
          ( SELECT si.episode
            FROM series AS si
            WHERE si.series_id = ds.series_id
              AND si.season = ds.season
            ORDER BY si.episode DESC
            LIMIT 1
          ) ;

and another using NOT EXISTS:

SELECT s.*
FROM series AS s
WHERE NOT EXISTS
      ( SELECT 1
        FROM series AS sb
        WHERE sb.series_id = s.series_id
          AND ( sb.season > s.season
             OR sb.season = s.season AND sb.episode > s.episode
              )
      ) ;

Best Answer

Related Solutions

SQLite – How to Select All Rows with a Minimum Value

Nesting MAX for SELECT

Related Question