1

I have this output from mysql and i want sort to the highst unix timestamp first. ctime variable is unix timestamp

i have addet a secend foreach i $output { to my code

bind pub "-|-" !grptop add:grptop
proc add:grptop {nick host handle channel text} {

global mysql

set output [AA BB CC DD EE FF]
foreach i $output {
set sql "SELECT * FROM name WHERE grp = '$i' ORDER BY ctime DESC LIMIT 1"
set result [mysqlsel $mysql $sql -list]
set record [lindex $result 0];
set name [lindex $record 2];
set ctime [lindex $record 6];
set date [clock format [lindex [split $ctime] 0] -format {%d:%m:%Y}];
putnow "PRIVMSG $channel :\00314\[\00307$i\00314\]\00300 >\00314 [getLongTime $ctime] \00300> \00314$name"
}   
}

proc getLongTime {ctime} {
set date [clock format [lindex [split $ctime] 0] -format {%d:%m:%Y}];
   set elapsed [duration [expr [clock seconds] - $ctime]];
regsub -all { second(s)?} $elapsed s elapsed;
regsub -all { minute(s)?} $elapsed m elapsed;
regsub -all { hour(s)?} $elapsed h elapsed;
regsub -all { day(s)?} $elapsed d elapsed;
regsub -all { week(s)?} $elapsed w elapsed;
regsub -all { month(s)?} $elapsed m elapsed;
regsub -all { year(s)?} $elapsed y elapsed;
return $elapsed;
}

output its :

<testbot> [AA] > 1d 3h 37m 41s > testname1
<testbot> [CC] > 1y 17w 2d 7h 25m 16s > testname2
<testbot> [DD] > 2h 45m 7s > testname3
<testbot> [BB] > 1d 21h 57m 15s > testname4
<testbot> [EE] > 42m 40s > testname5

and not sorted in time

Donal Fellows
  • 133,037
  • 18
  • 149
  • 215
Mike Shiwa
  • 71
  • 6
  • Why exactly is the output *not* sorted, it appears to me in perfect descending time order? – mrcalvin Apr 23 '19 at 17:00
  • oh sorry, that was a coincidence.. – Mike Shiwa Apr 23 '19 at 17:02
  • There seems to be some confusion in the logic of your program, I am afraid: each query is limited to returning only one record (`LIMIT 1`, per group `grp`). So, `ORDER BY` will not return a result set of timestamp-ordered entries per group, just the first of the ordered entries per group. Besides, this way the entries will not be ordered globally across all groups. What is your intention? You should add the `sql` tag, as this is only marginally, if at all, related to `tcl`. – mrcalvin Apr 23 '19 at 18:03
  • ORDER BY ctime DESC LIMIT 1, i have set this because i just want the latest from each grp – Mike Shiwa Apr 23 '19 at 20:41

1 Answers1

0

As explained in my comment, this is due to the query (SELECT) not operating in line with your expectations:

SELECT * FROM name WHERE grp = '$i' ORDER BY ctime DESC LIMIT 1

This returns the most recent (ctime) for each group (grp), but it does not provide for an ordering across groups. For this, and depending on your exact schema, you must rewrite your query to sth. like:

SELECT n1.*
FROM name n1
WHERE ctime = (SELECT MAX(n2.ctime)
               FROM name n2
               WHERE n2.grp = n1.grp);
  • The inner SELECT obtains the most recent (highest, max) timestamp for a given grp.
  • The outer SELECT obtains the details of entries matching the most recent (highest, max) timestamp for a given group.
  • You might want to add LIMIT 1 too, to the outher SELECT, in case your data as multiple entries per timestamp (timestamp granularity).

This is not about tcl, however, that is not to say that you could not do the grouping/ filtering in Tcl per se.

mrcalvin
  • 3,291
  • 12
  • 18