By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,365 Members | 3,180 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,365 IT Pros & Developers. It's quick & easy.

cross-tab query - last date/time

P: n/a
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. :)
Jul 19 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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. :)
Jul 19 '05 #2

P: n/a
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. :)
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.