CODEWITHSUNDEEP
sqlsqlite
pm100
pm100

Reputation: 50210

selecting a column sorted by count but without the count

table is like this, say it represents various types of events in a log file

<type> <date>

I want to select the top 5 most common types

select type,count(type) as c from log order by c desc limit 5

this works fine, but I only want the type column so I can use this is a where in subquery. How do I do that? I cant work out how to supress the count colum

Upvotes: 3

Views: 825

Answers (4)

sorpigal
sorpigal

Reputation: 26106

You didn't specify the RDBMS and this is highly dependent on which one you're using. Here are some options.

-- works in postgres and mysql
select type from log group by type order by count(*) desc limit 5;

-- this variant works in mssql but probably no where else (maybe sybase)
select top(5) type from log group by type order by count(*) desc;

-- works in posgres and mssqlserver but not mysql or oracle
select
      type
  from (select
        type,
        row_number() over (order by count(*) desc) as r
    from
        log
    group by
        type
  ) as t
  where
    r <= 5
;

-- portable to all standards compliant RDMS
select
      type
  from (select
        type,
        row_number() over (order by c) as r
    from
        (select
                type,
                count(*) as c
            from
                log
            group by
                type
        ) as t
  ) as t
  where
    r <= 5
;

-- works if you don't have windowing functions, but returns more than 5 rows
select
        type
    from
        (select
                type,
                count(*) as c
            from
                log
            group by
                type
        ) as t
    order by
        c desc
;

Upvotes: 1

Orel Eraki
Orel Eraki

Reputation: 12196

Pretty straightforward:

SELECT type FROM log GROUP BY type ORDER BY COUNT(type) DESC LIMIT 5

Upvotes: 3

Steve Cooper
Steve Cooper

Reputation: 21490

not used sqlite, but I'd write something like this in SQL server; maybe there's some idea you could steal?

select top (5) type from log group by type order by count(type) desc

Upvotes: 0

Aaron Dietz
Aaron Dietz

Reputation: 10277

Just make this a subquery too:

SELECT TYPE
FROM LOG
WHERE TYPE IN
    (select type,count(type) as c 
     from log 
     order by c desc limit 5)

Upvotes: 0

Related Questions