Re: Help with an SQL query

[Gobi]

Rhino wrote:


I'm glad the explanation helped. I figured that the solution alone wouldn't
be very useful if it wasn't explained since it is not obvious to most people
how correlated queries work :-)

I really wasn't trying to center you out with my "rant" about MySQL version.
It's been a long-running frustration for me; I've tried to urge people to
give this information in their notes to the list but an awful lot of people
never think to mention their version. Then, an already lengthy reply gets
even longer as you try to explain: "If you have Version 3.x, the solution is
this If you have Version 4.x the solution is this. If you have
Version 5.x, the solution is this." You get the picture.

Okay, let me explain the temp tables approach now.

Most subqueries are not correlated so the subquery gets done once and then
its result gets plugged into the outer query. For example, given a table
called Employee in the Sample database which has one row for each employee
and each row contains an employee number, last name, and salary, this query
will return the employee number and last name of every employee who makes
more than the average salary for all employees in the table:

---
select empno, lastname
from Sample.Employee
where salary >
(select avg(salary)
from Sample.Employee);
---

If subqueries are supported, as in Version 4.1.x or above of MySQL, you just
run that exact query and you will get a list of all of the people who make
more than the average salary.

However, if you are using an older version of MySQL which doesn't support
subqueries, you need to use a temporary table approach. Basically, you
create a temporary table that contains the same results that the subquery
would have given you, then join it to the outer query. [Many, but not all,
subqueries can be rewritten as joins.] For example:
---
#Create the temporary table that will contain the average salary for all
employees.
create table if not exists temp_average_salary (average_salary
decimal(9,2));

#Populate the temporary table with the average salary for all employees.
insert into temp_average_salary(select avg(salary) from Sample.Employee);

#Do a join of the employee table and the temporary table to obtain only
employees who make
#more than the average salary.
select empno, lastname, salary
from Sample.Employee e inner join temp_average_salary t
where e.salary > t.average_salary;
---

This would give the same result as the original solution that used
subqueries although there is obviously more work to do in accomplishing the
desired result.

Initially, I was almost certain that this approach could not be made to work
for your problem due to its correlated nature but a couple of experiments
persuaded me that it was possible after all. It's actually quite similar to
the example I gave above, except that this time the temporary table is not
just a single value on a single row but a table containing one row for each
VBS_id, showing the max (latest) date for any row containing that VBS_id.
Here's the definition of the table and the correct method of populating the
table:

---
create table if not exists temp_table
(VBS_id int not null,
max_date date not null);

insert into temp_table (select VBS_id, max(date) from VBS_table group by
VBS_id);
---

If you display the contents of the temporary table, you get:

---
VBS_idmax_date
112005-10-08
122005-10-08

---
Now, you simply join the VBS_table and the temp_table, as follows:
---
select v.VBS_id, v.date, v.weight
from VBS_table v inner join temp_table t
on v.VBS_id = t.VBS_id
and v.date = t.max_date;
---

The result is exactly what you wanted:
---
VBS_iddateweight
112005-10-0811.52
122005-10-0810.50
---

You could apply this solution to versions of MySQL that don't have subquery
support; just remember that you need to re-create the temp_table immediately
before you do the join each time; otherwise, you are going to determine
whatever weights were in effect whenever you first populated the temp_table,
not the current weights.

In your case though, you can make life better for yourself. Since you have
view support, you can simply create a view that is the equivalent to the
temp_table and then join the view to the VBS_table to get the data you want.
Since the view is always up-to-date, you eliminate the need to have the
temp_table at all. Something like this:

---
create view VBS_max_dates
(VBS_id, max_date)
as select VBS_id, max(date)
from VBS_table
group by VBS_id;

select v.VBS_id, v.date, v.weight
from VBS_table v inner join VBS_max_dates z
on v.VBS_id = z.VBS_id
and v.date = z.max_date;
---

Note that the view is only created once, not each time you are about to do
the join!!

One other solution jumps to mind that should be just as good. Instead of
creating temp tables or views, why not just put the original correlated
subquery that I gave you into a stored procedure? Then, all you'd need to do
is call that stored procedure 

Re: Help with an SQL query

[Rhino]

ure? Then, all you'd need to do
is call that stored procedure every time you need that result.

Rhino


- Original Message - 
From: "Gobi" <[EMAIL PROTECTED]>
To: 
Sent: Saturday, November 05, 2005 8:05 AM
Subject: Re: Help with an SQL query


> Rhino wrote:
>
> >I can't test this in MySQL- I'm using an older version of MySQL that
doesn't
> >support subqueries - but it works in DB2 and it should do the trick for
> >getting the current weight of each VBS_id value:
> >
> >select VBS_id, date, weight
> >from VBS_table x
> >where date =
> >(select max(date) from VBS_table
> >where VBS_id = x.VBS_id);
> >
> >I'm assuming you are using a more version of MySQL which DOES support
> >subqueries! If not, you may be able to get the same result with temp
tables
> >but I haven't tried that. Make sure to say something if you don't know
how
> >to use temp tables to simulate subqueries. I'm dubious that this query
can
> >be simulated with temp tables though
> >
> >(Rant: I REALLY wish people (all people, not just you) posting questions
to
> >this mailing list would get in the habit of specifying which version of
> >MySQL they are using!! The answers to questions FREQUENTLY depend on the
> >MySQL version so it would really help reduce the size of answers if
people
> >volunteered this information in the original question.)
> >
> >Also, I'm assuming that that MySQL supports correlated subqueries; I'm
> >really not sure so I'll let you try the actual query and see if it works
for
> >you.
> >
> >Here's an explanation of how this query works, in case you've never seen
one
> >like this before.
> >
> >This is called a correlated subquery; the key thing that makes it obvious
> >that this is a correlated subquery (in case you have to recognize one) is
> >that a correlation name, in this case 'x', appears in the FROM clause of
the
> >outer query and again in the WHERE clause of the subquery. The subquery
> >appears in brackets in case you are not familiar with subqueries.
> >
> >A correlated subquery works backwards to a normal subquery. In a normal
> >subquery, the subquery is executed first and is only executed once: its
> >result is plugged into the outer query which then executes just once as
> >well. In a correlated subquery, both the outer query and the subquery are
> >executed _repeatedly_, possibly MANY times each.
> >
> >The outer query is executed in order to obtain one row, THEN the subquery
is
> >executed to see if the row found by the outer query can be kept. In this
> >case, let's say that the outer query returned the first row of the table,
> >which has a VBS_id of 11 and a date of '10/3/2005': the subquery
> >determines the maximum (most recent) date for any row that has same
VBS_id
> >as the one just found by the outer query; if the maximum date differs
from
> >the date found by the outer query, the outer query row is discarded and
does
> >not appear in the final result set. In this case, the maximum date for
> >VBS_ID is 10/8/2005 which is not equal to the value found by the outer
> >query, so that row is discarded.
> >
> >Having dealt with the first row of the outer query, the outer query
executes
> >again and gets another row. Again, the subquery is executed to see if the
> >date is the same as maximum date for that VBS_id and again, the outer row
is
> >only kept if its date matches the maximum date found by the subquery. And
so
> >it goes, one row at a time, until the outer query has read every row of
the
> >table; a single row of the outer query is obtained, then the subquery
> >determines if that row contains the maximum date for the VBS_id that was
> >just found by the outer query. The final result will contain only rows
that
> >have the maximum dates for each VBS_id and will show the VBS_id, the
maximum
> >date, and the weight at that date. That is the result you requested.
> >
> >Bear in mind that a correlated query can perform much more poorly than a
> >regular subquery, although the optimizer sometimes has tricks that make
the
> >performance quite tolerable.
> >
> >There may be a solution that doesn't involve a correlated subquery but
it's
> >not coming to me. However, others who are better at SQL may think of
> >something that does the job.
> >
> >Rhino
> >
>
> Wow, excellent explanation.  By the way, my apologies... I am using
> MySQL 5.0.15.  I would be interested in knowing how to use temp tables
> to simulate subqueries thoug

Re: Help with an SQL query

[Gobi]

Rhino wrote:


I can't test this in MySQL- I'm using an older version of MySQL that doesn't
support subqueries - but it works in DB2 and it should do the trick for
getting the current weight of each VBS_id value:

select VBS_id, date, weight
from VBS_table x
where date =
(select max(date) from VBS_table
where VBS_id = x.VBS_id);

I'm assuming you are using a more version of MySQL which DOES support
subqueries! If not, you may be able to get the same result with temp tables
but I haven't tried that. Make sure to say something if you don't know how
to use temp tables to simulate subqueries. I'm dubious that this query can
be simulated with temp tables though

(Rant: I REALLY wish people (all people, not just you) posting questions to
this mailing list would get in the habit of specifying which version of
MySQL they are using!! The answers to questions FREQUENTLY depend on the
MySQL version so it would really help reduce the size of answers if people
volunteered this information in the original question.)

Also, I'm assuming that that MySQL supports correlated subqueries; I'm
really not sure so I'll let you try the actual query and see if it works for
you.

Here's an explanation of how this query works, in case you've never seen one
like this before.

This is called a correlated subquery; the key thing that makes it obvious
that this is a correlated subquery (in case you have to recognize one) is
that a correlation name, in this case 'x', appears in the FROM clause of the
outer query and again in the WHERE clause of the subquery. The subquery
appears in brackets in case you are not familiar with subqueries.

A correlated subquery works backwards to a normal subquery. In a normal
subquery, the subquery is executed first and is only executed once: its
result is plugged into the outer query which then executes just once as
well. In a correlated subquery, both the outer query and the subquery are
executed _repeatedly_, possibly MANY times each.

The outer query is executed in order to obtain one row, THEN the subquery is
executed to see if the row found by the outer query can be kept. In this
case, let's say that the outer query returned the first row of the table,
which has a VBS_id of 11 and a date of '10/3/2005': the subquery
determines the maximum (most recent) date for any row that has same VBS_id
as the one just found by the outer query; if the maximum date differs from
the date found by the outer query, the outer query row is discarded and does
not appear in the final result set. In this case, the maximum date for
VBS_ID is 10/8/2005 which is not equal to the value found by the outer
query, so that row is discarded.

Having dealt with the first row of the outer query, the outer query executes
again and gets another row. Again, the subquery is executed to see if the
date is the same as maximum date for that VBS_id and again, the outer row is
only kept if its date matches the maximum date found by the subquery. And so
it goes, one row at a time, until the outer query has read every row of the
table; a single row of the outer query is obtained, then the subquery
determines if that row contains the maximum date for the VBS_id that was
just found by the outer query. The final result will contain only rows that
have the maximum dates for each VBS_id and will show the VBS_id, the maximum
date, and the weight at that date. That is the result you requested.

Bear in mind that a correlated query can perform much more poorly than a
regular subquery, although the optimizer sometimes has tricks that make the
performance quite tolerable.

There may be a solution that doesn't involve a correlated subquery but it's
not coming to me. However, others who are better at SQL may think of
something that does the job.

Rhino



Wow, excellent explanation.  By the way, my apologies... I am using 
MySQL 5.0.15.  I would be interested in knowing how to use temp tables 
to simulate subqueries though.  I found out that the sql statement, 
although it returns the correct result, does not work in a view because 
MySQL Views does not allow subqueries.


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

Fw: Help with an SQL query

[Rhino]

Oops, I meant to post this on the list AND copy the person asking the
question

Rhino

- Original Message - 
From: "Rhino" <[EMAIL PROTECTED]>
To: "Gobi" <[EMAIL PROTECTED]>
Sent: Friday, November 04, 2005 1:46 PM
Subject: Re: Help with an SQL query


> I can't test this in MySQL- I'm using an older version of MySQL that
doesn't
> support subqueries - but it works in DB2 and it should do the trick for
> getting the current weight of each VBS_id value:
>
> select VBS_id, date, weight
> from VBS_table x
> where date =
> (select max(date) from VBS_table
> where VBS_id = x.VBS_id);
>
> I'm assuming you are using a more version of MySQL which DOES support
> subqueries! If not, you may be able to get the same result with temp
tables
> but I haven't tried that. Make sure to say something if you don't know how
> to use temp tables to simulate subqueries. I'm dubious that this query can
> be simulated with temp tables though
>
> (Rant: I REALLY wish people (all people, not just you) posting questions
to
> this mailing list would get in the habit of specifying which version of
> MySQL they are using!! The answers to questions FREQUENTLY depend on the
> MySQL version so it would really help reduce the size of answers if people
> volunteered this information in the original question.)
>
> Also, I'm assuming that that MySQL supports correlated subqueries; I'm
> really not sure so I'll let you try the actual query and see if it works
for
> you.
>
> Here's an explanation of how this query works, in case you've never seen
one
> like this before.
>
> This is called a correlated subquery; the key thing that makes it obvious
> that this is a correlated subquery (in case you have to recognize one) is
> that a correlation name, in this case 'x', appears in the FROM clause of
the
> outer query and again in the WHERE clause of the subquery. The subquery
> appears in brackets in case you are not familiar with subqueries.
>
> A correlated subquery works backwards to a normal subquery. In a normal
> subquery, the subquery is executed first and is only executed once: its
> result is plugged into the outer query which then executes just once as
> well. In a correlated subquery, both the outer query and the subquery are
> executed _repeatedly_, possibly MANY times each.
>
> The outer query is executed in order to obtain one row, THEN the subquery
is
> executed to see if the row found by the outer query can be kept. In this
> case, let's say that the outer query returned the first row of the table,
> which has a VBS_id of 11 and a date of '10/3/2005': the subquery
> determines the maximum (most recent) date for any row that has same VBS_id
> as the one just found by the outer query; if the maximum date differs from
> the date found by the outer query, the outer query row is discarded and
does
> not appear in the final result set. In this case, the maximum date for
> VBS_ID is 10/8/2005 which is not equal to the value found by the outer
> query, so that row is discarded.
>
> Having dealt with the first row of the outer query, the outer query
executes
> again and gets another row. Again, the subquery is executed to see if the
> date is the same as maximum date for that VBS_id and again, the outer row
is
> only kept if its date matches the maximum date found by the subquery. And
so
> it goes, one row at a time, until the outer query has read every row of
the
> table; a single row of the outer query is obtained, then the subquery
> determines if that row contains the maximum date for the VBS_id that was
> just found by the outer query. The final result will contain only rows
that
> have the maximum dates for each VBS_id and will show the VBS_id, the
maximum
> date, and the weight at that date. That is the result you requested.
>
> Bear in mind that a correlated query can perform much more poorly than a
> regular subquery, although the optimizer sometimes has tricks that make
the
> performance quite tolerable.
>
> There may be a solution that doesn't involve a correlated subquery but
it's
> not coming to me. However, others who are better at SQL may think of
> something that does the job.
>
> Rhino
>
>
>
> - Original Message - 
> From: "Gobi" <[EMAIL PROTECTED]>
> To: 
> Sent: Friday, November 04, 2005 3:05 AM
> Subject: Help with an SQL query
>
>
> > Not sure if this is the right place to ask.  I have a table, Weight,
> > with the following test data:
> >
> > idx  VBS_ID   DateWeight
> > 11110/3/200511.5
> > 2119/5/2004  10
> > 3111

Re: Help with an SQL query

[Gobi]

Figured out the query:

select idx, vbs_id, date, weight from Weight,
(select vbs_id as maxid, max(date) as maxdate from Weight group by 
vbs_id) as t

where vbs_id = maxid and date = maxdate;

It returns the proper weight and idx.

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

Re: Help with an SQL query

[Johan Höök]

Hi Gobi,
the problem with your original query is that there is no
guarantee that your max(date) and it's associated VBS_ID
is picked, so what you have to ensure is that they get picked
together, so I think your statement should be like this:
SELECT VBS_ID,
SUBSTRING( MAX( CONCAT( LPAD( Date,10,'0'),weight, 11 ) AS D
   LEFT( MAX( CONCAT( LPAD(Date,10,'0'),weight)), 10 ) AS W
GROUP BY VBS_ID;
What happens here is that you're not doing max on date, you're
doing it on the combination of date and weight. What might be a
problem though is your dateformat, you might run into problems
with that I guess. Also you might have to play with lengths a bit.

/Johan

Gobi wrote:

Johan Höök wrote:


Hi Gobi,
there was a similar posting in august.
See:
http://lists.mysql.com/mysql/187436
which I think describes what you want.
I'll include a bit of it here as well
--

This is out of the MySQL class and is called the Max-Concat trick.


What is the biggest country in each continent?
"The Rows Holding the Group-wise Maximum of a Certain Field"

MySQL> Select Continent,
->  SUBSTRING(MAX(CONCAT(LPAD(Population,10,'0'),Name)),11) AS
Country,
-> 0+LEFT(MAX(CONCAT(LPAD(Population,10,'0'),Name)),10) AS
Population
-> From Country
-> Group By Continent
-> Order By Population DESC;
-

/Johan 




Sorry for being slow but this statement is quite confusing and I am 
having difficulty trying to understand it in order to adapt it to my 
table.  Can you explain it?  Thanks.






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

Re: Help with an SQL query

[Gobi]

Johan Höök wrote:


Hi Gobi,
there was a similar posting in august.
See:
http://lists.mysql.com/mysql/187436
which I think describes what you want.
I'll include a bit of it here as well
--

This is out of the MySQL class and is called the Max-Concat trick.


What is the biggest country in each continent?
"The Rows Holding the Group-wise Maximum of a Certain Field"

MySQL> Select Continent,
->  SUBSTRING(MAX(CONCAT(LPAD(Population,10,'0'),Name)),11) AS
Country,
-> 0+LEFT(MAX(CONCAT(LPAD(Population,10,'0'),Name)),10) AS
Population
-> From Country
-> Group By Continent
-> Order By Population DESC;
-

/Johan



Actually, taking a closer look at your link, I think my query is 
slightly different from  Kemin's.  Kemin was trying to get the top 
numbers from col2 AND col3 together.  Therefore, in his original 
statement, he was trying to order both col2 and col3 in descending order.


Me, I just want to get the most recent date for each unique VBS_ID.  I 
don't need to get the max weight so I am not sure if it is necessary to 
concat my date and weight together.  Comments?


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

Re: Help with an SQL query

[Gobi]

Johan Höök wrote:


Hi Gobi,
there was a similar posting in august.
See:
http://lists.mysql.com/mysql/187436
which I think describes what you want.
I'll include a bit of it here as well
--

This is out of the MySQL class and is called the Max-Concat trick.


What is the biggest country in each continent?
"The Rows Holding the Group-wise Maximum of a Certain Field"

MySQL> Select Continent,
->  SUBSTRING(MAX(CONCAT(LPAD(Population,10,'0'),Name)),11) AS
Country,
-> 0+LEFT(MAX(CONCAT(LPAD(Population,10,'0'),Name)),10) AS
Population
-> From Country
-> Group By Continent
-> Order By Population DESC;
-

/Johan 



Sorry for being slow but this statement is quite confusing and I am 
having difficulty trying to understand it in order to adapt it to my 
table.  Can you explain it?  Thanks.


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

Re: Help with an SQL query

[Johan Höök]

Hi Gobi,
there was a similar posting in august.
See:
http://lists.mysql.com/mysql/187436
which I think describes what you want.
I'll include a bit of it here as well
--

This is out of the MySQL class and is called the Max-Concat trick.


What is the biggest country in each continent?
"The Rows Holding the Group-wise Maximum of a Certain Field"

MySQL> Select Continent,
->  SUBSTRING(MAX(CONCAT(LPAD(Population,10,'0'),Name)),11) AS
Country,
-> 0+LEFT(MAX(CONCAT(LPAD(Population,10,'0'),Name)),10) AS
Population
-> From Country
-> Group By Continent
-> Order By Population DESC;
-

/Johan

Gobi wrote:

Gobi wrote:


Arno Coetzee wrote:


Gobi wrote:

Not sure if this is the right place to ask.  I have a table, Weight, 
with the following test data:


idx  VBS_ID   DateWeight
11110/3/200511.5
2119/5/2004  10
31110/7/200511.51
41110/8/200511.52
51210/8/200510.5
61210/1/200510.3
7129/28/200510

What I would like to do is to get the most recent weight for each 
unique VBS_ID.  So, in this case, I want a result of:


11, 10/8/2005, 11.52
12, 10/8/2005, 10.5

Can anyone tell me how to do this?  Thanks.


select VBS_ID , max(Date)
from weight

Actually, I tried that.  For one thing, using max() also requires a 
GROUP BY clause.  Otherwise, mysql complains.  So I am forced to run 
the following statement:


Select VBS_ID, max(Date) from Weight Group By VBS_ID

And it returns:

VBS_IDmax(Date)weight
1110/8/200511.5
1210/8/200510.5

At first, I thought I had it right but when I look more closely, the 
weights are incorrect.  The weight for 11 on 10/8/2005 should be 
11.52.  It looks look it is just returning the weight for 10/3/2005.



As a further note on my above post, I added "idx" to my query:

Select idx, VBS_ID, max(Date) from Weight Group By VBS_ID

and it returned:

idxVBS_IDmax(Date)weight
1  1110/8/200511.5
5  1210/8/200510.5

So actually, it is returning the wrong rows.





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

Re: Help with an SQL query

[Gobi]

Gobi wrote:


Arno Coetzee wrote:


Gobi wrote:

Not sure if this is the right place to ask.  I have a table, Weight, 
with the following test data:


idx  VBS_ID   DateWeight
11110/3/200511.5
2119/5/2004  10
31110/7/200511.51
41110/8/200511.52
51210/8/200510.5
61210/1/200510.3
7129/28/200510

What I would like to do is to get the most recent weight for each 
unique VBS_ID.  So, in this case, I want a result of:


11, 10/8/2005, 11.52
12, 10/8/2005, 10.5

Can anyone tell me how to do this?  Thanks.


select VBS_ID , max(Date)
from weight

Actually, I tried that.  For one thing, using max() also requires a 
GROUP BY clause.  Otherwise, mysql complains.  So I am forced to run 
the following statement:


Select VBS_ID, max(Date) from Weight Group By VBS_ID

And it returns:

VBS_IDmax(Date)weight
1110/8/200511.5
1210/8/200510.5

At first, I thought I had it right but when I look more closely, the 
weights are incorrect.  The weight for 11 on 10/8/2005 should be 
11.52.  It looks look it is just returning the weight for 10/3/2005.



As a further note on my above post, I added "idx" to my query:

Select idx, VBS_ID, max(Date) from Weight Group By VBS_ID

and it returned:

idxVBS_IDmax(Date)weight
1  1110/8/200511.5
5  1210/8/200510.5

So actually, it is returning the wrong rows.


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

Re: Help with an SQL query

[Gobi]

Arno Coetzee wrote:


Gobi wrote:

Not sure if this is the right place to ask.  I have a table, Weight, 
with the following test data:


idx  VBS_ID   DateWeight
11110/3/200511.5
2119/5/2004  10
31110/7/200511.51
41110/8/200511.52
51210/8/200510.5
61210/1/200510.3
7129/28/200510

What I would like to do is to get the most recent weight for each 
unique VBS_ID.  So, in this case, I want a result of:


11, 10/8/2005, 11.52
12, 10/8/2005, 10.5

Can anyone tell me how to do this?  Thanks.


select VBS_ID , max(Date)
from weight

Actually, I tried that.  For one thing, using max() also requires a 
GROUP BY clause.  Otherwise, mysql complains.  So I am forced to run the 
following statement:


Select VBS_ID, max(Date) from Weight Group By VBS_ID

And it returns:

VBS_IDmax(Date)weight
1110/8/200511.5
1210/8/200510.5

At first, I thought I had it right but when I look more closely, the 
weights are incorrect.  The weight for 11 on 10/8/2005 should be 
11.52.  It looks look it is just returning the weight for 10/3/2005.


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

Re: Help with an SQL query

[Arno Coetzee]

Gobi wrote:

Not sure if this is the right place to ask.  I have a table, Weight, 
with the following test data:


idx  VBS_ID   DateWeight
11110/3/200511.5
2119/5/2004  10
31110/7/200511.51
41110/8/200511.52
51210/8/200510.5
61210/1/200510.3
7129/28/200510

What I would like to do is to get the most recent weight for each 
unique VBS_ID.  So, in this case, I want a result of:


11, 10/8/2005, 11.52
12, 10/8/2005, 10.5

Can anyone tell me how to do this?  Thanks.


select VBS_ID , max(Date)
from weight

--
Arno Coetzee
Developer
Flash Media Group
Office : +27 12 342 3490
Mobile : +27 82 693 6180
Fax : + 27 12 430 4269
www.flashmedia.co.za

>


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

Help with an SQL query

[Gobi]

Not sure if this is the right place to ask.  I have a table, Weight, 
with the following test data:


idx  VBS_ID   DateWeight
11110/3/200511.5
2119/5/2004  10
31110/7/200511.51
41110/8/200511.52
51210/8/200510.5
61210/1/200510.3
7129/28/200510

What I would like to do is to get the most recent weight for each unique 
VBS_ID.  So, in this case, I want a result of:


11, 10/8/2005, 11.52
12, 10/8/2005, 10.5

Can anyone tell me how to do this?  Thanks.

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

Re: help with an SQL query

[Ian Sales (DBA)]

Michael Satterwhite wrote:
-
As it is, all I can suggest is to JOIN on all 90 tables - and hope MySQL
can handle the query - and that you can type all of them without error.
Note that if you use a UNION query as you suggest above, you will get
the last login FOR EACH DAY - not the overall last loging.
- MySQL will only allow you to join 32 tables in a single SQL statement.
- ian
--
+---+
| Ian Sales  Database Administrator |
|   |
|  "All your database are belong to us" |
| ebuyer  http://www.ebuyer.com |
+---+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: help with an SQL query

[Harald Fuchs]

In article <[EMAIL PROTECTED]>,
"J S" <[EMAIL PROTECTED]> writes:

> and I need to check the last date each user logged on to the proxy in
> the last 3 months.

> In my database, there is a table for the users:

mysql> desc user_table;
> +---+--+--+-+-++
> | Field | Type | Null | Key | Default | Extra  |
> +---+--+--+-+-++
> | ID| int(10) unsigned |  | MUL | NULL| auto_increment |
> | uid   | varchar(10)  |  | PRI | ||
> +---+--+--+-+-++
> 2 rows in set (0.00 sec)

> and a table (tYYMMDD) for each days log:

mysql> desc t20041209;
> +--+--+--+-+-+---+
> | Field| Type | Null | Key | Default | Extra |
> +--+--+--+-+-+---+
> | uid  | int(10) unsigned |  | MUL | 0   |   |
> | time | timestamp(14)| YES  | | NULL|   |
> | ip   | int(10) unsigned |  | MUL | 0   |   |
> | urlid| int(10) unsigned |  | MUL | 0   |   |
> | timetaken| smallint(5) unsigned | YES  | | 0   |   |
> | cs_size  | int(10) unsigned | YES  | MUL | 0   |   |
> | sc_size  | int(10) unsigned | YES  | MUL | 0   |   |
> | method_ID| tinyint(3) unsigned  |  | | 0   |   |
> | action_ID| tinyint(3) unsigned  |  | | 0   |   |
> | virus_ID | tinyint(3) unsigned  |  | | 0   |   |
> | useragent_ID | smallint(5) unsigned |  | MUL | 0   |   |
> +--+--+--+-+-+---+
> 11 rows in set (0.00 sec)

> The time column here gives the actual time the user logged on, but I
> would be happy just to know the date (which I could get from the table
> name if the user's uid was present in there).

> Could anyone help me to write an SQL query to do this please?

Create a MERGE table over all the logs for at least the last three
months, and then proceed as described in the manual under
"The Rows Holding the Group-wise Maximum of a Certain Field".


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

Re: help with an SQL query

[Michael Satterwhite]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
J S wrote:
|
|
|> J S wrote:
|> | Hi,
|> |
|> | I need a bit of help with a mySQL query. I have a list of users in a
|> | text file called 'users':
|> |
|> | u655354
|> | u687994
|> | u696974
|> | u728141
|> | ..
|> | ..
|> |
|> | and I need to check the last date each user logged on to the proxy in
|> | the last 3 months.
|> |
|> | In my database, there is a table for the users:
|> |
|> | mysql> desc user_table;
|> | +---+--+--+-+-++
|> | | Field | Type | Null | Key | Default | Extra  |
|> | +---+--+--+-+-++
|> | | ID| int(10) unsigned |  | MUL | NULL| auto_increment |
|> | | uid   | varchar(10)  |  | PRI | ||
|> | +---+--+--+-+-++
|> | 2 rows in set (0.00 sec)
|> |
|> | and a table (tYYMMDD) for each days log:
|> |
|> | mysql> desc t20041209;
|> | +--+--+--+-+-+---+
|> | | Field| Type | Null | Key | Default | Extra |
|> | +--+--+--+-+-+---+
|> | | uid  | int(10) unsigned |  | MUL | 0   |   |
|> | | time | timestamp(14)| YES  | | NULL|   |
|> | | ip   | int(10) unsigned |  | MUL | 0   |   |
|> | | urlid| int(10) unsigned |  | MUL | 0   |   |
|> | | timetaken| smallint(5) unsigned | YES  | | 0   |   |
|> | | cs_size  | int(10) unsigned | YES  | MUL | 0   |   |
|> | | sc_size  | int(10) unsigned | YES  | MUL | 0   |   |
|> | | method_ID| tinyint(3) unsigned  |  | | 0   |   |
|> | | action_ID| tinyint(3) unsigned  |  | | 0   |   |
|> | | virus_ID | tinyint(3) unsigned  |  | | 0   |   |
|> | | useragent_ID | smallint(5) unsigned |  | MUL | 0   |   |
|> | +--+--+--+-+-+---+
|> | 11 rows in set (0.00 sec)
|> |
|> | The time column here gives the actual time the user logged on, but I
|> | would be happy just to know the date (which I could get from the table
|> | name if the user's uid was present in there).
|> |
|> | Could anyone help me to write an SQL query to do this please?
|> |
|> | Many thanks. Please let me know if you need anymore information.
|>
|> Select u.uid, max(l.time) as lastLog
|> ~   From user_table u join t20041209 l on u.uid = l.uid
|> ~   Group by uid;
|>
|
| Thanks Mike. I need to run this query over 3 months though. Is there a
| quick way to write:
|
| t20041101 union t20041102 union t20041103 union ... t20050125 union
| t20050126
Not that I know of. Why are you using a different table per day - as
opposed to a single log table with a field containing the day? I'm sure
there's a reason for the current structure, but having a logday field in
a single log table would make this kind of query much easier. A simple
BETWEEN clause would suffice if it was in a single table.
As it is, all I can suggest is to JOIN on all 90 tables - and hope MySQL
can handle the query - and that you can type all of them without error.
Note that if you use a UNION query as you suggest above, you will get
the last login FOR EACH DAY - not the overall last loging.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.0 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFCGhfOjeziQOokQnARAvSUAJ4zyHmYa95o+0eZ2zs//S24n0kyqQCeO6M2
UzELKfj6hZ14bp+NLLj+McQ=
=NZBB
-END PGP SIGNATURE-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: help with an SQL query

[J S]

J S wrote:
| Hi,
|
| I need a bit of help with a mySQL query. I have a list of users in a
| text file called 'users':
|
| u655354
| u687994
| u696974
| u728141
| ..
| ..
|
| and I need to check the last date each user logged on to the proxy in
| the last 3 months.
|
| In my database, there is a table for the users:
|
| mysql> desc user_table;
| +---+--+--+-+-++
| | Field | Type | Null | Key | Default | Extra  |
| +---+--+--+-+-++
| | ID| int(10) unsigned |  | MUL | NULL| auto_increment |
| | uid   | varchar(10)  |  | PRI | ||
| +---+--+--+-+-++
| 2 rows in set (0.00 sec)
|
| and a table (tYYMMDD) for each days log:
|
| mysql> desc t20041209;
| +--+--+--+-+-+---+
| | Field| Type | Null | Key | Default | Extra |
| +--+--+--+-+-+---+
| | uid  | int(10) unsigned |  | MUL | 0   |   |
| | time | timestamp(14)| YES  | | NULL|   |
| | ip   | int(10) unsigned |  | MUL | 0   |   |
| | urlid| int(10) unsigned |  | MUL | 0   |   |
| | timetaken| smallint(5) unsigned | YES  | | 0   |   |
| | cs_size  | int(10) unsigned | YES  | MUL | 0   |   |
| | sc_size  | int(10) unsigned | YES  | MUL | 0   |   |
| | method_ID| tinyint(3) unsigned  |  | | 0   |   |
| | action_ID| tinyint(3) unsigned  |  | | 0   |   |
| | virus_ID | tinyint(3) unsigned  |  | | 0   |   |
| | useragent_ID | smallint(5) unsigned |  | MUL | 0   |   |
| +--+--+--+-+-+---+
| 11 rows in set (0.00 sec)
|
| The time column here gives the actual time the user logged on, but I
| would be happy just to know the date (which I could get from the table
| name if the user's uid was present in there).
|
| Could anyone help me to write an SQL query to do this please?
|
| Many thanks. Please let me know if you need anymore information.
Select u.uid, max(l.time) as lastLog
~   From user_table u join t20041209 l on u.uid = l.uid
~   Group by uid;
Thanks Mike. I need to run this query over 3 months though. Is there a quick 
way to write:

t20041101 union t20041102 union t20041103 union ... t20050125 union 
t20050126

?

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

help with an SQL query

[J S]

Hi,
I need a bit of help with a mySQL query. I have a list of users in a text 
file called 'users':

u655354
u687994
u696974
u728141
..
..
and I need to check the last date each user logged on to the proxy in the 
last 3 months.

In my database, there is a table for the users:
mysql> desc user_table;
+---+--+--+-+-++
| Field | Type | Null | Key | Default | Extra  |
+---+--+--+-+-++
| ID| int(10) unsigned |  | MUL | NULL| auto_increment |
| uid   | varchar(10)  |  | PRI | ||
+---+--+--+-+-++
2 rows in set (0.00 sec)
and a table (tYYMMDD) for each days log:
mysql> desc t20041209;
+--+--+--+-+-+---+
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-+---+
| uid  | int(10) unsigned |  | MUL | 0   |   |
| time | timestamp(14)| YES  | | NULL|   |
| ip   | int(10) unsigned |  | MUL | 0   |   |
| urlid| int(10) unsigned |  | MUL | 0   |   |
| timetaken| smallint(5) unsigned | YES  | | 0   |   |
| cs_size  | int(10) unsigned | YES  | MUL | 0   |   |
| sc_size  | int(10) unsigned | YES  | MUL | 0   |   |
| method_ID| tinyint(3) unsigned  |  | | 0   |   |
| action_ID| tinyint(3) unsigned  |  | | 0   |   |
| virus_ID | tinyint(3) unsigned  |  | | 0   |   |
| useragent_ID | smallint(5) unsigned |  | MUL | 0   |   |
+--+--+--+-+-+---+
11 rows in set (0.00 sec)
The time column here gives the actual time the user logged on, but I would 
be happy just to know the date (which I could get from the table name if the 
user's uid was present in there).

Could anyone help me to write an SQL query to do this please?
Many thanks. Please let me know if you need anymore information.
JS.

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