Re: [sqlite] recursive common table expression, an example

[Petite Abeille]

On Feb 3, 2014, at 11:05 PM, big stone  wrote:

> group_concat is indeed super nice ! I didn't notice that little jewel of
> SQLite, thank you.

You are welcome. 

But *do* read the very fine prints associated with that aggregate function:

http://www.sqlite.org/lang_aggfunc.html

“… The order of the concatenated elements is arbitrary… “ … which, well, 
renders it pretty much useless for anything but display purpose… and even then… 
sigh...

> Is there a "standardized" SQL normalization for that ?

No. Not that I know of.

> (I see that oracle has a LISTAGG instead)

Yes, LISTAGG [1] is much more usable in that respect as it sports a WITHIN 
GROUP ( ORDER BY … ) clause which makes is more predictable.

[1] http://docs.oracle.com/cd/E11882_01/server.112/e17118/functions089.htm

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] recursive common table expression, an example

[big stone]

group_concat is indeed super nice ! I didn't notice that little jewel of
SQLite, thank you.

Is there a "standardized" SQL normalization for that ? (I see that oracle
has a LISTAGG instead)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] recursive common table expression, an example

[Petite Abeille]

On Feb 3, 2014, at 10:11 PM, big stone  wrote:

>  bag colors  bag1 blue - red - yellow  bag2 green - yellow

Does that really require a recursive query? Wouldn’t a simple group by + 
group_concat do as well?

with
DataSet
as
(
  select 'bag1' as bag, 'blue' as color union all
  select 'bag1' as bag, 'red' as color union all
  select 'bag1' as bag, 'yellow' as color union all
  select 'bag2' as bag, 'green' as color union all
  select 'bag2' as bag, 'yellow' as color 
)
selectDataSet.bag as bag,
  group_concat( DataSet.color, ' - ' ) as colors
from  DataSet

group by  DataSet.bag

order by  DataSet.bag;


P.S.

I’m starting to develop a strong aversion to that ‘values( … )’ syntax… sigh... 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] recursive common table expression, an example

[big stone]

Other usage  = A workaround to  "For XML PATH" (see
http://www.sqlusa.com/bestpractices/training/scripts/commadelimitedlist/ )

List of colors of a bag :
**

with colors(bag,color) as
(values('bag1','blue'),('bag1','red'),('bag2','green'),('bag2','yellow'),('bag1','yellow'))
   , bags (bag, colors, last) as (
   select  bag , min(color) as colors, min(color) as last from colors
group by bag
   union all
   select b.bag, colors ||' - ' ||  color  ,  color  from bags b ,
colors l
where b.bag=l.bag and l.color=(
select min(color) from colors as x where b.bag=x.bag and
b.last gives :
*
  bag colors  bag1 blue - red - yellow  bag2 green - yellow
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] recursive common table expression, an example

[Petite Abeille]

Now that 3.8.3 is officially out, we can all play with these nice little common 
table expressions! Yeah!

So, while solving sudoku puzzles is all fine and dandy, the bread and butter of 
recursive queries is more along the lines of plain, old hierarchies.

So, let create one:

  select  'A' as node, null as parent union all
  select  'B' as node, 'A' as parent union all
  select  'C' as node, 'B' as parent union all
  select  'D' as node, 'C' as parent 

A simple hierarchy, each node with one parent, the root node without one: A → B 
→ C → D. Nice and easy.

Let recurse!

with
DataSet
as
(
  select  'A' as node, null as parent union all
  select  'B' as node, 'A' as parent union all
  select  'C' as node, 'B' as parent union all
  select  'D' as node, 'C' as parent 
),
Hierarchy( node, parent, level, path )
as
(
  select  DataSet.node,
  DataSet.parent,
  1 as level,
  ' → ' || DataSet.node as path
  from DataSet
  whereDataSet.parent is null

  union all
  select  DataSet.node,
  DataSet.parent,
  Hierarchy.level + 1 as level,
  Hierarchy.path || ' → ' || DataSet.node as path
  fromHierarchy
  joinDataSet
  on  DataSet.parent = Hierarchy.node
)
select*
from  Hierarchy

order by  path;

node|parent|level|path
A||1| → A
B|A|2| → A → B
C|B|3| → A → B → C
D|C|4| → A → B → C → D

Beautiful. For each node, we get its level and full path, recursively. And 
that’s all there is to it. Very nice.

Recursive or not, common table expressions are your friend. Use them ☺


N.B.

One word of caution about circular recursion though… as it stands, SQLite 
doesn’t have any build-in mechanism to detect circularity… and will happily get 
into a funk and run forever if given the opportunity… so… watch out…

Little demonstration:

with
DataSet
as
(
  select  'A' as node, 'A' as parent
),
Hierarchy( node, parent, level, path )
as
(
  select  DataSet.node,
  DataSet.parent,
  1 as level,
  ' → ' || DataSet.node as path
  from DataSet

  union
  select  DataSet.node,
  DataSet.parent,
  Hierarchy.level + 1 as level,
  Hierarchy.path || ' → ' || DataSet.node as path
  fromHierarchy
  joinDataSet
  on  DataSet.parent = Hierarchy.node
)
select*
from  Hierarchy

order by  path;









___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users