Re: [sqlite] group_concat() on a JOIN problem

[Jay A. Kreibich]

On Mon, Jun 04, 2012 at 09:13:51PM +0200, Gert Van Assche scratched on the wall:

> SELECT DISTINCT
> group_concat( [TB].[F2],' ')  AS [Groep]
> FROM [TA] JOIN [TB] ON [TA].[Groep] = [TB].[Groep];
> 
> I get this as a result:
> Groep
> 1 2 *3 3*
> 
> but I would like to get
> Groep
> 1 2 *3*
> 
> I'm sure the JOIN is causing this doubling of elements in the grouped
> result.

  Yes, there will be two "3" rows returned, which are then fed into the
  group_concat() aggregate.  The result is only one row, however, so it
  is always distinct.


> Is there a way I could achieve this?

  You want the DISTINCT on the aggregate, not the result set:

  SELECT group_concat( DISTINCT TB.F2, ' ' ) AS


   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] group_concat() on a JOIN problem

[Pavel Ivanov]

> SELECT DISTINCT
> group_concat( [TB].[F2],' ')  AS [Groep]
> FROM [TA] JOIN [TB] ON [TA].[Groep] = [TB].[Groep];

You probably want to do the following here:

SELECT group_concat([F2],' ') AS [Groep]
FROM (
SELECT DISTINCT [TB].[F2] AS [F2]
FROM [TA] JOIN [TB] ON [TA].[Groep] = [TB].[Groep]
);


Pavel


On Mon, Jun 4, 2012 at 3:13 PM, Gert Van Assche  wrote:
> All,
>
> I'm sure this is not an SQLite bug, but I hope I can get rid of it:
>
> CREATE TABLE "TA"([Groep], F1);
> CREATE TABLE "TB"([Groep], F2);
>
> INSERT INTO [TA]([Groep], [F1]) VALUES('1', 'ABC');
> INSERT INTO [TA]([Groep], [F1]) VALUES('2', 'DE');
> INSERT INTO [TA]([Groep], [F1]) VALUES('3', 'F');
> INSERT INTO [TA]([Groep], [F1]) VALUES('3', 'G');
>
> INSERT INTO [TB]([Groep], [F2]) VALUES('1', '1');
> INSERT INTO [TB]([Groep], [F2]) VALUES('2', '2');
> INSERT INTO [TB]([Groep], [F2]) VALUES('3', '3');
>
> SELECT DISTINCT
> group_concat( [TB].[F2],' ')  AS [Groep]
> FROM [TA] JOIN [TB] ON [TA].[Groep] = [TB].[Groep];
>
> I get this as a result:
> Groep
> 1 2 *3 3*
>
> but I would like to get
> Groep
> 1 2 *3*
>
> I'm sure the JOIN is causing this doubling of elements in the grouped
> result.
> Is there a way I could achieve this?
>
>
> thanks
>
>
> Gert
> ___
> 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

[sqlite] group_concat() on a JOIN problem

[Gert Van Assche]

All,

I'm sure this is not an SQLite bug, but I hope I can get rid of it:

CREATE TABLE "TA"([Groep], F1);
CREATE TABLE "TB"([Groep], F2);

INSERT INTO [TA]([Groep], [F1]) VALUES('1', 'ABC');
INSERT INTO [TA]([Groep], [F1]) VALUES('2', 'DE');
INSERT INTO [TA]([Groep], [F1]) VALUES('3', 'F');
INSERT INTO [TA]([Groep], [F1]) VALUES('3', 'G');

INSERT INTO [TB]([Groep], [F2]) VALUES('1', '1');
INSERT INTO [TB]([Groep], [F2]) VALUES('2', '2');
INSERT INTO [TB]([Groep], [F2]) VALUES('3', '3');

SELECT DISTINCT
group_concat( [TB].[F2],' ')  AS [Groep]
FROM [TA] JOIN [TB] ON [TA].[Groep] = [TB].[Groep];

I get this as a result:
Groep
1 2 *3 3*

but I would like to get
Groep
1 2 *3*

I'm sure the JOIN is causing this doubling of elements in the grouped
result.
Is there a way I could achieve this?


thanks


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