This is not related to any specific database. It is more about
relational-algebra.

My question is: How can I write a query to find the most frequent item in a
column of a relation?

Say I have a relation about several guys knowing a certain number of foregin
languages. How can I project the guy who speaks the max number of foreign
languages?


person foreign language

John Spanish
Brian Spanish
Charlie Spanish
John Japanese
Brian French
Brian German


Obviously Brian speaks three foreign languages, more than anyone else in the
relation table does.


How can I project Brian with a relational-algebra statement, perhaps with
the help with the aggregate functions like max, min, count, etc?

John wrote:

Assuming table JUNK created as:

create table junk (person varchar(18),language varchar(18))


having following rows:

select * from junk

PERSON LANGUAGE
------------------ ------------------
John Spanish
Brian Spanish
Charlie Spanish
John Japanese
Brian French
Brian German

6 record(s) selected.


query like this is what you are probably trying to achieve:

select person, count(language) as Languages from junk group by person

PERSON LANGUAGES
------------------ -----------
Brian 3
Charlie 1
John 2

3 record(s) selected.


Jan M. Nelken

Then how do I project "Brian" with the last result set? with a max function?

BTW, how can I translante "select person, count(language) as Languages from
junk group by person" into relational-algebra?


"Jan M. Nelken" <Unknown.User (AT) Invalid (DOT) Domain> wrote

On Mon, 02 Jul 2007 16:18:44 -0400, John wrote:
Most portable:
-------------

1. Create a view:
create view langs_summed as
select person,count(*) as cnt
from langs group by person

2. Use that view to find the person with highest count:
select person,cnt
from langs_summed
where cnt=(select max(cnt) from langs_summed)

Without using a view, using a common table expression (CTE):
-----------------------------------------------------------

with langs_summed (person,cnt) as
(
select person,count(*) as cnt
from langs
group by person
)
select * from langs_summed
where cnt=(select max(cnt) from langs_summed)


My memory of relational algebra (RA) isn't good enough to even try to
express aggregate functions in RA.

--
Regards,
Troels Arvin <troels (AT) arvin (DOT) dk>
http://troels.arvin.dk/

John wrote:


Perhaps this will do:

select person, count(language) as Languages from junk group by person
order by languages desc fetch first 1 row only

PERSON LANGUAGES
------------------ -----------
Brian 3

1 record(s) selected.


Translation of this SELECT into relational algebra is left as exercise
to the reader...


Jan M. Nelken

John wrote:
I trust this is not homework?

select person, count(language) as Languages
from junk
group by person
having count(language) = (
select max(cnt) from (
select person, count(language) as cnt
from junk group by person
) X
)

DB2 has a construction called Common Table Expression which is neat for
such queries:

with T (person, cnt) as (
select person, count(language) as Languages
from junk group by person
) select person, cnt from T where cnt = (select max(cnt) from T)

I'm afraid I wont be of much help when it comes to the algebra



/Lennart

If there are more than one person who speaks the max number of foreign
languages, "FETCH FIRST 1 ROW ONLY" will not result all persons who
speaks the max number of foreign languages.

In such case, it will be nessesary to use Lennart's way or Troels
Arvin's way.

Another way is to use join, like this.
SELECT j1.person
, COUNT(DISTINCT j1.language) languages
FROM junk j1
, (SELECT COUNT(language)
FROM junk
GROUP BY person
) j2(languages)
GROUP BY
j1.person
HAVING COUNT(DISTINCT j1.language)
= MAX(languages);