Re: grouping by the difference between values in rows

[Takeshi Hashimoto]

For me, it seems just use [case - when ] on the difference between x and y, and 
group by with output. 

Good luck *\(^o^)/*

Sent from my iPhone

On Jan 21, 2014, at 15:38, h...@tbbs.net wrote:

> 2014/01/12 14:17 -0500, Larry Martell 
> I've been asked to do something that I do not think is possible in SQL.
> 
> I have a query that has this basic form:
> 
> SELECT a, b, c, d, AVG(e), STD(e), CONCAT(x, ',', y) as f
> FROM t
> GROUP BY a, b, c, d, f
> 
> x and y are numbers (378.18, 2213.797 or 378.218, 2213.949 or
> 10053.490, 2542.094).
> 
> The business issue is that if either x or y in 2 rows that are in the
> same a, b, c, d group are within 1 of each other then they should be
> grouped together. And to make it more complicated, the tolerance is
> applied as a rolling continuum. For example, if the x and y in a set
> of grouped rows are:
> 
> row 1: 1.5, 9.5
> row 2: 2.4, 20.8
> row 3: 3.3, 40.6
> row 4: 4.2, 2.5
> row 5: 5.1, 10.1
> row 6: 6.0, 7.9
> row 7: 8.0, 21.0
> row 8: 100, 200
> 
> 1 through 6 get combined because all their X values are within the
> tolerance of some other X in the set that's been combined. 7's Y value
> is within the tolerance of 2's Y, so that should be combined as well.
> 8 is not combined because neither the X or Y value is within the
> tolerance of any X or Y in the set that was combined.
> 
> In python I can easily parse the data and identify the rows that need
> to be combined, but then I've lost the ability to calculate the
> average and std. The only way I can think of to do this is to remove
> the grouping from the SQL and do all the grouping and aggregating
> myself. But this query often returns 20k to 30k rows after grouping.
> It could easily be 80k to 100k rows that I have to process if I remove
> the grouping and I think that will be very slow.
> 
> Anyone have any ideas?
> 
> I suspect you can carry out their ideas by something like this, in an SQL 
> procedure:
> 
> Besides your table "t", there are tables "t1", "t2", "t3", "tpair", and "tq".
> 
> With a cursor copy records from "t" to "t1" (with all of "t" s fields and an  
> "g1" besides) ordered by a, b, c, d, x, going through all the complications 
> of deciding where a group boundary falls, numbering the groups by "g1".
> 
> Repeat this copying from "t1" to "t2" (which has besides "g1" also "g2"), 
> ordered by a, b, c, d, y, numbering the groups by "g2". Now "t1" no longer is 
> needed.
> 
> Copy all distinct pairs of "g1" and "g2" to "tpair".
> 
> Until g = MIN(g1) of "tpair" is null, move (INSERT ... SELECT; DELETE ...) 
> from "tpair" to "tq" all pairs where g1=g, and as long as there is anything 
> to move from "tpair" to "tq" alternatly move records where any in "tpair" s 
> "g2" match any already in "tq" and any in "tpair" s "g1" match any already in 
> "tq". Now all the pairs in "tq" represent the same group: every record in 
> "t2" with a pair in "tq" is copied into "t3" with a new number "g3" instead 
> of the pair.
> 
> After this one may group "t3" by "g3".
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
> 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: grouping by the difference between values in rows

[hsv]

 2014/01/12 14:17 -0500, Larry Martell 
I've been asked to do something that I do not think is possible in SQL.

I have a query that has this basic form:

SELECT a, b, c, d, AVG(e), STD(e), CONCAT(x, ',', y) as f
FROM t
GROUP BY a, b, c, d, f

x and y are numbers (378.18, 2213.797 or 378.218, 2213.949 or
10053.490, 2542.094).

The business issue is that if either x or y in 2 rows that are in the
same a, b, c, d group are within 1 of each other then they should be
grouped together. And to make it more complicated, the tolerance is
applied as a rolling continuum. For example, if the x and y in a set
of grouped rows are:

row 1: 1.5, 9.5
row 2: 2.4, 20.8
row 3: 3.3, 40.6
row 4: 4.2, 2.5
row 5: 5.1, 10.1
row 6: 6.0, 7.9
row 7: 8.0, 21.0
row 8: 100, 200

1 through 6 get combined because all their X values are within the
tolerance of some other X in the set that's been combined. 7's Y value
is within the tolerance of 2's Y, so that should be combined as well.
8 is not combined because neither the X or Y value is within the
tolerance of any X or Y in the set that was combined.

In python I can easily parse the data and identify the rows that need
to be combined, but then I've lost the ability to calculate the
average and std. The only way I can think of to do this is to remove
the grouping from the SQL and do all the grouping and aggregating
myself. But this query often returns 20k to 30k rows after grouping.
It could easily be 80k to 100k rows that I have to process if I remove
the grouping and I think that will be very slow.

Anyone have any ideas?

I suspect you can carry out their ideas by something like this, in an SQL 
procedure:

Besides your table "t", there are tables "t1", "t2", "t3", "tpair", and "tq".

With a cursor copy records from "t" to "t1" (with all of "t" s fields and an  
"g1" besides) ordered by a, b, c, d, x, going through all the complications of 
deciding where a group boundary falls, numbering the groups by "g1".

Repeat this copying from "t1" to "t2" (which has besides "g1" also "g2"), 
ordered by a, b, c, d, y, numbering the groups by "g2". Now "t1" no longer is 
needed.

Copy all distinct pairs of "g1" and "g2" to "tpair".

Until g = MIN(g1) of "tpair" is null, move (INSERT ... SELECT; DELETE ...) from 
"tpair" to "tq" all pairs where g1=g, and as long as there is anything to move 
from "tpair" to "tq" alternatly move records where any in "tpair" s "g2" match 
any already in "tq" and any in "tpair" s "g1" match any already in "tq". Now 
all the pairs in "tq" represent the same group: every record in "t2" with a 
pair in "tq" is copied into "t3" with a new number "g3" instead of the pair.

After this one may group "t3" by "g3".


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: grouping by the difference between values in rows

[Larry Martell]

On Wed, Jan 15, 2014 at 4:06 PM, shawn l.green  wrote:
> Hello Larry,
>
>
> On 1/13/2014 2:43 AM, Peter Brawley wrote:
>>
>>
>> On 2014-01-12 9:13 PM, Larry Martell wrote:
>>>
>>> On Sun, Jan 12, 2014 at 2:47 PM, Peter Brawley
>>>  wrote:
>>>>
>>>> On 2014-01-12 1:17 PM, Larry Martell wrote:
>>>>>
>>>>> I've been asked to do something that I do not think is possible in SQL.
>>>>>
>>>>> I have a query that has this basic form:
>>>>>
>>>>> SELECT a, b, c, d, AVG(e), STD(e), CONCAT(x, ',', y) as f
>>>>> FROM t
>>>>> GROUP BY a, b, c, d, f
>>>>>
>>>>> x and y are numbers (378.18, 2213.797 or 378.218, 2213.949 or
>>>>> 10053.490, 2542.094).
>>>>>
>>>>> The business issue is that if either x or y in 2 rows that are in the
>>>>> same a, b, c, d group are within 1 of each other then they should be
>>>>> grouped together. And to make it more complicated, the tolerance is
>>>>> applied as a rolling continuum. For example, if the x and y in a set
>>>>> of grouped rows are:
>>>>>
>>>>> row 1: 1.5, 9.5
>>>>> row 2: 2.4, 20.8
>>>>> row 3: 3.3, 40.6
>>>>> row 4: 4.2, 2.5
>>>>> row 5: 5.1, 10.1
>>>>> row 6: 6.0, 7.9
>>>>> row 7: 8.0, 21.0
>>>>> row 8: 100, 200
>>>>>
>>>>> 1 through 6 get combined because all their X values are within the
>>>>> tolerance of some other X in the set that's been combined. 7's Y value
>>>>> is within the tolerance of 2's Y, so that should be combined as well.
>>>>> 8 is not combined because neither the X or Y value is within the
>>>>> tolerance of any X or Y in the set that was combined.
>>>>>
>>>>> In python I can easily parse the data and identify the rows that need
>>>>> to be combined, but then I've lost the ability to calculate the
>>>>> average and std. The only way I can think of to do this is to remove
>>>>> the grouping from the SQL and do all the grouping and aggregating
>>>>> myself. But this query often returns 20k to 30k rows after grouping.
>>>>> It could easily be 80k to 100k rows that I have to process if I remove
>>>>> the grouping and I think that will be very slow.
>>>>>
>>>>> Anyone have any ideas?
>>>>
>>>>
>>>> Could you compute the row-to-row values & write them to a temp table,
>>>> then
>>>> run the SQL that incorporates that result column?
>>>
>>> I thought of temp tables, but I could not come up with a way to use
>>> them for this. How can I apply the x/y tolerance grouping in sql?
>>
>>
>> Run the query you showed, saving the result to a temp table. In an sproc
>> or your preferred app language, do the row-to-row processing to generate
>> a new column in the temp table from the biz rules you outlined, now
>> query the revised temp table as desired.
>>
>
> This is a very interesting problem but I am not sure what to do with a
> grouping. To me this appears to be a two-dimensional, nearest-neighbor-type
> sorting problem.
>
> The way you outline it you could end up with all of the elements in your
> analysis plane in a single group depending on how closely they are to each
> other. Are long strings of points and blobs (clusters of points) in your 2-D
> search space (your plane is defined by your {a,b,c,d} tuple) acceptable
> results of your re-grouping process?
>
> If I had to draw this out graphically, you are stamping each of your {x,y}
> points with a 2x2 boundary box (each point is in intersection in the middle)
> and you want to know which sets of those boxes form a contiguous
> intersection area (they overlap).
>
> Is that what you are looking for? One technique would be to start with
> Peter's suggestion. Then reprocess that list to generate an acyclic directed
> graph (by eliminating the reverse matches from your set), then tracing down
> the tree.  If point 2 links to point 7,then 7 also links to 2 (both would be
> found by your nearness test. Eliminate any matches where the first point
> appears after the second point from your list.


Thanks much for the reply Shawn. Unfortunately this project was put on
the back burner. When I get back to it I'll be sure to try the
suggestions given here and I will report back as to what worked.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: grouping by the difference between values in rows

[shawn l.green]

Hello Larry,

On 1/13/2014 2:43 AM, Peter Brawley wrote:


On 2014-01-12 9:13 PM, Larry Martell wrote:

On Sun, Jan 12, 2014 at 2:47 PM, Peter Brawley
 wrote:

On 2014-01-12 1:17 PM, Larry Martell wrote:

I've been asked to do something that I do not think is possible in SQL.

I have a query that has this basic form:

SELECT a, b, c, d, AVG(e), STD(e), CONCAT(x, ',', y) as f
FROM t
GROUP BY a, b, c, d, f

x and y are numbers (378.18, 2213.797 or 378.218, 2213.949 or
10053.490, 2542.094).

The business issue is that if either x or y in 2 rows that are in the
same a, b, c, d group are within 1 of each other then they should be
grouped together. And to make it more complicated, the tolerance is
applied as a rolling continuum. For example, if the x and y in a set
of grouped rows are:

row 1: 1.5, 9.5
row 2: 2.4, 20.8
row 3: 3.3, 40.6
row 4: 4.2, 2.5
row 5: 5.1, 10.1
row 6: 6.0, 7.9
row 7: 8.0, 21.0
row 8: 100, 200

1 through 6 get combined because all their X values are within the
tolerance of some other X in the set that's been combined. 7's Y value
is within the tolerance of 2's Y, so that should be combined as well.
8 is not combined because neither the X or Y value is within the
tolerance of any X or Y in the set that was combined.

In python I can easily parse the data and identify the rows that need
to be combined, but then I've lost the ability to calculate the
average and std. The only way I can think of to do this is to remove
the grouping from the SQL and do all the grouping and aggregating
myself. But this query often returns 20k to 30k rows after grouping.
It could easily be 80k to 100k rows that I have to process if I remove
the grouping and I think that will be very slow.

Anyone have any ideas?


Could you compute the row-to-row values & write them to a temp table,
then
run the SQL that incorporates that result column?

I thought of temp tables, but I could not come up with a way to use
them for this. How can I apply the x/y tolerance grouping in sql?


Run the query you showed, saving the result to a temp table. In an sproc
or your preferred app language, do the row-to-row processing to generate
a new column in the temp table from the biz rules you outlined, now
query the revised temp table as desired.



This is a very interesting problem but I am not sure what to do with a 
grouping. To me this appears to be a two-dimensional, 
nearest-neighbor-type sorting problem.


The way you outline it you could end up with all of the elements in your 
analysis plane in a single group depending on how closely they are to 
each other. Are long strings of points and blobs (clusters of points) in 
your 2-D search space (your plane is defined by your {a,b,c,d} tuple) 
acceptable results of your re-grouping process?


If I had to draw this out graphically, you are stamping each of your 
{x,y} points with a 2x2 boundary box (each point is in intersection in 
the middle) and you want to know which sets of those boxes form a 
contiguous intersection area (they overlap).


Is that what you are looking for? One technique would be to start with 
Peter's suggestion. Then reprocess that list to generate an acyclic 
directed graph (by eliminating the reverse matches from your set), then 
tracing down the tree.  If point 2 links to point 7,then 7 also links to 
2 (both would be found by your nearness test. Eliminate any matches 
where the first point appears after the second point from your list.


Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: grouping by the difference between values in rows

[Larry Martell]

On Mon, Jan 13, 2014 at 2:43 AM, Peter Brawley
 wrote:
>
> On 2014-01-12 9:13 PM, Larry Martell wrote:
>>
>> On Sun, Jan 12, 2014 at 2:47 PM, Peter Brawley
>>  wrote:
>>>
>>> On 2014-01-12 1:17 PM, Larry Martell wrote:

 I've been asked to do something that I do not think is possible in SQL.

 I have a query that has this basic form:

 SELECT a, b, c, d, AVG(e), STD(e), CONCAT(x, ',', y) as f
 FROM t
 GROUP BY a, b, c, d, f

 x and y are numbers (378.18, 2213.797 or 378.218, 2213.949 or
 10053.490, 2542.094).

 The business issue is that if either x or y in 2 rows that are in the
 same a, b, c, d group are within 1 of each other then they should be
 grouped together. And to make it more complicated, the tolerance is
 applied as a rolling continuum. For example, if the x and y in a set
 of grouped rows are:

 row 1: 1.5, 9.5
 row 2: 2.4, 20.8
 row 3: 3.3, 40.6
 row 4: 4.2, 2.5
 row 5: 5.1, 10.1
 row 6: 6.0, 7.9
 row 7: 8.0, 21.0
 row 8: 100, 200

 1 through 6 get combined because all their X values are within the
 tolerance of some other X in the set that's been combined. 7's Y value
 is within the tolerance of 2's Y, so that should be combined as well.
 8 is not combined because neither the X or Y value is within the
 tolerance of any X or Y in the set that was combined.

 In python I can easily parse the data and identify the rows that need
 to be combined, but then I've lost the ability to calculate the
 average and std. The only way I can think of to do this is to remove
 the grouping from the SQL and do all the grouping and aggregating
 myself. But this query often returns 20k to 30k rows after grouping.
 It could easily be 80k to 100k rows that I have to process if I remove
 the grouping and I think that will be very slow.

 Anyone have any ideas?
>>>
>>>
>>> Could you compute the row-to-row values & write them to a temp table,
>>> then
>>> run the SQL that incorporates that result column?
>>
>> I thought of temp tables, but I could not come up with a way to use
>> them for this. How can I apply the x/y tolerance grouping in sql?
>
>
> Run the query you showed, saving the result to a temp table. In an sproc or
> your preferred app language, do the row-to-row processing to generate a new
> column in the temp table from the biz rules you outlined, now query the
> revised temp table as desired.

Not too clear on how this will help me. The issue with the query I
showed is that I don't have the individual rows that make up the
aggregate data of the rows I need to combine. I think I have to run a
query with no group by and do all the grouping and aggregation myself.

In any case, unfortunately this has been made a low priority task and
I've been put on to something else (I hate when they do that). I'll
revive this thread when I'm allowed to get back on this.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: grouping by the difference between values in rows

[Peter Brawley]

On 2014-01-12 9:13 PM, Larry Martell wrote:

On Sun, Jan 12, 2014 at 2:47 PM, Peter Brawley
 wrote:

On 2014-01-12 1:17 PM, Larry Martell wrote:

I've been asked to do something that I do not think is possible in SQL.

I have a query that has this basic form:

SELECT a, b, c, d, AVG(e), STD(e), CONCAT(x, ',', y) as f
FROM t
GROUP BY a, b, c, d, f

x and y are numbers (378.18, 2213.797 or 378.218, 2213.949 or
10053.490, 2542.094).

The business issue is that if either x or y in 2 rows that are in the
same a, b, c, d group are within 1 of each other then they should be
grouped together. And to make it more complicated, the tolerance is
applied as a rolling continuum. For example, if the x and y in a set
of grouped rows are:

row 1: 1.5, 9.5
row 2: 2.4, 20.8
row 3: 3.3, 40.6
row 4: 4.2, 2.5
row 5: 5.1, 10.1
row 6: 6.0, 7.9
row 7: 8.0, 21.0
row 8: 100, 200

1 through 6 get combined because all their X values are within the
tolerance of some other X in the set that's been combined. 7's Y value
is within the tolerance of 2's Y, so that should be combined as well.
8 is not combined because neither the X or Y value is within the
tolerance of any X or Y in the set that was combined.

In python I can easily parse the data and identify the rows that need
to be combined, but then I've lost the ability to calculate the
average and std. The only way I can think of to do this is to remove
the grouping from the SQL and do all the grouping and aggregating
myself. But this query often returns 20k to 30k rows after grouping.
It could easily be 80k to 100k rows that I have to process if I remove
the grouping and I think that will be very slow.

Anyone have any ideas?


Could you compute the row-to-row values & write them to a temp table, then
run the SQL that incorporates that result column?

I thought of temp tables, but I could not come up with a way to use
them for this. How can I apply the x/y tolerance grouping in sql?


Run the query you showed, saving the result to a temp table. In an sproc 
or your preferred app language, do the row-to-row processing to generate 
a new column in the temp table from the biz rules you outlined, now 
query the revised temp table as desired.


PB

-






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: grouping by the difference between values in rows

[Larry Martell]

On Sun, Jan 12, 2014 at 2:47 PM, Peter Brawley
 wrote:
> On 2014-01-12 1:17 PM, Larry Martell wrote:
>>
>> I've been asked to do something that I do not think is possible in SQL.
>>
>> I have a query that has this basic form:
>>
>> SELECT a, b, c, d, AVG(e), STD(e), CONCAT(x, ',', y) as f
>> FROM t
>> GROUP BY a, b, c, d, f
>>
>> x and y are numbers (378.18, 2213.797 or 378.218, 2213.949 or
>> 10053.490, 2542.094).
>>
>> The business issue is that if either x or y in 2 rows that are in the
>> same a, b, c, d group are within 1 of each other then they should be
>> grouped together. And to make it more complicated, the tolerance is
>> applied as a rolling continuum. For example, if the x and y in a set
>> of grouped rows are:
>>
>> row 1: 1.5, 9.5
>> row 2: 2.4, 20.8
>> row 3: 3.3, 40.6
>> row 4: 4.2, 2.5
>> row 5: 5.1, 10.1
>> row 6: 6.0, 7.9
>> row 7: 8.0, 21.0
>> row 8: 100, 200
>>
>> 1 through 6 get combined because all their X values are within the
>> tolerance of some other X in the set that's been combined. 7's Y value
>> is within the tolerance of 2's Y, so that should be combined as well.
>> 8 is not combined because neither the X or Y value is within the
>> tolerance of any X or Y in the set that was combined.
>>
>> In python I can easily parse the data and identify the rows that need
>> to be combined, but then I've lost the ability to calculate the
>> average and std. The only way I can think of to do this is to remove
>> the grouping from the SQL and do all the grouping and aggregating
>> myself. But this query often returns 20k to 30k rows after grouping.
>> It could easily be 80k to 100k rows that I have to process if I remove
>> the grouping and I think that will be very slow.
>>
>> Anyone have any ideas?
>
>
> Could you compute the row-to-row values & write them to a temp table, then
> run the SQL that incorporates that result column?

I thought of temp tables, but I could not come up with a way to use
them for this. How can I apply the x/y tolerance grouping in sql?

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: grouping by the difference between values in rows

[Peter Brawley]

On 2014-01-12 1:17 PM, Larry Martell wrote:

I've been asked to do something that I do not think is possible in SQL.

I have a query that has this basic form:

SELECT a, b, c, d, AVG(e), STD(e), CONCAT(x, ',', y) as f
FROM t
GROUP BY a, b, c, d, f

x and y are numbers (378.18, 2213.797 or 378.218, 2213.949 or
10053.490, 2542.094).

The business issue is that if either x or y in 2 rows that are in the
same a, b, c, d group are within 1 of each other then they should be
grouped together. And to make it more complicated, the tolerance is
applied as a rolling continuum. For example, if the x and y in a set
of grouped rows are:

row 1: 1.5, 9.5
row 2: 2.4, 20.8
row 3: 3.3, 40.6
row 4: 4.2, 2.5
row 5: 5.1, 10.1
row 6: 6.0, 7.9
row 7: 8.0, 21.0
row 8: 100, 200

1 through 6 get combined because all their X values are within the
tolerance of some other X in the set that's been combined. 7's Y value
is within the tolerance of 2's Y, so that should be combined as well.
8 is not combined because neither the X or Y value is within the
tolerance of any X or Y in the set that was combined.

In python I can easily parse the data and identify the rows that need
to be combined, but then I've lost the ability to calculate the
average and std. The only way I can think of to do this is to remove
the grouping from the SQL and do all the grouping and aggregating
myself. But this query often returns 20k to 30k rows after grouping.
It could easily be 80k to 100k rows that I have to process if I remove
the grouping and I think that will be very slow.

Anyone have any ideas?


Could you compute the row-to-row values & write them to a temp table, 
then run the SQL that incorporates that result column?


PB

-






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: grouping and limiting results and rand()

[Peter Brawley]

On 2013-09-23 8:10 PM, Jeremiah Jester wrote:

Hello,

How would i go about selecting 5
random cars that are flagged as internet_special (value 1) for each
dealer from a specific table?  Ive tried sub selects with no luck.
Here's a basic query that has my
required conditional. Note that I get all unique dealers by doing a
distinct(dealer_web_name). Make sense? Using mysql 5.

select dealer_web_name,id,internet_special,active from inventory where
internet_special=1 and active=1;

Appreciate the help


For ideas see "Within-group quotas" at 
http://www.artfulsoftware.com/infotree/queries.php.


PB

-

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: grouping

[Brent Baisley]

It sounds to me like you might be trying to find the standard  
deviation or the variance, which are functions you can use right in  
your query.



On Nov 2, 2007, at 7:37 AM, Octavian Rasnita wrote:


Hi,

I have a table with the following columns:

symbol
date
value

I want to select all the symbols (grouping by symbols) between 2  
specified dates, and calculate the percent of change for each symbol.


The percent of change is (the value from the last date of the  
symbol - the value from the first date) / the value from the first  
date.


For example I have:

SMB1, 2007-01-01, 1000
SMB1, 2007-03-15, 2100
SMB1, 2007-10-10, 1300
... (other symbols)

And the result of the select should be:
SMB1, 0.3

Is it possible to do this with an MySQL query?

Thank you.

Octavian


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql? 
[EMAIL PROTECTED]





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: grouping

[Baron Schwartz]

Hi,

Octavian Rasnita wrote:

Hi,

I have a table with the following columns:

symbol
date
value

I want to select all the symbols (grouping by symbols) between 2 
specified dates, and calculate the percent of change for each symbol.


The percent of change is (the value from the last date of the symbol - 
the value from the first date) / the value from the first date.


For example I have:

SMB1, 2007-01-01, 1000
SMB1, 2007-03-15, 2100
SMB1, 2007-10-10, 1300
... (other symbols)

And the result of the select should be:
SMB1, 0.3

Is it possible to do this with an MySQL query?


Yes.  You need to find the first row per group and the last row per 
group, then join these two results so the values are all in the same 
row.  Then you can just do the math as usual.


I will assume the primary key is on (symbol, date).  This won't work if 
there are duplicated symbols and dates.  Find the extrema:


select symbol, min(date) as mindate, max(date) as maxdate
from tbl
group by symbol

Now join the table against this result, and do the math in the SELECT list:

select fl.symbol, (tbl_l.value - tbl_f.value)/tbl_f.value
from (
   select symbol, min(date) as mindate, max(date) as maxdate
   from tbl
   group by symbol
) as fl
   inner join tbl as tbl_f
  on fl.symbol=tbl_f.symbol and fl.mindate = tbl_f.date
   inner join tbl as tbl_l
  on fl.symbol=tbl_l.symbol and fl.maxdate = tbl_l.date

Disclaimer: I haven't tried this code.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Grouping Question

[YL]

Thanks a lot CJ.  That's the cost of flexibility:-)
  - Original Message - 
  From: Christian High 
  To: YL 
  Cc: mysql@lists.mysql.com 
  Sent: Sunday, April 22, 2007 12:45 PM
  Subject: Re: Grouping Question



  On 4/22/07, YL <[EMAIL PROTECTED]> wrote: 
I have a contact table looks like

id type owner_idowner_type   value
11email21 person   [EMAIL PROTECTED]
12phone   21 person  303-777-
13PO Box18 business   220
14cell   21 person  101-202-3344 
.

The reason for such a table is that I get room for any kind of contact 
info. even future ones.

For practical reasons, I need a view from the above to contain only the 
following info

owner_idemailphone 


such that the owner_type = 'person' and column owner_id has no duplications

what the sql should be to create such a view?

thanks a lot


  As far as I know you will need 3 views to accomplish this
  view 1 will get the phone number and owner id together and the create 
statement should look like this

  CREATE VIEW contact_phone AS
  SELECT owner_id, value as phone_number
  FROM contact_info
  WHERE `type` = 'phone';

  view 2 will get the owner_id and the phone number together and should look 
like this

  CREATE VIEW contact_email AS
  SELECT owner_id, value as email_address
  FROM contact_info
  WHERE type = 'email';

  view 3 will pull the owner_id, email_address, and phone_number together in 
one row and should look like this

  CREATE VIEW contact_view AS
  SELECT contact_phone.owner_id, contact_phone.phone_number as phone_number, 
contact_email.email_address as email_address
  FROM contact_phone
  JOIN contact_email ON
  (contact_phone.owner_id = contact_email.owner_id);

  CJ

   

Re: Grouping Question

[Christian High]

On 4/22/07, Christian High <[EMAIL PROTECTED]> wrote:



 On 4/22/07, YL <[EMAIL PROTECTED]> wrote:
>
> I have a contact table looks like
>
> id type owner_idowner_type   value
> 11email21 person   [EMAIL PROTECTED]
> 12phone   21 person  303-777-
> 13PO Box18 business   220
> 14cell   21 person  101-202-3344
> .
>
> The reason for such a table is that I get room for any kind of contact
> info. even future ones.
>
> For practical reasons, I need a view from the above to contain only the
> following info
>
> owner_idemailphone
>
>
> such that the owner_type = 'person' and column owner_id has no
> duplications
>
> what the sql should be to create such a view?
>
> thanks a lot



 As far as I know you will need 3 views to accomplish this
view 1 will get the phone number and owner id together and the create
statement should look like this

CREATE VIEW contact_phone AS
SELECT owner_id, value as phone_number
FROM contact_info
WHERE `type` = 'phone';

view 2 will get the owner_id and the phone number together and should look
like this

CREATE VIEW contact_email AS
SELECT owner_id, value as email_address
FROM contact_info
WHERE type = 'email';

view 3 will pull the owner_id, email_address, and phone_number together in
one row and should look like this

CREATE VIEW contact_view AS
SELECT contact_phone.owner_id, contact_phone.phone_number as phone_number,
contact_email.email_address as email_address
FROM contact_phone
JOIN contact_email ON
(contact_phone.owner_id = contact_email.owner_id);

CJ

 of course i forgot that you wanted only owner_type personal so just add
that to the end of the WHERE clause on the contact_phone and contact_email
views like



AND owner_type = 'personal'

CJ

Re: Grouping Question

[Christian High]

On 4/22/07, YL <[EMAIL PROTECTED]> wrote:


I have a contact table looks like

id type owner_idowner_type   value
11email21 person  [EMAIL PROTECTED]
12phone   21 person  303-777-
13PO Box18 business   220
14cell   21 person  101-202-3344
.

The reason for such a table is that I get room for any kind of contact
info. even future ones.

For practical reasons, I need a view from the above to contain only the
following info

owner_idemailphone


such that the owner_type = 'person' and column owner_id has no
duplications

what the sql should be to create such a view?

thanks a lot




As far as I know you will need 3 views to accomplish this
view 1 will get the phone number and owner id together and the create
statement should look like this

CREATE VIEW contact_phone AS
SELECT owner_id, value as phone_number
FROM contact_info
WHERE `type` = 'phone';

view 2 will get the owner_id and the phone number together and should look
like this

CREATE VIEW contact_email AS
SELECT owner_id, value as email_address
FROM contact_info
WHERE type = 'email';

view 3 will pull the owner_id, email_address, and phone_number together in
one row and should look like this

CREATE VIEW contact_view AS
SELECT contact_phone.owner_id, contact_phone.phone_number as phone_number,
contact_email.email_address as email_address
FROM contact_phone
JOIN contact_email ON
(contact_phone.owner_id = contact_email.owner_id);

CJ

Re: grouping

[Peter Brawley]

>select column1, column2, max(column3) as maximum
>from table_name group by column 1;

>Please tell me if the values from "column2" will contain the values from
>those records where the column3 has the maximum value.

They will not.

>please tell me how to do this as fast as possible.

What's fastest depends on your table, indexes &c. Here is one way...

select
 t1.column1,
 (select column2 from table_name t2 where t2.column1=t1.column1) as 
column2,

 max(t1.column3) as maximum
from table_name t1
group by column1;

and here is another, usually faster.

select t1.column1, t1.column2, t1.column3
from table_name t1
left join table_name t2 on t1.column1=t2.column1 and t1.column3
Hi,

I want to use:

select column1, column2, max(column3) as maximum from table_name group 
by column 1;


Please tell me if the values from "column2" will contain the values 
from those records where the column3 has the maximum value.
If it doesn't, please tell me how to do this as fast as possible. I 
know that I could get the maximum values, than make another query and 
get the values from the lines that have that max value, but I think 
this will take too much time.


Thank you.

Octavian





--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.411 / Virus Database: 268.17.34/679 - Release Date: 2/10/2007


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Grouping based on state changes

[SGreen]

I think all you need is a GROUP BY. Here is a tutorial sample from the 
manual:
http://dev.mysql.com/doc/mysql/en/Counting_rows.html

Here is page that describes the full SELECT syntax, including GROUP BY
http://dev.mysql.com/doc/mysql/en/SELECT.html

And here are all of the other functions you can use with GROUP BY
http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html

Just guessing but your query should look something like:

select state
, avg(value) as mean
, count(value) as population
, std(value) as std_deviation
, variance(value) as variance
from timeseries_data_table
group by state

Uh- OH I just re-read your example and realized that I had the 
situation wrong. The second set of state=0 records would be grouped in 
with the first pair. Without some other means of differentiating one group 
of state values from another group __by the data__ and not __by their 
position__, SQL cannot accomplish what you want.  The second pair of 
state=0 records is different from the first pair only because the records 
before them had a different state value (state=1). SQL is not meant to 
process information in this type of linear fashion. A cursor-based query 
_may_  be available to you IF you are running the bleeding edge MySQL 
server (5.x+).  I don't run that version so I can't tell you what's 
working yet and what isn't. 

IMHO, I believe you will need to script a solution that scrolls through 
the records in sequence in order to detect the change in state and compute 
each "group"'s statistics on the fly.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

[EMAIL PROTECTED] wrote on 09/22/2004 07:48:45 AM:

> I have the following table definition for time series data:
> 
> ID (int)  time (DATETIME)state  (int)value (int)
> 
> I want to make a state based grouping and calculate the mean of
> each grouping. The state based grouping should be done by creating a new
> group whenever the state changes, from one point in time to another.
> 
> To explain what I mean I have made a small example:
> 
> ID  time   state  value
> 1   2004-01-01 00:00   0  5
> 2   2004-01-01 00:02   0  3
> 3   2004-01-01 00:04   1  7
> 4   2004-01-01 00:07   1  9
> 5   2004-01-01 00:08   1  2
> 6   2004-01-01 00:10   0  2
> 7   2004-01-01 00:12   0  1
> 8   2004-01-01 00:13   2  2
> 9   2004-01-01 00:14   2  4
> 10  2004-01-01 00:15   2  2
> 
> The grouping for the above table would then be: (1,2);
> (3,4,5); (6,7); (8,9,10), as the state changed at ID 3, 6, and 8.
> 
> How do express a select statement in SQL which gives me the mean of each
> group?
> 
> Greetings,
> 
> Mads Lindstrøm
> 
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 

Re: Grouping based on state changes

[Michael Stassen]

Use GROUP BY:
  SELECT state, AVG(value) FROM yourtable GROUP BY state;
See the manual for all the details 
.

Michael
[EMAIL PROTECTED] wrote:
I have the following table definition for time series data:
ID (int)  time (DATETIME)state  (int)value (int)
I want to make a state based grouping and calculate the mean of
each grouping. The state based grouping should be done by creating a new
group whenever the state changes, from one point in time to another.
To explain what I mean I have made a small example:
ID  time   state  value
1   2004-01-01 00:00   0  5
2   2004-01-01 00:02   0  3
3   2004-01-01 00:04   1  7
4   2004-01-01 00:07   1  9
5   2004-01-01 00:08   1  2
6   2004-01-01 00:10   0  2
7   2004-01-01 00:12   0  1
8   2004-01-01 00:13   2  2
9   2004-01-01 00:14   2  4
10  2004-01-01 00:15   2  2
The grouping for the above table would then be: (1,2);
(3,4,5); (6,7); (8,9,10), as the state changed at ID 3, 6, and 8.
How do express a select statement in SQL which gives me the mean of each
group?
Greetings,
Mads Lindstrøm


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: grouping / sorting etc..

[Christopher Knight]

Thanks for everybody's help!!
This way worked for me.  Clever!

Chris

... and yes... even my example was messed up
:-P

-Original Message-
From: Rudy Metzger [mailto:[EMAIL PROTECTED]
Sent: Monday, July 14, 2003 4:15 AM
To: Christopher Knight; MySQL List
Subject: RE: grouping / sorting etc..


SELECT col1, IF(count(*)!=count(col2),NULL,'BAD')
  FROM table1
 GROUP BY col1;

This ONLY works EXACLTY for the case you submitted.

Please note that your example is wrong (I think). 3 should also return
NULL, shouldn't it?

/rudy



-Original Message-
From: Christopher Knight [mailto:[EMAIL PROTECTED] 
Sent: vrijdag 11 juli 2003 17:32
To: MySQL List
Subject: grouping / sorting etc..

Im having troubles getting the results I want... can someone suggest
which way to go..
mysql 4.0

A

| 1 |  |
| 1 | BAD  |
| 2 |  |
| 3 | BAD  |
| 3 |  |
| 4 | BAD  |
| 5 |  |
| 5 | BAD  |
| 5 | BAD  |


what I want is 5 results.  If there is a NULL, then return the NULL,
else return the BAD

so basically return ...

| 1 |  |
| 2 |  |
| 3 | BAD  |
| 4 | BAD  |
| 5 |  |


Ive tried 

select * from table group by 1stcolumn = (unpredicatble results,
but returns 5 rows)
select * from talbe groub by 1stcolumn, 2ndcolumn = (returns 8
rows)

I could do the 2nd way then filter out w/ the code...
Is there a better way?
Thanks
Chris


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: grouping / sorting etc..

[Krasimir_Slaveykov]

Hello Christopher,

Friday, July 11, 2003, 6:31:33 PM, you wrote:

First select:

The results are just right what you want in your SELECT.
You have 5 different values in COLUMN1 and your select returns them.
>From second column SELECT gets first value for each value of first
column.

Second select:
It works correct too. The data in yor excample are 9 records total.
To have grouping you must have equal values in columns wich you
incuded in group-criteria.
You have only 2 records wich have the same values in grouping
criteria...so they are grouped. The rest records are different. So
it's normall second SQL to return 8 record.

I don't know what is  the main job anyway. But, if you want to exclude
some records you can use WHERE clause for example.



CK> Im having troubles getting the results I want... can someone suggest which way to 
go..
CK> mysql 4.0

CK> A
CK> 
CK> | 1 |  |
CK> | 1 | BAD  |
CK> | 2 |  |
CK> | 3 | BAD  |
CK> | 3 |  |
CK> | 4 | BAD  |
CK> | 5 |  |
CK> | 5 | BAD  |
CK> | 5 | BAD  |
CK> 

CK> what I want is 5 results.  If there is a NULL, then return the NULL, else return 
the BAD

CK> so basically return ...
CK> 
CK> | 1 |  |
CK> | 2 |  |
CK> | 3 | BAD  |
CK> | 4 | BAD  |
CK> | 5 |  |
CK> 

CK> Ive tried 

CK> select * from table group by 1stcolumn = (unpredicatble results, but 
returns 5 rows)

The results are just right what you want in your SELECT.
You have 5 different values in COLUMN1 and your select returns them.
>From second column SELECT gets first value for each value of first
column.



CK> select * from talbe groub by 1stcolumn, 2ndcolumn = (returns 8 rows)

CK> I could do the 2nd way then filter out w/ the code...
CK> Is there a better way?
CK> Thanks
CK> Chris






-- 
Best regards,
 Krasimir_Slaveykovmailto:[EMAIL PROTECTED]
Tel: ++359 2 97 666 [EMAIL PROTECTED]
Tel: ++359 2 97 66 701
Fax: ++359 2 97 66 731


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: grouping / sorting etc..

[Rudy Metzger]

SELECT col1, IF(count(*)!=count(col2),NULL,'BAD')
  FROM table1
 GROUP BY col1;

This ONLY works EXACLTY for the case you submitted.

Please note that your example is wrong (I think). 3 should also return
NULL, shouldn't it?

/rudy



-Original Message-
From: Christopher Knight [mailto:[EMAIL PROTECTED] 
Sent: vrijdag 11 juli 2003 17:32
To: MySQL List
Subject: grouping / sorting etc..

Im having troubles getting the results I want... can someone suggest
which way to go..
mysql 4.0

A

| 1 |  |
| 1 | BAD  |
| 2 |  |
| 3 | BAD  |
| 3 |  |
| 4 | BAD  |
| 5 |  |
| 5 | BAD  |
| 5 | BAD  |


what I want is 5 results.  If there is a NULL, then return the NULL,
else return the BAD

so basically return ...

| 1 |  |
| 2 |  |
| 3 | BAD  |
| 4 | BAD  |
| 5 |  |


Ive tried 

select * from table group by 1stcolumn = (unpredicatble results,
but returns 5 rows)
select * from talbe groub by 1stcolumn, 2ndcolumn = (returns 8
rows)

I could do the 2nd way then filter out w/ the code...
Is there a better way?
Thanks
Chris


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Grouping behavior question

[Anvar Hussain K.M.]

Hi Mr. Jerry,

Since Mysql does not support sub query, you will have to use temporary 
table instead.

Create temporary table tmptbl1 Select keycol, Max(datetimecol) as maxdate 
from detailtable group by Keycol;

Create temporary table tmptbl2 Select distinct T.Keycol, D.Status from 
tmptbl1 T, detailtable D Where T.Keycol = D.keycol and T.maxdate = 
D.datetimecol;

Select M.*, T2.Status From mastertable M, tmptbl2 T2 Where M.Keycol = 
T2.Keycol;

I think this is the most straight forward way to solve your problem.  I 
have not checked the above commands.

Hope this works for you.

Anvar.

At 02:41 PM 02/11/2001 +1100, you wrote:

>I often find myself in the following situation:
>
>I have a master table and a detail table, the detail table contains 
>chronologically ordered items (statuses) that relate to the master record. 
>I now need to display one line for each of the master records with the 
>latest status from the detail table.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: GROUPING

[Olivier Georg]

Hola Rodrigo,

I suggest that you use a temporary table:

CREATE tmp (
INT id,
TIME max);

INSERT INTO tmp
SELECT mytable.id, MAX(mytable.start)
FROM mytable
GROUP BY mytable.id;

and then join it to your original table:

SELECT M.*
FROM mytable M, tmp T
WHERE M.id=T.id
AND M.start=T.max;

I didn't try it, but that's what I would try.

Un saludo,

Olivier

- Original Message -
From: Rodrigo Gonzalez <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, May 30, 2001 4:56 PM
Subject: GROUPING


Hi,

I have to do a query but i don't know how to do this...

I have this table

id int
start time
stop time
...
There are 2 to 4 rows for each id

I have to select one row for each id where start is the max value between
all rows with this id

If you have an idea please let me know

Thanks



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: GROUPING

[Ravi Raman]

hi.

okay...that's a different story.
in oracle that could be done with a corellated subquery...in mysql there
really is no easy way to do something like this.
you could theoretically copy your information to a temporary table, and then
select it out, but other than that, it seems you're out of luck.
otherwise, you'll have to do this in 2 queries within your app.

if anyone can point out something i'm overlooking (either re: your request
or mysql), i'd be interested to know.

-ravi.

-Original Message-
From: Rodrigo Gonzalez [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 30, 2001 11:41 AM
To: Ravi Raman; [EMAIL PROTECTED]
Subject: Re: GROUPING


I did this, but there is a problem
I have to select all fields from the record returned, not just id and start,
i need the other fields from the record with max start do you understand?

- Original Message -
From: "Ravi Raman" <[EMAIL PROTECTED]>
To: "Rodrigo Gonzalez" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Wednesday, May 30, 2001 12:37 PM
Subject: RE: GROUPING


> hi.
>
> select id, max(start) from your_table group by id;
>
> HTH.
> -ravi.
>
> -Original Message-
> From: Rodrigo Gonzalez [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, May 30, 2001 10:57 AM
> To: [EMAIL PROTECTED]
> Subject: GROUPING
>
>
> Hi,
>
> I have to do a query but i don't know how to do this...
>
> I have this table
>
> id int
> start time
> stop time
> ...
> There are 2 to 4 rows for each id
>
> I have to select one row for each id where start is the max value between
> all rows with this id
>
> If you have an idea please let me know
>
> Thanks
>
>



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: GROUPING

[Rodrigo Gonzalez]

I did this, but there is a problem
I have to select all fields from the record returned, not just id and start,
i need the other fields from the record with max start do you understand?

- Original Message -
From: "Ravi Raman" <[EMAIL PROTECTED]>
To: "Rodrigo Gonzalez" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Wednesday, May 30, 2001 12:37 PM
Subject: RE: GROUPING


> hi.
>
> select id, max(start) from your_table group by id;
>
> HTH.
> -ravi.
>
> -Original Message-
> From: Rodrigo Gonzalez [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, May 30, 2001 10:57 AM
> To: [EMAIL PROTECTED]
> Subject: GROUPING
>
>
> Hi,
>
> I have to do a query but i don't know how to do this...
>
> I have this table
>
> id int
> start time
> stop time
> ...
> There are 2 to 4 rows for each id
>
> I have to select one row for each id where start is the max value between
> all rows with this id
>
> If you have an idea please let me know
>
> Thanks
>
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: GROUPING

[Ravi Raman]

hi.

select id, max(start) from your_table group by id;

HTH.
-ravi.

-Original Message-
From: Rodrigo Gonzalez [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 30, 2001 10:57 AM
To: [EMAIL PROTECTED]
Subject: GROUPING


Hi,

I have to do a query but i don't know how to do this...

I have this table

id int
start time
stop time
...
There are 2 to 4 rows for each id

I have to select one row for each id where start is the max value between
all rows with this id

If you have an idea please let me know

Thanks


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: GROUPing question

[Ung, Seng]

Bob:
In case if you don't know this just want you to know...
Out of all the messages that I received, I only read your and saved it for the future.



thank you..
seng

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: GROUPing question

[Bob Hall]

>I have a GROUPing question on the following query:
>
>SELECT HOUR(closedtime) AS hour, COUNT(assignedto) AS count FROM db WHERE
>(closedtime >='2001-01-01' AND closedtime <= '2001-01-31 23:59:59') AND
>assignedto='person' GROUP BY hour;
>
>give something like this:
>
>+--++---+
>| hour | prettyhour | count |
>+--++---+
>|8 | 8AM|40 |
>|9 | 9AM|   161 |
>|   10 | 10AM   |   265 |
>|   11 | 11AM   |   177 |
>|   12 | 12PM   |   213 |
>|   13 | 1PM|93 |
>|   14 | 2PM|   119 |
>|   15 | 3PM|   105 |
>|   16 | 4PM|   129 |
>|   17 | 5PM|77 |
>|   18 | 6PM|42 |
>|   19 | 7PM| 5 |
>|   20 | 8PM| 9 |
>|   21 | 9PM| 6 |
>|   22 | 10PM   | 7 |
>|   23 | 11PM   | 2 |
>+--++---+
>16 rows in set (4.71 sec)
>
>Is there a way to get the AVG count per day over this range instead of the
>above total (in one query)?

Sir, it's not clear what you want here. To calculate the mean count 
per day, you would have to have counts for at least two days, but the 
range you're showing is only part of a day. I think what you want is 
the mean count per hour over this range. If that is so, you can try
SELECT Count(*)/Count(DISTINCT Hour(closedtime))

>I can't seem to find info on grouping by several things.

If you mean that you want to apply aggregate functions to two levels 
simultaneously, the answer is that SQL doesn't allow it. Sometimes 
you can trick SQL into doing it, but the result is a query that 
doesn't use indices and runs slowly. For example,
SELECT Sum(Hour(closedtime) = 8), Count(*)/Count(DISTINCT Hour(closedtime))
gives you the count during one hour in the first column and the mean 
hourly count in the second. (Assuming it works. I haven't tried it. 
You may have to use Sum(IF(Hour(closedtime) = 8, 1, 0))) This gives 
you statistics from two different levels, but the boolean evaluation 
in the first column will probably prevent the use of indices, as will 
the use of Hour() in the second.

Bob Hall

Bob Hall

Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php