OT : help needed with a mySQL request

[Peter Haworth]

Hi,
Just put together a quick database using John's data and the query I sent
earlier works great, at least in sqlite.  To get a count of the number of
entries, the query is:

SELECT count(*) FROM (SELECT Text FROM Table GROUP BY Text HAVING
Date='2013' AND count(Text)=1)

Pete
lcSQL Software <http://www.lcsql.com>


On Fri, Nov 22, 2013 at 9:16 AM, <jbv at souslelogo.com> wrote:

> John,
>
> Thank you so much : I just tested the request you sent and compared it
> with the results I got with several successive requests and everything
> is consistent...
>
> If I may, I have one last question though :
> what if I want, in the same single request, to output the number of
> distinct names, instead of the full list of entries with all columns ?
> In your example the output should be "1".
> I tried
>    SELECT COUNT(DISTINCT name)
> at the beginning of the request, but the result was empty...
>
> Thanks again,
> jbv
>
> > If I understrand correctly, you have data similar to below;
> >
> > mysql> select * from data;
> > +----+------+------+
> > | id | name | year |
> > +----+------+------+
> > |  1 | John | 1972 |
> > |  2 | John | 2006 |
> > |  3 | John | 2010 |
> > |  4 | John | 2013 |
> > |  5 | Kyle | 2006 |
> > |  6 | Kyle | 2010 |
> > |  7 | Kyle | 2013 |
> > |  8 | Katy | 2013 |
> > +----+------+------+
> >
> > and you only want the 'Katy' record.  Here's one possible query;
> >
> > mysql> SELECT *, (SELECT COUNT(id) FROM data AS data2 WHERE name =
> > data.name AND year < 2013) AS yCount FROM data WHERE year = 2013 HAVING
> > yCount = 0;
> > +----+------+------+--------+
> > | id | name | year | yCount |
> > +----+------+------+--------+
> > |  8 | Katy | 2013 |      0 |
> > +----+------+------+--------+
> >
> >
> > :D
> >
> >
> > On 22/11/2013 15:47, jbv at souslelogo.com wrote:
> >> Hi list,
> >>
> >> I have a table with a Date column (among others).
> >> Each entry has a date that ranges from 1930 to 2013.
> >> For several entries, the content of other columns can be similar,
> >> only the Date value changes. For instance I can have
> >>   aaa 2004
> >>   aaa 2013
> >> I need to find with 1 single request all entries with Date = 2013
> >> that don't exist in the table with other Date values.
> >> I tried SELECT * FROM myTable WHERE MIN(Date) = 2013
> >> but that returns an error #1111 - Invalid use of group function
> >>
> >> Any help would be much apreciated.
> >> Thanks in advance.
> >> jbv
> >>
> >>
>
>
>
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>