It seems that it is not possible to specify the concatenation separator
when using GROUP_CONCAT with DISTINCT.
For example while this works
SELECT pub_id, GROUP_CONCAT(cate_id, " - ")
FROM book_mast
GROUP BY pub_id;
and this works
SELECT pub_id, GROUP_CONCAT(DISTINCT cate_id)
FROM book_mast
Thanks all for your input, it has really helped.
In my real world application tmp_keep is a temporary table populated by
examinining a number of other tables etc., and I suddenly realsied that it
could even contain duplicate ids. Sloppy thinking on my part.
I get the best results by creating
Could be keep almost all the records so ~50, but it varies greatly so
sometimes will be just keep 10. I can adjust approach depending on size if
necessary.
Yes the id1 are integer primary keys.
Table1 has a number of indexes and views, so the create new table approach
is less attractive
Say table1 has more then 50 records, and there is a second table
tmp_keep with the ids of the records in table1 to be kept, the rest need to
be deleted. The number of records in tmp_keep can vary from 0 to all the
records in table1, with any values in between.
What is the best strategy for
Some simple testing is showing using an EXISTS statement is generally
quicker then using an IN
e.g.
SELECT * FROM tablea
WHERE EXISTS (SELECT 1 FROM tableb WHERE tablea.id = tableb.id AND ...)
is quicker than
SELECT * FROM tablea
WHERE tablea.id IN (SELECT tableb.id FROM tableb WHERE ...)
Is
Looking for the best way to query a table with an integer column by value
of the lower 16 bits of the data in that column. Does SQLite support
bitwise logic?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
Can I confirm that defining feilds as VARCHAR(20) rather than TEXT, if I
know I only want 20 chars or less, will result in a smaller database?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
is to avoid the left join by
ensuring that all songs have at least one artist. This is a better data
design in the end.
On 14 February 2016 at 18:00, Clemens Ladisch wrote:
> Dave Blake wrote:
> > Is there anything I can do to get the optimiser to perform 1) with the
> same
> > efficie
5:15, Clemens Ladisch wrote:
> Dave Blake wrote:
> > What I see as wrong is that in 1) (and 4) ) we have a query of the form
> > A LEFT JOIN B WHERE clause involving index fields on A
> >
> > yet the optimiser does not search A, the outer table, first using the
>
table first. This is
> necessary for the join to work correctly.
But it is not scanning song, the outer table, first. It is not optimising
correctly.
Is there a way with 1) to get it to scan song first? Can you see why that
would be the optimal plan?
On 14 February 2016 at 10:49, Cleme
urrent behaviour is making views
unusable in my application.
On 12 February 2016 at 20:47, Clemens Ladisch wrote:
> Dave Blake wrote:
> >> It chooses a _correct_ plan.
> >
> > Really? With query 1) to select a song it first scans the song_artist
> table
> &
:34, Clemens Ladisch wrote:
> Dave Blake wrote:
> > I noticed my queries going very slowly after changing a join to a left
> > join, examination of the query plan showed that the optimiser was
> choosing
> > a poor plan.
>
> It chooses a _correct_ plan.
>
> &
I noticed my queries going very slowly after changing a join to a left
join, examination of the query plan showed that the optimiser was choosing
a poor plan. It only occurs when the left join is on a views, if explicit
tables are used instead then the problem does not occur.
To repeat the issue
13 matches
Mail list logo
