· 8 years ago · Nov 16, 2017, 01:20 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 test_id, MAX(request_id), request_id
89FROM testresults
90GROUP BY test_id DESC;
91
92SELECT test_id, request_id
93FROM (
94 SELECT test_id, MAX(request_id), request_id
95 FROM testresults
96 GROUP BY test_id DESC) as ids
97ORDER BY test_id;
98
99SELECT test_id, request_id
100FROM testresults, (SELECT @group:=NULL) as init
101WHERE IF(IFNULL(@group, -1)=@group:=test_id, 0, 1)
102ORDER BY test_id DESC, request_id DESC
103
104select
105 a.*
106from
107 messages a
108 inner join
109 (select name, max(id) as maxid from messages group by name) as b on
110 a.id = b.maxid
111
112select
113 a.*
114from
115 messages a
116 inner join
117 (select name, max(other_col) as other_col
118 from messages group by name) as b on
119 a.name = b.name
120 and a.other_col = b.other_col
121
122SELECT id, name, other_columns
123FROM messages
124WHERE id IN (
125 SELECT MAX(id)
126 FROM messages
127 GROUP BY name
128);
129
130select * from messages where id in
131(select max(id) from messages group by Name)
132
133select m1.* from messages m1
134left outer join messages m2
135on ( m1.id<m2.id and m1.name=m2.name )
136where m2.id is null
137
138SELECT *, Max(Id) FROM messages GROUP BY Name
139
140WITH Ranked AS (
141 SELECT Id, Name, OtherColumns,
142 ROW_NUMBER() OVER (
143 PARTITION BY Name
144 ORDER BY Id DESC
145 ) AS rk
146 FROM messages
147)
148 SELECT Id, Name, OtherColumns
149 FROM messages
150 WHERE rk = 1;
151
152SELECT
153 Id, Name, OtherColumns
154FROM messages
155WHERE NOT EXISTS (
156 SELECT * FROM messages as M2
157 WHERE M2.Name = messages.Name
158 AND M2.Id > messages.Id
159)
160
161SELECT
162 column1,
163 column2
164FROM
165 table_name
166WHERE id IN
167 (SELECT
168 MAX(id)
169 FROM
170 table_name
171 GROUP BY column1)
172ORDER BY column1 ;
173
174SELECT
175 DISTINCT NAME,
176 MAX(MESSAGES) OVER(PARTITION BY NAME) MESSAGES
177FROM MESSAGE;
178
179SELECT jos_categories.title AS name,
180 joined .catid,
181 joined .title,
182 joined .introtext
183FROM jos_categories
184 INNER JOIN (SELECT *
185 FROM (SELECT `title`,
186 catid,
187 `created`,
188 introtext
189 FROM `jos_content`
190 WHERE `sectionid` = 6
191 ORDER BY `id` DESC) AS yes
192 GROUP BY `yes`.`catid` DESC
193 ORDER BY `yes`.`created` DESC) AS joined
194 ON( joined.catid = jos_categories.id )
195
196SELECT
197 `Id`,
198 `Name`,
199 SUBSTRING_INDEX(
200 GROUP_CONCAT(
201 `Other_Columns`
202 ORDER BY `Id` DESC
203 SEPARATOR '||'
204 ),
205 '||',
206 1
207 ) Other_Columns
208FROM
209 messages
210GROUP BY `Name`
211
212SELECT d1.ID,Name,City FROM Demo_User d1
213INNER JOIN
214(SELECT MAX(ID) AS ID FROM Demo_User GROUP By NAME) AS P ON (d1.ID=P.ID);
215
216SELECT * FROM (SELECT * FROM Demo_User ORDER BY ID DESC) AS T GROUP BY NAME ;
217
218DELETE FROM messages WHERE id NOT IN
219 (SELECT m1.id
220 FROM messages m1 LEFT JOIN messages m2
221 ON (m1.name = m2.name AND m1.id < m2.id)
222 WHERE m2.id IS NULL)
223
224DROP TABLE IF EXISTS UniqueIDs;
225CREATE Temporary table UniqueIDs (id Int(11));
226
227INSERT INTO UniqueIDs
228 (SELECT T1.ID FROM Table T1 LEFT JOIN Table T2 ON
229 (T1.Field1 = T2.Field1 AND T1.Field2 = T2.Field2 #Comparison Fields
230 AND T1.ID < T2.ID)
231 WHERE T2.ID IS NULL);
232
233DELETE FROM Table WHERE id NOT IN (SELECT ID FROM UniqueIDs);
234
235SELECT M1.*
236FROM MESSAGES M1,
237(
238 SELECT SUBSTR(Others_data,1,2),MAX(Others_data) AS Max_Others_data
239 FROM MESSAGES
240 GROUP BY 1
241) M2
242WHERE M1.Others_data = M2.Max_Others_data
243ORDER BY Others_data;
244
245SELECT m1.* FROM messages m1 INNER JOIN (SELECT max(Id) as lastmsgId FROM messages GROUP BY Name) m2 ON m1.Id=m2.lastmsgId
246
247SELECT t1.Id,
248 t1.Name,
249 t1.Other_Columns
250FROM
251(
252 SELECT Id,
253 Name,
254 Other_Columns,
255 (
256 CASE Name WHEN @curA
257 THEN @curRow := @curRow + 1
258 ELSE @curRow := 1 AND @curA := Name END
259 ) + 1 AS rn
260 FROM messages t,
261 (SELECT @curRow := 0, @curA := '') r
262 ORDER BY Name,Id DESC
263)t1
264WHERE t1.rn = 1
265ORDER BY t1.Id;
266
267select * from messages group by name desc
268
269SELECT DISTINCT ON (name) *
270FROM messages
271ORDER BY name, id DESC;