Skip to content

Instantly share code, notes, and snippets.

@cweagans

cweagans/gist:1651279

Created January 21, 2012 04:24
Show Gist options
  • Save cweagans/1651279 to your computer and use it in GitHub Desktop.
Save cweagans/1651279 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1
mysql> SELECT
-> pic.nid,
-> (
-> SELECT
-> pic2.sid
-> FROM
-> project_issue_comments pic2
-> WHERE
-> pic2.nid = 8 AND
-> pic2.comment_number = pic.comment_number - 1
-> ) as "original_state",
-> (
-> SELECT
-> pis2.name
-> FROM
-> project_issue_state pis2
-> WHERE
-> pis2.sid = original_state
-> ) as "original_state_name",
-> pic.sid as "new_state",
-> pis.name as "new_state_name",
-> pic.comment_number,
-> pic.timestamp
-> FROM
-> project_issue_comments pic
-> JOIN
-> project_issue_state pis ON pic.sid = pis.sid
-> WHERE
-> pic.nid = 8;
+------+----------------+------------------------------------+-----------+------------------------------------+----------------+------------+
| nid | original_state | original_state_name | new_state | new_state_name | comment_number | timestamp |
+------+----------------+------------------------------------+-----------+------------------------------------+----------------+------------+
| 8 | NULL | NULL | 1 | active | 1 | 1008653348 |
| 8 | 1 | active | 1 | active | 2 | 1011059204 |
| 8 | 1 | active | 1 | active | 3 | 1014567557 |
| 8 | 1 | active | 1 | active | 4 | 1014725251 |
| 8 | 1 | active | 1 | active | 5 | 1014740996 |
| 8 | 1 | active | 1 | active | 6 | 1014892452 |
| 8 | 1 | active | 1 | active | 7 | 1015194556 |
| 8 | 1 | active | 1 | active | 8 | 1015362264 |
| 8 | 1 | active | 1 | active | 9 | 1018908807 |
| 8 | 1 | active | 1 | active | 10 | 1067657869 |
| 8 | 1 | active | 1 | active | 11 | 1098705338 |
| 8 | 1 | active | 1 | active | 12 | 1131681912 |
| 8 | 1 | active | 1 | active | 13 | 1131681951 |
| 8 | 1 | active | 1 | active | 14 | 1131696988 |
| 8 | 1 | active | 1 | active | 15 | 1131699871 |
| 8 | 1 | active | 1 | active | 16 | 1135003648 |
| 8 | 1 | active | 1 | active | 17 | 1135003790 |
| 8 | 1 | active | 1 | active | 18 | 1136610909 |
| 8 | 1 | active | 1 | active | 19 | 1136611963 |
| 8 | 1 | active | 1 | active | 20 | 1136715700 |
| 8 | 1 | active | 1 | active | 21 | 1136835940 |
| 8 | 1 | active | 1 | active | 22 | 1136842410 |
| 8 | 1 | active | 1 | active | 23 | 1136850190 |
| 8 | 1 | active | 1 | active | 24 | 1136868668 |
| 8 | 1 | active | 1 | active | 25 | 1137203142 |
| 8 | 1 | active | 13 | needs work | 26 | 1137231468 |
| 8 | 13 | needs work | 13 | needs work | 27 | 1137268513 |
| 8 | 13 | needs work | 13 | needs work | 28 | 1137268901 |
| 8 | 13 | needs work | 13 | needs work | 29 | 1137368447 |
| 8 | 13 | needs work | 13 | needs work | 30 | 1137432962 |
| 8 | 13 | needs work | 13 | needs work | 31 | 1138985428 |
| 8 | 13 | needs work | 13 | needs work | 32 | 1139011556 |
| 8 | 13 | needs work | 13 | needs work | 33 | 1139011609 |
| 8 | 13 | needs work | 13 | needs work | 34 | 1140056043 |
| 8 | 13 | needs work | 13 | needs work | 35 | 1140396841 |
| 8 | 13 | needs work | 13 | needs work | 36 | 1140689175 |
| 8 | 13 | needs work | 13 | needs work | 37 | 1140727917 |
| 8 | 13 | needs work | 13 | needs work | 38 | 1141315551 |
| 8 | 13 | needs work | 13 | needs work | 39 | 1141330420 |
| 8 | 13 | needs work | 13 | needs work | 40 | 1141800989 |
| 8 | 13 | needs work | 13 | needs work | 41 | 1141994249 |
| 8 | 13 | needs work | 8 | needs review | 42 | 1149795918 |
| 8 | 8 | needs review | 13 | needs work | 43 | 1150067195 |
| 8 | 13 | needs work | 13 | needs work | 44 | 1153940178 |
| 8 | 13 | needs work | 13 | needs work | 45 | 1153945313 |
| 8 | 13 | needs work | 13 | needs work | 46 | 1161672872 |
| 8 | 13 | needs work | 8 | needs review | 47 | 1162485840 |
| 8 | 8 | needs review | 14 | reviewed & tested by the community | 48 | 1162486884 |
| 8 | 14 | reviewed & tested by the community | 14 | reviewed & tested by the community | 49 | 1162493515 |
| 8 | 14 | reviewed & tested by the community | 4 | postponed | 50 | 1163275828 |
| 8 | 4 | postponed | 8 | needs review | 51 | 1163454061 |
| 8 | 8 | needs review | 8 | needs review | 52 | 1176839399 |
| 8 | 8 | needs review | 8 | needs review | 53 | 1176841505 |
| 8 | 8 | needs review | 13 | needs work | 54 | 1182648593 |
| 8 | 13 | needs work | 13 | needs work | 55 | 1182768458 |
| 8 | 13 | needs work | 13 | needs work | 56 | 1182771381 |
| 8 | 13 | needs work | 13 | needs work | 57 | 1186658960 |
| 8 | 13 | needs work | 8 | needs review | 58 | 1186659086 |
| 8 | 8 | needs review | 13 | needs work | 59 | 1186664980 |
| 8 | 13 | needs work | 13 | needs work | 60 | 1186665514 |
| 8 | 13 | needs work | 13 | needs work | 61 | 1186666177 |
| 8 | 13 | needs work | 13 | needs work | 62 | 1186682852 |
| 8 | 13 | needs work | 13 | needs work | 63 | 1190807023 |
| 8 | 13 | needs work | 13 | needs work | 64 | 1191014981 |
| 8 | 13 | needs work | 13 | needs work | 65 | 1191015541 |
| 8 | 13 | needs work | 13 | needs work | 66 | 1191032068 |
| 8 | 13 | needs work | 13 | needs work | 67 | 1191307936 |
| 8 | 13 | needs work | 13 | needs work | 68 | 1191900721 |
| 8 | 13 | needs work | 13 | needs work | 69 | 1193849129 |
| 8 | 13 | needs work | 13 | needs work | 70 | 1194360754 |
| 8 | 13 | needs work | 13 | needs work | 71 | 1194362934 |
| 8 | 13 | needs work | 13 | needs work | 72 | 1194871969 |
| 8 | 13 | needs work | 13 | needs work | 73 | 1194934064 |
| 8 | 13 | needs work | 13 | needs work | 74 | 1194945397 |
| 8 | 13 | needs work | 13 | needs work | 75 | 1202290688 |
| 8 | 13 | needs work | 13 | needs work | 76 | 1202824618 |
| 8 | 13 | needs work | 13 | needs work | 77 | 1204176560 |
| 8 | 13 | needs work | 13 | needs work | 78 | 1204202126 |
| 8 | 13 | needs work | 13 | needs work | 79 | 1204870060 |
| 8 | 13 | needs work | 13 | needs work | 80 | 1204990695 |
| 8 | 13 | needs work | 13 | needs work | 81 | 1204999022 |
| 8 | 13 | needs work | 13 | needs work | 82 | 1205016808 |
| 8 | 13 | needs work | 13 | needs work | 83 | 1208209540 |
| 8 | 13 | needs work | 13 | needs work | 84 | 1208366873 |
| 8 | 13 | needs work | 13 | needs work | 85 | 1208535765 |
| 8 | 13 | needs work | 13 | needs work | 86 | 1208749398 |
| 8 | 13 | needs work | 13 | needs work | 87 | 1209405828 |
| 8 | 13 | needs work | 13 | needs work | 88 | 1209743719 |
| 8 | 13 | needs work | 13 | needs work | 89 | 1209751523 |
| 8 | 13 | needs work | 13 | needs work | 90 | 1209904160 |
| 8 | 13 | needs work | 13 | needs work | 91 | 1210657266 |
| 8 | 13 | needs work | 13 | needs work | 92 | 1211289866 |
| 8 | 13 | needs work | 13 | needs work | 93 | 1211447361 |
| 8 | 13 | needs work | 13 | needs work | 94 | 1212748500 |
| 8 | 13 | needs work | 13 | needs work | 95 | 1214441083 |
| 8 | 13 | needs work | 13 | needs work | 96 | 1214449023 |
| 8 | 13 | needs work | 13 | needs work | 97 | 1214801872 |
| 8 | 13 | needs work | 13 | needs work | 98 | 1217983768 |
| 8 | 13 | needs work | 13 | needs work | 99 | 1223743169 |
| 8 | 13 | needs work | 13 | needs work | 100 | 1225831844 |
| 8 | 13 | needs work | 13 | needs work | 101 | 1228271399 |
| 8 | 13 | needs work | 13 | needs work | 102 | 1229636316 |
| 8 | 13 | needs work | 13 | needs work | 103 | 1229646538 |
| 8 | 13 | needs work | 13 | needs work | 104 | 1230514121 |
| 8 | 13 | needs work | 8 | needs review | 105 | 1230860889 |
| 8 | 8 | needs review | 8 | needs review | 106 | 1230861923 |
| 8 | 8 | needs review | 8 | needs review | 107 | 1230874332 |
| 8 | 8 | needs review | 13 | needs work | 108 | 1230876700 |
| 8 | 13 | needs work | 8 | needs review | 109 | 1230877490 |
| 8 | 8 | needs review | 13 | needs work | 110 | 1230877681 |
| 8 | 13 | needs work | 13 | needs work | 111 | 1230881563 |
| 8 | 13 | needs work | 13 | needs work | 112 | 1230906745 |
| 8 | 13 | needs work | 13 | needs work | 113 | 1230910357 |
| 8 | 13 | needs work | 13 | needs work | 114 | 1230913749 |
| 8 | 13 | needs work | 13 | needs work | 115 | 1230914702 |
| 8 | 13 | needs work | 13 | needs work | 116 | 1230917897 |
| 8 | 13 | needs work | 13 | needs work | 117 | 1230927587 |
| 8 | 13 | needs work | 13 | needs work | 118 | 1230931863 |
| 8 | 13 | needs work | 13 | needs work | 119 | 1230936376 |
| 8 | 13 | needs work | 13 | needs work | 120 | 1230947023 |
| 8 | 13 | needs work | 8 | needs review | 121 | 1230948038 |
| 8 | 8 | needs review | 8 | needs review | 122 | 1230960135 |
| 8 | 8 | needs review | 8 | needs review | 123 | 1230989650 |
| 8 | 8 | needs review | 8 | needs review | 124 | 1230989910 |
| 8 | 8 | needs review | 8 | needs review | 125 | 1230990301 |
| 8 | 8 | needs review | 8 | needs review | 126 | 1231031380 |
| 8 | 8 | needs review | 8 | needs review | 127 | 1231043081 |
| 8 | 8 | needs review | 8 | needs review | 128 | 1231048949 |
| 8 | 8 | needs review | 8 | needs review | 129 | 1231052152 |
| 8 | 8 | needs review | 8 | needs review | 130 | 1231060484 |
| 8 | 8 | needs review | 8 | needs review | 131 | 1231061428 |
| 8 | 8 | needs review | 8 | needs review | 132 | 1231085498 |
| 8 | 8 | needs review | 8 | needs review | 133 | 1231114136 |
| 8 | 8 | needs review | 8 | needs review | 134 | 1231136094 |
| 8 | 8 | needs review | 8 | needs review | 135 | 1231145097 |
| 8 | 8 | needs review | 8 | needs review | 136 | 1231174660 |
| 8 | 8 | needs review | 8 | needs review | 137 | 1231185791 |
| 8 | 8 | needs review | 8 | needs review | 138 | 1231188414 |
| 8 | NULL | NULL | 8 | needs review | 140 | 1231197164 |
| 8 | 8 | needs review | 8 | needs review | 141 | 1231197960 |
| 8 | 8 | needs review | 8 | needs review | 142 | 1231198881 |
| 8 | 8 | needs review | 8 | needs review | 143 | 1231198971 |
| 8 | 8 | needs review | 8 | needs review | 144 | 1231246815 |
| 8 | 8 | needs review | 8 | needs review | 145 | 1231255956 |
| 8 | 8 | needs review | 8 | needs review | 146 | 1231294222 |
| 8 | 8 | needs review | 8 | needs review | 147 | 1231294270 |
| 8 | 8 | needs review | 8 | needs review | 148 | 1231300852 |
| 8 | 8 | needs review | 8 | needs review | 149 | 1231302490 |
| 8 | 8 | needs review | 8 | needs review | 150 | 1231303886 |
| 8 | 8 | needs review | 8 | needs review | 151 | 1231307977 |
| 8 | 8 | needs review | 13 | needs work | 152 | 1231313326 |
| 8 | 13 | needs work | 8 | needs review | 153 | 1231313760 |
| 8 | 8 | needs review | 8 | needs review | 154 | 1231317842 |
| 8 | 8 | needs review | 13 | needs work | 155 | 1231325466 |
| 8 | 13 | needs work | 8 | needs review | 156 | 1231334541 |
| 8 | 8 | needs review | 8 | needs review | 157 | 1231340311 |
| 8 | 8 | needs review | 8 | needs review | 158 | 1231347259 |
| 8 | 8 | needs review | 8 | needs review | 159 | 1231352050 |
| 8 | 8 | needs review | 8 | needs review | 160 | 1231352639 |
| 8 | 8 | needs review | 8 | needs review | 161 | 1231365460 |
| 8 | 8 | needs review | 13 | needs work | 162 | 1231383612 |
| 8 | 13 | needs work | 8 | needs review | 163 | 1231391722 |
| 8 | 8 | needs review | 13 | needs work | 164 | 1231397259 |
| 8 | 13 | needs work | 8 | needs review | 165 | 1231399688 |
| 8 | 8 | needs review | 8 | needs review | 166 | 1231399769 |
| 8 | 8 | needs review | 8 | needs review | 167 | 1231401093 |
| 8 | 8 | needs review | 14 | reviewed & tested by the community | 168 | 1231402247 |
| 8 | 14 | reviewed & tested by the community | 14 | reviewed & tested by the community | 169 | 1231402613 |
| 8 | 14 | reviewed & tested by the community | 14 | reviewed & tested by the community | 170 | 1231402669 |
| 8 | 14 | reviewed & tested by the community | 2 | fixed | 171 | 1231404268 |
| 8 | 2 | fixed | 13 | needs work | 172 | 1231404544 |
| 8 | 13 | needs work | 13 | needs work | 173 | 1231406973 |
| 8 | 13 | needs work | 2 | fixed | 174 | 1231408056 |
| 8 | 2 | fixed | 2 | fixed | 175 | 1231443161 |
| 8 | 2 | fixed | 2 | fixed | 176 | 1231472835 |
| 8 | 2 | fixed | 7 | closed (fixed) | 177 | 1232704202 |
| 8 | 7 | closed (fixed) | 7 | closed (fixed) | 178 | 1232735395 |
| 8 | 7 | closed (fixed) | 7 | closed (fixed) | 179 | 1232736151 |
| 8 | 7 | closed (fixed) | 7 | closed (fixed) | 180 | 1232737612 |
| 8 | 7 | closed (fixed) | 7 | closed (fixed) | 181 | 1232746702 |
| 8 | 7 | closed (fixed) | 7 | closed (fixed) | 182 | 1268540269 |
| 8 | 7 | closed (fixed) | 7 | closed (fixed) | 183 | 1276835832 |
| 8 | 7 | closed (fixed) | 7 | closed (fixed) | 184 | 1276868818 |
| 8 | 7 | closed (fixed) | 7 | closed (fixed) | 185 | 1276872619 |
| 8 | 7 | closed (fixed) | 7 | closed (fixed) | 186 | 1276938610 |
+------+----------------+------------------------------------+-----------+------------------------------------+----------------+------------+
185 rows in set (0.32 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment