jbanks
Posts: 4
|
| Posted: 09/17/2009, 2:25 PM |
|
I'm really green at this, but I'm trying out CCS and this is my issue. I've got a table and I only want the last x entries for a device. I can get exactly what I want by doing a row limit to, say, 5 rows. For example, I get this:
123
abc
345
defg
6789
But this is the oldest data in the table. To get the newest data in the table, I can order on a column and order descending. Which also gives me exactly what I want, but now my data is in reverse order:
6789
defg
345
abc
123
Using CCS, what is the easiest way to reverse the order of data obtained by sorting DESC?
I've searched a ton of forums and the usual answer is something like this:
SELECT * FROM
(
SELECT *
FROM $tbl_shift_results
WHERE `catagory` = '12'
ORDER
BY `time` DESC
LIMIT 30
)x
ORDER BY `time`;
Or sometimes it is suggested to use a reverse array in PHP.
Anybody have any suggestions on the best way to approach this issue?
Thanks,
|
 |
 |
ckroon
Posts: 869
|
| Posted: 09/17/2009, 2:50 PM |
|
Use the grid builder.
Makes it easy.
Order by your field, Desc order
Then limit the number of records.
_________________
Walter Kempees...you are dearly missed. |
|
|
|
datadoit
|
| Posted: 09/17/2009, 2:51 PM |
|
Look at the Visual Query Builder (VQB) for your data source. Under the
ORDER section, look all the way to the lower right where you can select
Ascending or Descending. The default (as you found out) is Ascending.
|
|
|
|
jbanks
Posts: 4
|
| Posted: 09/17/2009, 5:02 PM |
|
Quote :Use the grid builder.
Makes it easy.
Order by your field, Desc order
Then limit the number of records.
I don't think you guys are following me. If an ascended column order is changed to descended and the rows limited, you go from the oldest data in the table to the newest (I understand this), but you also reverse the order of the data in the process. See my example in this thread, because that is exactly what it does to the data.
What I'm wanting is like a sort inside of a sort, and I can't figure out how to do it in CCS.
Here is a live example of exactly what I'm talking about, with a static coded table of my desired format:
http://www.nscada.com/test8/s865217.asp
|
|
|
|
damian
Posts: 838
|
| Posted: 09/17/2009, 8:34 PM |
|
change the data source to sql instead of table and use code like this:
SELECT
* FROM
(
SELECT *
FROM $tbl_shift_results
WHERE `catagory` = '12'
ORDER BY
'time' DESC
LIMIT 0, 5
)
as $tbl_shift_results_1
ORDER BY $tbl_shift_results_1.'time' ASC
_________________
if you found this post useful take the time to help someone else.... :)
|
|
|
|
jbanks
Posts: 4
|
| Posted: 09/19/2009, 1:31 PM |
|
Yes, although it took me a while, I got it to work just the way I wanted. Thank you for pointing me in the right direction.
Here is actual working code that worked in CCS with no errors for me:
SELECT * FROM
(
Select * FROM _865217 INNER JOIN tag_id ON _865217._NAME=tag_id._TAG
WHERE _865217._NAME LIKE '%865217-1%'
ORDER BY _865217.id DESC LIMIT 0, 5
)
as temp_tbl ORDER BY temp_tbl.id ASC
The biggest trouble I had is that on an INNER JOIN, there cannot be duplicate column names involved in either table. Code Charge Studio must take care of that with aliases or something, because it wasn't a problem until I tried to do it with an SQL statement instead of through the visual query builder.
Thanks,
|
|
|
|
jbanks
Posts: 4
|
| Posted: 09/19/2009, 1:41 PM |
|
As a side note, I spent a lot of time looking at solutions to this issue and dealing with them through MySQL statement vs. programming. It seems programming is the preferred way to do it, because if you have a huge database, the extra sorts can cause a performance hit when done through MySQL.
I did find one novel solution that involved adding a pseudo reverse index to your table. This was for a website that had a huge MySQL database and it lets them order the data without extra ORDER BY and ASC DESC statements.
Step by step is here at igvita.com, just google for "pseudo reverse indexes in mysql" and it is the first link.
|
|
|
|
damian
Posts: 838
|
| Posted: 09/19/2009, 9:20 PM |
|
Quote jbanks:The biggest trouble I had is that on an INNER JOIN, there cannot be duplicate column names involved in either table. Code Charge Studio must take care of that with aliases or something, because it wasn't a problem until I tried to do it with an SQL statement instead of through the visual query builder.
you must use an alias when doing a select from a select...
_________________
if you found this post useful take the time to help someone else.... :)
|
|
|
|
|
