Macgyver7 <[email protected]> wrote:
> I have a table with three columns, Col1, Col2 and Meaning. I have multiple
> occurrences of distinct words within the column, Col2, with it's specific
> meaning for each word listed in the Meaning column. There are multiple
> occurrences of each distinct word and meaning combination. How do I return
> a table showing in one column each distinct word that occurs in Col2 and in
> the next column to that (or ideally the next five columns), have the top 5
> most frequently occurring meanings for the particular Col2 word?
>
> I have some associated data in Col1 and would also like to display that in
> the resulting table, before the Col2 word. Any help would be greatly
> appreciated.
select
(select Col1 from MyTable where Col2=ThisCol2),
ThisCol2,
(select Meaning from MyTable where Col2=ThisCol2
group by Meaning order by count(*) desc limit 1 offset 0),
(select Meaning from MyTable where Col2=ThisCol2
group by Meaning order by count(*) desc limit 1 offset 1),
(select Meaning from MyTable where Col2=ThisCol2
group by Meaning order by count(*) desc limit 1 offset 2),
(select Meaning from MyTable where Col2=ThisCol2
group by Meaning order by count(*) desc limit 1 offset 3),
(select Meaning from MyTable where Col2=ThisCol2
group by Meaning order by count(*) desc limit 1 offset 4)
from (select distinct Col2 as ThisCol2 from MyTable);
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
