OT : help needed with a mySQL request

[John Craig]

You can also do (maybe easier to read);

mysql> SELECT * FROM data HAVING (SELECT COUNT(id) FROM data AS data2 
WHERE name = data.name AND year < 2013) = 0;
+----+--------+------+
| id | name   | year |
+----+--------+------+
|  8 | Katy   | 2013 |
|  9 | Tracey | 2013 |
+----+--------+------+

And to get the count (2 results in this case);

mysql> SELECT COUNT(id) FROM (SELECT * FROM data HAVING (SELECT 
COUNT(id) FROM data AS data2 WHERE name = data.name AND year < 2013) = 
0) AS derivedTable;
+-----------+
| COUNT(id) |
+-----------+
|         2 |
+-----------+

There will be more solutions - this will either fit your requirements or 
at least give you a start  ;)



On 22/11/2013 17:16, 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
>