Re: [sqlite] appending the output of a query

[Igor Tandetnik]

On 12/2/2014 2:46 PM, Paul Sanderson wrote:

So what I really need is to
a) ensure that the ordering of the recursive query is guranteed, i.e.
the order follows the ID/parent relationship to the root
b) a method of concatenating this in reverse order


WITH RECURSIVE rcte AS (
  SELECT 0 as level, parent, FileName
  FROM rtable
  WHERE ID = 510
UNION ALL
  SELECT rcte.level + 1 as level, rtable.parent, rtable.FileName
  FROM rcte JOIN rtable ON rcte.parent = rtable.ID
  WHERE rtable.FileName <> '.')
SELECT FileName
FROM rcte
ORDER BY level desc;

--
Igor Tandetnik

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

Re: [sqlite] appending the output of a query

[Paul Sanderson]

Hm thanks - actually thinking about it sorting by ID doesn't work as
it is quite likely that the folder order doesn't follow the id/parent
order, i.e. a parent folder could have an ID greater than one or more
of its children.

So what I really need is to
a) ensure that the ordering of the recursive query is guranteed, i.e.
the order follows the ID/parent relationship to the root
b) a method of concatenating this in reverse order

This is an academic exercise so a solution is not actually required
but interesting non the less


Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy



On 2 December 2014 at 19:36, Igor Tandetnik  wrote:
> On 12/2/2014 1:47 PM, Paul Sanderson wrote:
>>
>> WITH RECURSIVE rcte AS (SELECT rtable.ID,
>>rtable.parent,
>>rtable.FileName
>>  FROM rtable
>>  WHERE rtable.ID = 510
>>  UNION ALL
>>  SELECT rtable.ID,
>>rtable.parent,
>>rtable.FileName
>>  FROM rcte
>>INNER JOIN rtable ON rcte.parent = rtable.ID
>>  WHERE rtable.FileName <> '.'
>>  LIMIT 20)
>> SELECT Group_Concat(rcte.FileName, '\') AS col1
>> FROM rcte
>> ORDER BY rcte.ID
>>
>> This however appends the path in the wrong order, i.e. I get the file
>> name first and the root folder last
>
>
> Try this:
>
> select group_concat(FileName, '\') as col1 from (
>   WITH RECURSIVE rcte ...
>  SELECT rcte.FileName as FileName
>  FROM rcte
>  ORDER BY rcte.ID
> );
>
> That is, select individual rows, put them in the right order, then do
> group_concat as an extra layer on top.
>
> I don't believe it's technically guaranteed that rows will be grouped in the
> right order, but it's very likely to work in practice.
> --
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] appending the output of a query

[Igor Tandetnik]

On 12/2/2014 1:47 PM, Paul Sanderson wrote:

WITH RECURSIVE rcte AS (SELECT rtable.ID,
   rtable.parent,
   rtable.FileName
 FROM rtable
 WHERE rtable.ID = 510
 UNION ALL
 SELECT rtable.ID,
   rtable.parent,
   rtable.FileName
 FROM rcte
   INNER JOIN rtable ON rcte.parent = rtable.ID
 WHERE rtable.FileName <> '.'
 LIMIT 20)
SELECT Group_Concat(rcte.FileName, '\') AS col1
FROM rcte
ORDER BY rcte.ID

This however appends the path in the wrong order, i.e. I get the file
name first and the root folder last


Try this:

select group_concat(FileName, '\') as col1 from (
  WITH RECURSIVE rcte ...
 SELECT rcte.FileName as FileName
 FROM rcte
 ORDER BY rcte.ID
);

That is, select individual rows, put them in the right order, then do 
group_concat as an extra layer on top.


I don't believe it's technically guaranteed that rows will be grouped in 
the right order, but it's very likely to work in practice.

--
Igor Tandetnik

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

Re: [sqlite] appending the output of a query

[Paul Sanderson]

Thanks Richard

That helps but it seems I over simplified in order to get the help I
thought I needed :)

My actual query follows a MFT entry from an NTFS file system as such

WITH RECURSIVE rcte AS (SELECT rtable.ID,
  rtable.parent,
  rtable.FileName
FROM rtable
WHERE rtable.ID = 510
UNION ALL
SELECT rtable.ID,
  rtable.parent,
  rtable.FileName
FROM rcte
  INNER JOIN rtable ON rcte.parent = rtable.ID
WHERE rtable.FileName <> '.'
LIMIT 20)
SELECT Group_Concat(rcte.FileName, '\') AS col1
FROM rcte
ORDER BY rcte.ID

This however appends the path in the wrong order, i.e. I get the file
name first and the root folder last

Changing the sort order on the ORDE BY to DESC when using group_Concat
has no affect but when the Group-Concat is ommitted I get the correct
ordering. I understand why, but I can't see how I can get the sort
applied before the Group_Concat is called - I think this might be what
John is referring to.

Thanks


Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy



On 2 December 2014 at 18:27, John McKown  wrote:
> On Tue, Dec 2, 2014 at 11:51 AM, Richard Hipp  wrote:
>
>> On Tue, Dec 2, 2014 at 12:46 PM, Paul Sanderson <
>> sandersonforens...@gmail.com> wrote:
>>
>> > I have a query that returns one column but a number of rows so for
>> instance
>> >
>> > SELECT name from tab
>> >
>> > might return
>> >
>> > a
>> > b
>> > c
>> > d
>> >
>> > I would like to append these terms and get a single line/string
>> >
>> > a_b_c_d
>> >
>> >
>> > I want to just use a single SQL query to do this, is it possible?
>> >
>>
>> SELECT group_concat(name,'_') FROM tab;
>>
>
> Just a bit curious, but is there a way to ensure a particular order on the
> "name" column values? I.e. isn't it possible that the result might be
> a_d_b_c under some circumstance?
>
>
>
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>>
>>
>
> --
> The temperature of the aqueous content of an unremittingly ogled
> culinary vessel will not achieve 100 degrees on the Celsius scale.
>
> Maranatha! <><
> John McKown
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] appending the output of a query

[John McKown]

On Tue, Dec 2, 2014 at 11:51 AM, Richard Hipp  wrote:

> On Tue, Dec 2, 2014 at 12:46 PM, Paul Sanderson <
> sandersonforens...@gmail.com> wrote:
>
> > I have a query that returns one column but a number of rows so for
> instance
> >
> > SELECT name from tab
> >
> > might return
> >
> > a
> > b
> > c
> > d
> >
> > I would like to append these terms and get a single line/string
> >
> > a_b_c_d
> >
> >
> > I want to just use a single SQL query to do this, is it possible?
> >
>
> SELECT group_concat(name,'_') FROM tab;
>

​Just a bit curious, but is there a way to ensure a particular order on the
"name" column values? I.e. isn't it possible that the result might be
a_d_b_c under some circumstance?​



>
> --
> D. Richard Hipp
> d...@sqlite.org
>
>

-- 
The temperature of the aqueous content of an unremittingly ogled
culinary vessel will not achieve 100 degrees on the Celsius scale.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] appending the output of a query

[Richard Hipp]

On Tue, Dec 2, 2014 at 12:46 PM, Paul Sanderson <
sandersonforens...@gmail.com> wrote:

> I have a query that returns one column but a number of rows so for instance
>
> SELECT name from tab
>
> might return
>
> a
> b
> c
> d
>
> I would like to append these terms and get a single line/string
>
> a_b_c_d
>
>
> I want to just use a single SQL query to do this, is it possible?
>

SELECT group_concat(name,'_') FROM tab;



>
>
>
>
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
> -Forensic Toolkit for SQLite
> http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
> processing made easy
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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