one possibility: use || for concatenation.
e.g. "(" || select(..) ||"," select() ||")"
maybe there is a cleaner way in sqlite.
-sean
-----Original Message-----
From: [email protected] [mailto:[email protected]]
On Behalf Of Macgyver7
Sent: Friday, December 02, 2011 4:04 PM
To: [email protected]
Subject: Re: [sqlite] Most Frequent Occurrence Problem
Many thanks Igor, that worked beautifully. If I wanted to put the three top
meanings in one column separated by commas, instead of three columns, is
there an easy way to do that? It would be good to have them in brackets too.
eg. (meaning 1, meaning 2, meaning 3)
Thanks for the help,
Tim.
Igor Tandetnik wrote:
>
> Macgyver7 <[email protected]
> 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
>
>
--
View this message in context:
http://old.nabble.com/Most-Frequent-Occurrence-Problem-tp32900981p32906113.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
-----------------------------------------------------------------------------------
This email message is for the sole use of the intended recipient(s) and may
contain
confidential information. Any unauthorized review, use, disclosure or
distribution
is prohibited. If you are not the intended recipient, please contact the
sender by
reply email and destroy all copies of the original message.
-----------------------------------------------------------------------------------
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
