I have a table where I keep track of my employee's weight and the date 
their weight was taken.  Here is the table.

mysql> select * from Employees;
+------------+-----------------+--------+
| EmployeeID | DateWeightTaken | Weight |
+------------+-----------------+--------+
|          1 | 2011-01-01      |    100 |
|          1 | 2011-02-01      |    110 |
|          1 | 2011-03-01      |    120 |
|          2 | 2011-01-01      |    200 |
|          2 | 2011-02-01      |    210 |
|          2 | 2011-03-01      |    220 |
|          2 | 2011-04-01      |    230 |
+------------+-----------------+--------+

I would like to query the table to select the most recent weights for 
all my employees.

I have not been able to get a Google solution.  Any references or 
pointers would be appreciated.

Thank you,
Joe

On 8/8/2011 11:37 AM, Joseph Hesse wrote:
> I have a table where I keep track of my employee's weight and the date
> their weight was taken. Here is the table.
>
> mysql> select * from Employees;
> +------------+-----------------+--------+
> | EmployeeID | DateWeightTaken | Weight |
> +------------+-----------------+--------+
> | 1 | 2011-01-01 | 100 |
> | 1 | 2011-02-01 | 110 |
> | 1 | 2011-03-01 | 120 |
> | 2 | 2011-01-01 | 200 |
> | 2 | 2011-02-01 | 210 |
> | 2 | 2011-03-01 | 220 |
> | 2 | 2011-04-01 | 230 |
> +------------+-----------------+--------+
>
> I would like to query the table to select the most recent weights for
> all my employees.
>
> I have not been able to get a Google solution. Any references or
> pointers would be appreciated.
>
> Thank you,
> Joe
>

add "ORDER BY DateWeightTaken DESC LIMIT HowManyYouWantReturned" to the 
end of your query.

-- 
Shelly

On 08/08/2011 10:46 AM, sheldonlg wrote:
> On 8/8/2011 11:37 AM, Joseph Hesse wrote:
>> I have a table where I keep track of my employee's weight and the date
>> their weight was taken. Here is the table.
>>
>> mysql> select * from Employees;
>> +------------+-----------------+--------+
>> | EmployeeID | DateWeightTaken | Weight |
>> +------------+-----------------+--------+
>> | 1 | 2011-01-01 | 100 |
>> | 1 | 2011-02-01 | 110 |
>> | 1 | 2011-03-01 | 120 |
>> | 2 | 2011-01-01 | 200 |
>> | 2 | 2011-02-01 | 210 |
>> | 2 | 2011-03-01 | 220 |
>> | 2 | 2011-04-01 | 230 |
>> +------------+-----------------+--------+
>>
>> I would like to query the table to select the most recent weights for
>> all my employees.
>>
>> I have not been able to get a Google solution. Any references or
>> pointers would be appreciated.
>>
>> Thank you,
>> Joe
>>
>
> add "ORDER BY DateWeightTaken DESC LIMIT HowManyYouWantReturned" to the
> end of your query.
>
This doesn't quite work.  The query
SELECT * from Employees ORDER BY DateWeightTaken DESC LIMIT 1;
returns only one item.  I want a query that would produce:
1 2011-03-01 120
2 2011-04-01 230
Thank you.

Joseph Hesse:

>I would like to query the table to select the most recent weights for
>all my employees.

You've been asking almost the same question in June, and been given:
http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html



-- 
Erick

On 08/08/2011 11:05 AM, Erick T. Barkhuis wrote:
> Joseph Hesse:
>
>> I would like to query the table to select the most recent weights for
>> all my employees.
>
> You've been asking almost the same question in June, and been given:
> http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html
>
>
>
Sorry, I have egg on my face.
Joe

On 2011-08-08 17:59, Joseph Hesse wrote:
> On 08/08/2011 10:46 AM, sheldonlg wrote:
>> On 8/8/2011 11:37 AM, Joseph Hesse wrote:
>>> I have a table where I keep track of my employee's weight and the date
>>> their weight was taken. Here is the table.
>>>
>>> mysql> select * from Employees;
>>> +------------+-----------------+--------+
>>> | EmployeeID | DateWeightTaken | Weight |
>>> +------------+-----------------+--------+
>>> | 1 | 2011-01-01 | 100 |
>>> | 1 | 2011-02-01 | 110 |
>>> | 1 | 2011-03-01 | 120 |
>>> | 2 | 2011-01-01 | 200 |
>>> | 2 | 2011-02-01 | 210 |
>>> | 2 | 2011-03-01 | 220 |
>>> | 2 | 2011-04-01 | 230 |
>>> +------------+-----------------+--------+
>>>
>>> I would like to query the table to select the most recent weights for
>>> all my employees.
>>>
>>> I have not been able to get a Google solution. Any references or
>>> pointers would be appreciated.
>>>
>>> Thank you,
>>> Joe
>>>
>>
>> add "ORDER BY DateWeightTaken DESC LIMIT HowManyYouWantReturned" to the
>> end of your query.
>>
> This doesn't quite work.  The query
> SELECT * from Employees ORDER BY DateWeightTaken DESC LIMIT 1;
> returns only one item.  I want a query that would produce:
> 1 2011-03-01 120
> 2 2011-04-01 230
> Thank you.

Assuming EmployeeID, DateWeightTaken is a candidate key

select EmployeeID, max(DateWeightTaken) as DateWeightTaken
from Employees
group by EmployeeID

will give you the last date per employee. You can join this with
Employees to determine the weight.


/Lennart

On 08/08/2011 12:38 PM, Lennart Jonsson wrote:
> On 2011-08-08 17:59, Joseph Hesse wrote:
>> On 08/08/2011 10:46 AM, sheldonlg wrote:
>>> On 8/8/2011 11:37 AM, Joseph Hesse wrote:
>>>> I have a table where I keep track of my employee's weight and the date
>>>> their weight was taken. Here is the table.
>>>>
>>>> mysql>  select * from Employees;
>>>> +------------+-----------------+--------+
>>>> | EmployeeID | DateWeightTaken | Weight |
>>>> +------------+-----------------+--------+
>>>> | 1 | 2011-01-01 | 100 |
>>>> | 1 | 2011-02-01 | 110 |
>>>> | 1 | 2011-03-01 | 120 |
>>>> | 2 | 2011-01-01 | 200 |
>>>> | 2 | 2011-02-01 | 210 |
>>>> | 2 | 2011-03-01 | 220 |
>>>> | 2 | 2011-04-01 | 230 |
>>>> +------------+-----------------+--------+
>>>>
>>>> I would like to query the table to select the most recent weights for
>>>> all my employees.
>>>>
>>>> I have not been able to get a Google solution. Any references or
>>>> pointers would be appreciated.
>>>>
>>>> Thank you,
>>>> Joe
>>>>
>>>
>>> add "ORDER BY DateWeightTaken DESC LIMIT HowManyYouWantReturned" to the
>>> end of your query.
>>>
>> This doesn't quite work.  The query
>> SELECT * from Employees ORDER BY DateWeightTaken DESC LIMIT 1;
>> returns only one item.  I want a query that would produce:
>> 1 2011-03-01 120
>> 2 2011-04-01 230
>> Thank you.
>
> Assuming EmployeeID, DateWeightTaken is a candidate key
>
> select EmployeeID, max(DateWeightTaken) as DateWeightTaken
> from Employees
> group by EmployeeID
>
> will give you the last date per employee. You can join this with
> Employees to determine the weight.
>
>
> /Lennart
Thank you,
That was very helpful.
Joe