· 8 years ago · May 14, 2018, 06:00 PM
1Id Name Other_Columns
2-------------------------
31 A A_data_1
42 A A_data_2
53 A A_data_3
64 B B_data_1
75 B B_data_2
86 C C_data_1
9
101 A A_data_1
114 B B_data_1
126 C C_data_1
13
143 A A_data_3
155 B B_data_2
166 C C_data_1
17
18select * from (select * from messages ORDER BY id DESC) AS x GROUP BY name
19
20SELECT m1.*
21FROM messages m1 LEFT JOIN messages m2
22 ON (m1.name = m2.name AND m1.id < m2.id)
23WHERE m2.id IS NULL;
24
25SELECT p1.postid
26FROM Posts p1
27INNER JOIN (SELECT pi.owneruserid, MAX(pi.postid) AS maxpostid
28 FROM Posts pi GROUP BY pi.owneruserid) p2
29 ON (p1.postid = p2.maxpostid)
30WHERE p1.owneruserid = 20860;
31
321 row in set (1 min 17.89 sec)
33
34+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
35| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
36+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
37| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 76756 | |
38| 1 | PRIMARY | p1 | eq_ref | PRIMARY,PostId,OwnerUserId | PRIMARY | 8 | p2.maxpostid | 1 | Using where |
39| 2 | DERIVED | pi | index | NULL | OwnerUserId | 8 | NULL | 1151268 | Using index |
40+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
413 rows in set (16.09 sec)
42
43SELECT p1.postid
44FROM Posts p1 LEFT JOIN posts p2
45 ON (p1.owneruserid = p2.owneruserid AND p1.postid < p2.postid)
46WHERE p2.postid IS NULL AND p1.owneruserid = 20860;
47
481 row in set (0.28 sec)
49
50+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
51| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
52+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
53| 1 | SIMPLE | p1 | ref | OwnerUserId | OwnerUserId | 8 | const | 1384 | Using index |
54| 1 | SIMPLE | p2 | ref | PRIMARY,PostId,OwnerUserId | OwnerUserId | 8 | const | 1384 | Using where; Using index; Not exists |
55+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
562 rows in set (0.00 sec)
57
58CREATE TABLE `posts` (
59 `PostId` bigint(20) unsigned NOT NULL auto_increment,
60 `PostTypeId` bigint(20) unsigned NOT NULL,
61 `AcceptedAnswerId` bigint(20) unsigned default NULL,
62 `ParentId` bigint(20) unsigned default NULL,
63 `CreationDate` datetime NOT NULL,
64 `Score` int(11) NOT NULL default '0',
65 `ViewCount` int(11) NOT NULL default '0',
66 `Body` text NOT NULL,
67 `OwnerUserId` bigint(20) unsigned NOT NULL,
68 `OwnerDisplayName` varchar(40) default NULL,
69 `LastEditorUserId` bigint(20) unsigned default NULL,
70 `LastEditDate` datetime default NULL,
71 `LastActivityDate` datetime default NULL,
72 `Title` varchar(250) NOT NULL default '',
73 `Tags` varchar(150) NOT NULL default '',
74 `AnswerCount` int(11) NOT NULL default '0',
75 `CommentCount` int(11) NOT NULL default '0',
76 `FavoriteCount` int(11) NOT NULL default '0',
77 `ClosedDate` datetime default NULL,
78 PRIMARY KEY (`PostId`),
79 UNIQUE KEY `PostId` (`PostId`),
80 KEY `PostTypeId` (`PostTypeId`),
81 KEY `AcceptedAnswerId` (`AcceptedAnswerId`),
82 KEY `OwnerUserId` (`OwnerUserId`),
83 KEY `LastEditorUserId` (`LastEditorUserId`),
84 KEY `ParentId` (`ParentId`),
85 CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`PostTypeId`) REFERENCES `posttypes` (`PostTypeId`)
86) ENGINE=InnoDB;
87
88select
89 a.*
90from
91 messages a
92 inner join
93 (select name, max(id) as maxid from messages group by name) as b on
94 a.id = b.maxid
95
96select
97 a.*
98from
99 messages a
100 inner join
101 (select name, max(other_col) as other_col
102 from messages group by name) as b on
103 a.name = b.name
104 and a.other_col = b.other_col
105
106WITH Ranked AS (
107 SELECT Id, Name, OtherColumns,
108 ROW_NUMBER() OVER (
109 PARTITION BY Name
110 ORDER BY Id DESC
111 ) AS rk
112 FROM messages
113)
114 SELECT Id, Name, OtherColumns
115 FROM messages
116 WHERE rk = 1;
117
118SELECT
119 Id, Name, OtherColumns
120FROM messages
121WHERE NOT EXISTS (
122 SELECT * FROM messages as M2
123 WHERE M2.Name = messages.Name
124 AND M2.Id > messages.Id
125)
126
127SELECT M1.*
128FROM MESSAGES M1,
129(
130 SELECT SUBSTR(Others_data,1,2),MAX(Others_data) AS Max_Others_data
131 FROM MESSAGES
132 GROUP BY 1
133) M2
134WHERE M1.Others_data = M2.Max_Others_data
135ORDER BY Others_data;
136
137DELETE FROM messages WHERE id NOT IN
138 (SELECT m1.id
139 FROM messages m1 LEFT JOIN messages m2
140 ON (m1.name = m2.name AND m1.id < m2.id)
141 WHERE m2.id IS NULL)
142
143DROP TABLE IF EXISTS UniqueIDs;
144CREATE Temporary table UniqueIDs (id Int(11));
145
146INSERT INTO UniqueIDs
147 (SELECT T1.ID FROM Table T1 LEFT JOIN Table T2 ON
148 (T1.Field1 = T2.Field1 AND T1.Field2 = T2.Field2 #Comparison Fields
149 AND T1.ID < T2.ID)
150 WHERE T2.ID IS NULL);
151
152DELETE FROM Table WHERE id NOT IN (SELECT ID FROM UniqueIDs);
153
154SELECT jos_categories.title AS name,
155 joined .catid,
156 joined .title,
157 joined .introtext
158FROM jos_categories
159 INNER JOIN (SELECT *
160 FROM (SELECT `title`,
161 catid,
162 `created`,
163 introtext
164 FROM `jos_content`
165 WHERE `sectionid` = 6
166 ORDER BY `id` DESC) AS yes
167 GROUP BY `yes`.`catid` DESC
168 ORDER BY `yes`.`created` DESC) AS joined
169 ON( joined.catid = jos_categories.id )