Skip to content

Instantly share code, notes, and snippets.

@cweagans

cweagans/gist:1651192

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