OT : help needed with a mySQL request

jbv at souslelogo.com jbv at souslelogo.com
Fri Nov 22 18:16:22 CET 2013


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
>>
>>





More information about the use-livecode mailing list