Thanks for the suggestion, it works great.
> R Smith <mailto:rsmith at rsweb.co.za>
> January 30, 2016 at 6:13 AM
>
>
>
>
> A few ways come to mind, here's an easy one that works for me:
>
>
> WITH RNDCAT(cat) AS ( -- CTE to list 20 random categories
> SELECT DISTINCT formulas.majorcategory FROM formulas ORDER BY
> RANDOM() LIMIT 20
> ), RNDFRM (cat, pin) AS ( -- CTE to randomize the formula list
> SELECT majorcategory, pinyin FROM formulas ORDER BY RANDOM()
> ), RNDVAL(cat, pin) AS ( -- CTE to link one random-list formula to
> each category
> SELECT RNDCAT.cat, (SELECT pin FROM RNDFRM WHERE
> RNDFRM.cat=RNDCAT.cat LIMIT 1) FROM RNDCAT
> ) -- Final select to show values and names
> SELECT RNDVAL.pin AS Pinyin, RNDVAL.cat AS Category_ID, MC.item_name
> AS Category
> FROM RNDVAL
> LEFT JOIN majorfcategory AS MC ON MC.id=RNDVAL.cat
> ORDER BY RNDVAL.cat
> ;
>
> Cheers,
> Ryan
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> Gary Baranzini <mailto:conanjb at gmail.com>
> January 29, 2016 at 6:51 AM
> Hi,
>
> I have a query where I select 20 random distinct rows.
>
> SELECT DISTINCT formulas.pinyin, formulas.majorcategory,
> majorfcategory.item_name
> FROM formulas
> JOIN majorfcategory ON majorfcategory.id=formulas.majorcategory
> ORDER BY RANDOM() LIMIT 20
>
> Each row contains a major category id, "majorcategory"
> I want to improve on this query so that the select will NOT return
> duplicate major categories
>
> I thought I could do:
>
> SELECT DISTINCT formulas.pinyin, formulas.majorcategory,
> majorfcategory.item_name
> FROM formulas
> JOIN majorfcategory ON majorfcategory.id=formulas.majorcategory
> WHERE formulas.majorcategory in (SELECT DISTINCT
> formulas.majorcategory FROM formulas) ORDER BY RANDOM() LIMIT 20
>
> But that doesn't work.
>
> Any suggestions will be greatly appreciated.
>
> jb
