I'm new to MySQL and I am having a problem selecting the highest
valued date/time for a particular day. Here is the table structure:
+--------------+-----------------+-----+----+--------+--------------+
| Field | Type | Null| Key| Default|Extra |
+--------------+-----------------+-----+----+--------+--------------+
| id | int(10) unsigned| | PRI| NULL |auto_increment|
| timestamp | datetime | YES | | NULL | |
| ccrnumber | varchar(2) | YES | | NULL | |
| c00_low_value| int(10) unsigned| YES | | NULL | |
+--------------+-----------------+-----+----+--------+--------------+
I need to perform a cross-tab query which will create column headings
for 'ccrnumber' and hold the value of 'c00_low_value' and filter for
rows that occured on a particular day(in this case '11 23 2003'). So I
created the follwing query:
SELECT date_format(timestamp,'%m %d %Y %T')AS timestamp,
sum(IF(ccrnumber = '1',c00_low_value,Null)) AS '1',
sum(IF(ccrnumber = '2',c00_low_value,Null)) AS '2',
sum(IF(ccrnumber = '3',c00_low_value,Null)) AS '3',
sum(IF(ccrnumber = '4',c00_low_value,Null)) AS '4',
sum(IF(ccrnumber = '5',c00_low_value,Null)) AS '5',
sum(IF(ccrnumber = '6',c00_low_value,Null)) AS '6',
sum(IF(ccrnumber = '7',c00_low_value,Null)) AS '7',
sum(IF(ccrnumber = '8',c00_low_value,Null)) AS '8',
sum(IF(ccrnumber = '9',c00_low_value,Null)) AS '9'
FROM vault2 WHERE date_format(timestamp,'%m %d %Y')='11 23 2003'
GROUP BY date_format(timestamp,'%m %d %Y %T')
ORDER BY date_format(timestamp,'%m %d %Y %T');
Result:
+-------------------+---+---+---+---+---+---+---+---+---+
|timestamp | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
+-------------------+---+---+---+---+---+---+---+---+---+
|11 23 2003 14:54:32| 3 | 2 | 5 | 1 | 2 | 2 | 8 | 2 | 1 |
|11 23 2003 14:55:32| 4 | 3 | 5 | 7 | 8 | 4 | 5 | 6 | 4 |
+-------------------+---+---+---+---+---+---+---+---+---+
The problem I am having is getting the last entry for a particular day
(based on time). My result set is showing two dates for '11 23 2003'.
I only want to show the one that has the highest value in 'timestamp'
of the two rows that are shown above (in this case '11 23 2003
14:55:32').
Any help would be greatly appreciated. :)
2  4688  za****@sympatico.ca (zaceti) wrote in message news:<6e**************************@posting.google. com>... I'm new to MySQL and I am having a problem selecting the highest
valued date/time for a particular day. Here is the table structure:
+--------------+-----------------+-----+----+--------+--------------+
| Field | Type | Null| Key| Default|Extra |
+--------------+-----------------+-----+----+--------+--------------+
| id | int(10) unsigned| | PRI| NULL |auto_increment|
| timestamp | datetime | YES | | NULL | |
| ccrnumber | varchar(2) | YES | | NULL | |
| c00_low_value| int(10) unsigned| YES | | NULL | |
+--------------+-----------------+-----+----+--------+--------------+
I need to perform a cross-tab query which will create column headings
for 'ccrnumber' and hold the value of 'c00_low_value' and filter for
rows that occured on a particular day(in this case '11 23 2003'). So I
created the follwing query:
SELECT date_format(timestamp,'%m %d %Y %T')AS timestamp,
sum(IF(ccrnumber = '1',c00_low_value,Null)) AS '1',
sum(IF(ccrnumber = '2',c00_low_value,Null)) AS '2',
sum(IF(ccrnumber = '3',c00_low_value,Null)) AS '3',
sum(IF(ccrnumber = '4',c00_low_value,Null)) AS '4',
sum(IF(ccrnumber = '5',c00_low_value,Null)) AS '5',
sum(IF(ccrnumber = '6',c00_low_value,Null)) AS '6',
sum(IF(ccrnumber = '7',c00_low_value,Null)) AS '7',
sum(IF(ccrnumber = '8',c00_low_value,Null)) AS '8',
sum(IF(ccrnumber = '9',c00_low_value,Null)) AS '9'
FROM vault2 WHERE date_format(timestamp,'%m %d %Y')='11 23 2003'
GROUP BY date_format(timestamp,'%m %d %Y %T')
ORDER BY date_format(timestamp,'%m %d %Y %T');
Result:
+-------------------+---+---+---+---+---+---+---+---+---+
|timestamp | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
+-------------------+---+---+---+---+---+---+---+---+---+
|11 23 2003 14:54:32| 3 | 2 | 5 | 1 | 2 | 2 | 8 | 2 | 1 |
|11 23 2003 14:55:32| 4 | 3 | 5 | 7 | 8 | 4 | 5 | 6 | 4 |
+-------------------+---+---+---+---+---+---+---+---+---+
The problem I am having is getting the last entry for a particular day
(based on time). My result set is showing two dates for '11 23 2003'.
I only want to show the one that has the highest value in 'timestamp'
of the two rows that are shown above (in this case '11 23 2003
14:55:32').
Any help would be greatly appreciated. :)
Solution For above:
SELECT
date_format(timestamp,'%m %d %Y %T') AS timestamp,
sum(IF(ccrnumber = '1',c00_low_value,Null)) AS '1',
sum(IF(ccrnumber = '2',c00_low_value,Null)) AS '2',
sum(IF(ccrnumber = '3',c00_low_value,Null)) AS '3',
sum(IF(ccrnumber = '4',c00_low_value,Null)) AS '4',
sum(IF(ccrnumber = '5',c00_low_value,Null)) AS '5',
sum(IF(ccrnumber = '6',c00_low_value,Null)) AS '6',
sum(IF(ccrnumber = '7',c00_low_value,Null)) AS '7',
sum(IF(ccrnumber = '8',c00_low_value,Null)) AS '8',
sum(IF(ccrnumber = '9',c00_low_value,Null)) AS '9'
FROM vault2 where date_format(timestamp,'%m %d %Y')='11 23 2003'
GROUP BY date_format(timestamp,'%m %d %Y %T')
ORDER BY date_format(timestamp,'%m %d %Y %T') DESC LIMIT 1;
Returns:
+--------------------+---+---+---+---+---+---+---+---+---+
|timestamp | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
+--------------------+---+---+---+---+---+---+---+---+---+
|11 23 2003 14:55:32 | 4 | 3 | 5 | 7 | 8 | 4 | 5 | 6 | 4 |
+--------------------+---+---+---+---+---+---+---+---+---+
I am now faced with a new problem. How can I select the greatest
date/time value for each date in a range?
I have tried the following:
SELECT
Max(date_format(timestamp,'%m %d %Y %T')) AS timestamp,
sum(IF(ccrnumber = '1',c00_low_value,Null)) AS '1',
sum(IF(ccrnumber = '2',c00_low_value,Null)) AS '2',
sum(IF(ccrnumber = '3',c00_low_value,Null)) AS '3',
sum(IF(ccrnumber = '4',c00_low_value,Null)) AS '4',
sum(IF(ccrnumber = '5',c00_low_value,Null)) AS '5',
sum(IF(ccrnumber = '6',c00_low_value,Null)) AS '6',
sum(IF(ccrnumber = '7',c00_low_value,Null)) AS '7',
sum(IF(ccrnumber = '8',c00_low_value,Null)) AS '8',
sum(IF(ccrnumber = '9',c00_low_value,Null)) AS '9'
FROM vault2 where date_format(timestamp,'%m %d %Y')>='11 23 2003' AND
date_format(timestamp,'%m %d %Y')<='11 27 2003'
GROUP BY date_format(timestamp,'%m %d %Y %T')
ORDER BY date_format(timestamp,'%m %d %Y %T');
The above returns the following results:
+--------------------+---+---+---+---+---+---+---+---+---+
|timestamp | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
+--------------------+---+---+---+---+---+---+---+---+---+
|11 23 2003 14:54:32 | 3 | 2 | 5 | 1 | 2 | 2 | 8 | 2 | 1 |
|11 23 2003 14:55:32 | 4 | 3 | 5 | 7 | 8 | 4 | 5 | 6 | 4 |***
|11 25 2003 14:54:35 | 1 | 2 | 6 | 2 | 2 | 3 | 2 | 2 | 3 |***
|11 27 2003 14:54:31 | 3 | 2 | 2 | 7 | 2 | 8 | 2 | 2 | 3 |
|11 27 2003 14:54:33 | 4 | 2 | 5 | 4 | 2 | 6 | 5 | 2 | 2 |***
+--------------------+---+---+---+---+---+---+---+---+---+
This is not what I desire. I wish to return the rows marked with '***'
above which are the greatest date/time for a given day and just those
rows.
If anyone can please help me it would be greatly appreciated.
Thanks in advance. :) za****@sympatico.ca (zaceti) wrote in message news:<6e**************************@posting.google. com>... I'm new to MySQL and I am having a problem selecting the highest
valued date/time for a particular day. Here is the table structure:
+--------------+-----------------+-----+----+--------+--------------+
| Field | Type | Null| Key| Default|Extra |
+--------------+-----------------+-----+----+--------+--------------+
| id | int(10) unsigned| | PRI| NULL |auto_increment|
| timestamp | datetime | YES | | NULL | |
| ccrnumber | varchar(2) | YES | | NULL | |
| c00_low_value| int(10) unsigned| YES | | NULL | |
+--------------+-----------------+-----+----+--------+--------------+
I need to perform a cross-tab query which will create column headings
for 'ccrnumber' and hold the value of 'c00_low_value' and filter for
rows that occured on a particular day(in this case '11 23 2003'). So I
created the follwing query:
SELECT date_format(timestamp,'%m %d %Y %T')AS timestamp,
sum(IF(ccrnumber = '1',c00_low_value,Null)) AS '1',
sum(IF(ccrnumber = '2',c00_low_value,Null)) AS '2',
sum(IF(ccrnumber = '3',c00_low_value,Null)) AS '3',
sum(IF(ccrnumber = '4',c00_low_value,Null)) AS '4',
sum(IF(ccrnumber = '5',c00_low_value,Null)) AS '5',
sum(IF(ccrnumber = '6',c00_low_value,Null)) AS '6',
sum(IF(ccrnumber = '7',c00_low_value,Null)) AS '7',
sum(IF(ccrnumber = '8',c00_low_value,Null)) AS '8',
sum(IF(ccrnumber = '9',c00_low_value,Null)) AS '9'
FROM vault2 WHERE date_format(timestamp,'%m %d %Y')='11 23 2003'
GROUP BY date_format(timestamp,'%m %d %Y %T')
ORDER BY date_format(timestamp,'%m %d %Y %T');
Result:
+-------------------+---+---+---+---+---+---+---+---+---+
|timestamp | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
+-------------------+---+---+---+---+---+---+---+---+---+
|11 23 2003 14:54:32| 3 | 2 | 5 | 1 | 2 | 2 | 8 | 2 | 1 |
|11 23 2003 14:55:32| 4 | 3 | 5 | 7 | 8 | 4 | 5 | 6 | 4 |
+-------------------+---+---+---+---+---+---+---+---+---+
The problem I am having is getting the last entry for a particular day
(based on time). My result set is showing two dates for '11 23 2003'.
I only want to show the one that has the highest value in 'timestamp'
of the two rows that are shown above (in this case '11 23 2003
14:55:32').
Any help would be greatly appreciated. :)
Solution For above:
SELECT
date_format(timestamp,'%m %d %Y %T') AS timestamp,
sum(IF(ccrnumber = '1',c00_low_value,Null)) AS '1',
sum(IF(ccrnumber = '2',c00_low_value,Null)) AS '2',
sum(IF(ccrnumber = '3',c00_low_value,Null)) AS '3',
sum(IF(ccrnumber = '4',c00_low_value,Null)) AS '4',
sum(IF(ccrnumber = '5',c00_low_value,Null)) AS '5',
sum(IF(ccrnumber = '6',c00_low_value,Null)) AS '6',
sum(IF(ccrnumber = '7',c00_low_value,Null)) AS '7',
sum(IF(ccrnumber = '8',c00_low_value,Null)) AS '8',
sum(IF(ccrnumber = '9',c00_low_value,Null)) AS '9'
FROM vault2 where date_format(timestamp,'%m %d %Y')='11 23 2003'
GROUP BY date_format(timestamp,'%m %d %Y %T')
ORDER BY date_format(timestamp,'%m %d %Y %T') DESC LIMIT 1;
Returns:
+--------------------+---+---+---+---+---+---+---+---+---+
|timestamp | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
+--------------------+---+---+---+---+---+---+---+---+---+
|11 23 2003 14:55:32 | 4 | 3 | 5 | 7 | 8 | 4 | 5 | 6 | 4 |
+--------------------+---+---+---+---+---+---+---+---+---+
I am now faced with a new problem. How can I select the greatest
date/time value for each date in a range?
I have tried the following:
SELECT
Max(date_format(timestamp,'%m %d %Y %T')) AS timestamp,
sum(IF(ccrnumber = '1',c00_low_value,Null)) AS '1',
sum(IF(ccrnumber = '2',c00_low_value,Null)) AS '2',
sum(IF(ccrnumber = '3',c00_low_value,Null)) AS '3',
sum(IF(ccrnumber = '4',c00_low_value,Null)) AS '4',
sum(IF(ccrnumber = '5',c00_low_value,Null)) AS '5',
sum(IF(ccrnumber = '6',c00_low_value,Null)) AS '6',
sum(IF(ccrnumber = '7',c00_low_value,Null)) AS '7',
sum(IF(ccrnumber = '8',c00_low_value,Null)) AS '8',
sum(IF(ccrnumber = '9',c00_low_value,Null)) AS '9'
FROM vault2 where date_format(timestamp,'%m %d %Y')>='11 23 2003' AND
date_format(timestamp,'%m %d %Y')<='11 27 2003'
GROUP BY date_format(timestamp,'%m %d %Y %T')
ORDER BY date_format(timestamp,'%m %d %Y %T');
The above returns the following results:
+--------------------+---+---+---+---+---+---+---+---+---+
|timestamp | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
+--------------------+---+---+---+---+---+---+---+---+---+
|11 23 2003 14:54:32 | 3 | 2 | 5 | 1 | 2 | 2 | 8 | 2 | 1 |
|11 23 2003 14:55:32 | 4 | 3 | 5 | 7 | 8 | 4 | 5 | 6 | 4 |***
|11 25 2003 14:54:35 | 1 | 2 | 6 | 2 | 2 | 3 | 2 | 2 | 3 |***
|11 27 2003 14:54:31 | 3 | 2 | 2 | 7 | 2 | 8 | 2 | 2 | 3 |
|11 27 2003 14:54:33 | 4 | 2 | 5 | 4 | 2 | 6 | 5 | 2 | 2 |***
+--------------------+---+---+---+---+---+---+---+---+---+
This is not what I desire. I wish to return the rows marked with '***'
above which are the greatest date/time for a given day and just those
rows.
If anyone can please help me it would be greatly appreciated.
Thanks in advance. :) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Jeff Rodriguez |
last post by:
Here's what I want do:
Have a main daemon which starts up several threads in a Boss-Queue structure.
From those threads, I want them all to sit and watch a queue. Once an entry
goes into the...
|
by: Pieter |
last post by:
Hi,
I'm having some weird problem using the BackGroundWorker in an Outlook
(2003) Add-In, with VB.NET 2005:
I'm using the BackGroundWorker to get the info of some mailitems, and after
each item...
|
by: jlamanna |
last post by:
I was wondering if there was a utility that could tell you when your C#
application is making cross-apartment COM calls. I have a fairly large
application that makes extensive use of a 3rd party...
|
by: aspmonger |
last post by:
Hello,
I really believe that IE 6 has a new (intentional?) bug that severely limits the capability of dhtml and cross domain scripting. Yesterday, I read an interesting article about the subject and...
|
by: Robert Bravery |
last post by:
Hi all,
Can some one show me how to achieve a cross product of arrays. So that if I
had two arrays (could be any number) with three elements in each (once again
could be any number) I would get:...
| | |
by: Simon |
last post by:
Hi All,
An experiment i'm doing requires requires a synchronous cross-domain
request, without using a proxy. I wondered if anyone had any ideas to
help me achieve this.
Below is what I have...
|
by: Spam Catcher |
last post by:
Hello Everyone,
I need to implement single sign on across serveral applications. Some
applications are under my control while others are under the control of 3rd
parties.
Can anyone suggest a...
|
by: Otacon22 |
last post by:
Hi all,
I want to create a robot with a router board based on processor
atheros 2.6, called "fonera".
I have installed a version of linux, Openwrt and python and i want to
use it for some...
|
by: ampo |
last post by:
Hello.
Can anyone help with cross-domain problem?
I have HTML page from server1 that send xmlHTTPRequest to server2.
How can I do it?
Thanks.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |
