· 8 years ago · Apr 06, 2018, 07:12 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
19 *
20FROM (SELECT
21 *
22FROM messages
23ORDER BY id DESC) AS x
24GROUP BY name
25
26WITH ranked_messages AS (
27 SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn
28 FROM messages AS m
29)
30SELECT * FROM ranked_messages WHERE rn = 1;
31
32SELECT m1.*
33FROM messages m1 LEFT JOIN messages m2
34 ON (m1.name = m2.name AND m1.id < m2.id)
35WHERE m2.id IS NULL;
36
37SELECT p1.postid
38FROM Posts p1
39INNER JOIN (SELECT pi.owneruserid, MAX(pi.postid) AS maxpostid
40 FROM Posts pi GROUP BY pi.owneruserid) p2
41 ON (p1.postid = p2.maxpostid)
42WHERE p1.owneruserid = 20860;
43
441 row in set (1 min 17.89 sec)
45
46+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
47| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
48+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
49| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 76756 | |
50| 1 | PRIMARY | p1 | eq_ref | PRIMARY,PostId,OwnerUserId | PRIMARY | 8 | p2.maxpostid | 1 | Using where |
51| 2 | DERIVED | pi | index | NULL | OwnerUserId | 8 | NULL | 1151268 | Using index |
52+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
533 rows in set (16.09 sec)
54
55SELECT p1.postid
56FROM Posts p1 LEFT JOIN posts p2
57 ON (p1.owneruserid = p2.owneruserid AND p1.postid < p2.postid)
58WHERE p2.postid IS NULL AND p1.owneruserid = 20860;
59
601 row in set (0.28 sec)
61
62+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
63| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
64+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
65| 1 | SIMPLE | p1 | ref | OwnerUserId | OwnerUserId | 8 | const | 1384 | Using index |
66| 1 | SIMPLE | p2 | ref | PRIMARY,PostId,OwnerUserId | OwnerUserId | 8 | const | 1384 | Using where; Using index; Not exists |
67+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
682 rows in set (0.00 sec)
69
70CREATE TABLE `posts` (
71 `PostId` bigint(20) unsigned NOT NULL auto_increment,
72 `PostTypeId` bigint(20) unsigned NOT NULL,
73 `AcceptedAnswerId` bigint(20) unsigned default NULL,
74 `ParentId` bigint(20) unsigned default NULL,
75 `CreationDate` datetime NOT NULL,
76 `Score` int(11) NOT NULL default '0',
77 `ViewCount` int(11) NOT NULL default '0',
78 `Body` text NOT NULL,
79 `OwnerUserId` bigint(20) unsigned NOT NULL,
80 `OwnerDisplayName` varchar(40) default NULL,
81 `LastEditorUserId` bigint(20) unsigned default NULL,
82 `LastEditDate` datetime default NULL,
83 `LastActivityDate` datetime default NULL,
84 `Title` varchar(250) NOT NULL default '',
85 `Tags` varchar(150) NOT NULL default '',
86 `AnswerCount` int(11) NOT NULL default '0',
87 `CommentCount` int(11) NOT NULL default '0',
88 `FavoriteCount` int(11) NOT NULL default '0',
89 `ClosedDate` datetime default NULL,
90 PRIMARY KEY (`PostId`),
91 UNIQUE KEY `PostId` (`PostId`),
92 KEY `PostTypeId` (`PostTypeId`),
93 KEY `AcceptedAnswerId` (`AcceptedAnswerId`),
94 KEY `OwnerUserId` (`OwnerUserId`),
95 KEY `LastEditorUserId` (`LastEditorUserId`),
96 KEY `ParentId` (`ParentId`),
97 CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`PostTypeId`) REFERENCES `posttypes` (`PostTypeId`)
98) ENGINE=InnoDB;
99
100SELECT test_id, MAX(request_id), request_id
101FROM testresults
102GROUP BY test_id DESC;
103
104SELECT test_id, request_id
105FROM (
106 SELECT test_id, MAX(request_id), request_id
107 FROM testresults
108 GROUP BY test_id DESC) as ids
109ORDER BY test_id;
110
111SELECT test_id, request_id
112FROM testresults, (SELECT @group:=NULL) as init
113WHERE IF(IFNULL(@group, -1)=@group:=test_id, 0, 1)
114ORDER BY test_id DESC, request_id DESC
115
116select
117 a.*
118from
119 messages a
120 inner join
121 (select name, max(id) as maxid from messages group by name) as b on
122 a.id = b.maxid
123
124select
125 a.*
126from
127 messages a
128 inner join
129 (select name, max(other_col) as other_col
130 from messages group by name) as b on
131 a.name = b.name
132 and a.other_col = b.other_col
133
134SELECT id, name, other_columns
135FROM messages
136WHERE id IN (
137 SELECT MAX(id)
138 FROM messages
139 GROUP BY name
140);
141
142select * from messages where id in
143(select max(id) from messages group by Name)
144
145select m1.* from messages m1
146left outer join messages m2
147on ( m1.id<m2.id and m1.name=m2.name )
148where m2.id is null
149
150SELECT *, Max(Id) FROM messages GROUP BY Name
151
152WITH Ranked AS (
153 SELECT Id, Name, OtherColumns,
154 ROW_NUMBER() OVER (
155 PARTITION BY Name
156 ORDER BY Id DESC
157 ) AS rk
158 FROM messages
159)
160 SELECT Id, Name, OtherColumns
161 FROM messages
162 WHERE rk = 1;
163
164SELECT
165 Id, Name, OtherColumns
166FROM messages
167WHERE NOT EXISTS (
168 SELECT * FROM messages as M2
169 WHERE M2.Name = messages.Name
170 AND M2.Id > messages.Id
171)
172
173SELECT
174 DISTINCT NAME,
175 MAX(MESSAGES) OVER(PARTITION BY NAME) MESSAGES
176FROM MESSAGE;
177
178SELECT
179 `Id`,
180 `Name`,
181 SUBSTRING_INDEX(
182 GROUP_CONCAT(
183 `Other_Columns`
184 ORDER BY `Id` DESC
185 SEPARATOR '||'
186 ),
187 '||',
188 1
189 ) Other_Columns
190FROM
191 messages
192GROUP BY `Name`
193
194SELECT
195 column1,
196 column2
197FROM
198 table_name
199WHERE id IN
200 (SELECT
201 MAX(id)
202 FROM
203 table_name
204 GROUP BY column1)
205ORDER BY column1 ;
206
207SELECT jos_categories.title AS name,
208 joined .catid,
209 joined .title,
210 joined .introtext
211FROM jos_categories
212 INNER JOIN (SELECT *
213 FROM (SELECT `title`,
214 catid,
215 `created`,
216 introtext
217 FROM `jos_content`
218 WHERE `sectionid` = 6
219 ORDER BY `id` DESC) AS yes
220 GROUP BY `yes`.`catid` DESC
221 ORDER BY `yes`.`created` DESC) AS joined
222 ON( joined.catid = jos_categories.id )
223
224SELECT d1.ID,Name,City FROM Demo_User d1
225INNER JOIN
226(SELECT MAX(ID) AS ID FROM Demo_User GROUP By NAME) AS P ON (d1.ID=P.ID);
227
228SELECT * FROM (SELECT * FROM Demo_User ORDER BY ID DESC) AS T GROUP BY NAME ;
229
230DELETE FROM messages WHERE id NOT IN
231 (SELECT m1.id
232 FROM messages m1 LEFT JOIN messages m2
233 ON (m1.name = m2.name AND m1.id < m2.id)
234 WHERE m2.id IS NULL)
235
236DROP TABLE IF EXISTS UniqueIDs;
237CREATE Temporary table UniqueIDs (id Int(11));
238
239INSERT INTO UniqueIDs
240 (SELECT T1.ID FROM Table T1 LEFT JOIN Table T2 ON
241 (T1.Field1 = T2.Field1 AND T1.Field2 = T2.Field2 #Comparison Fields
242 AND T1.ID < T2.ID)
243 WHERE T2.ID IS NULL);
244
245DELETE FROM Table WHERE id NOT IN (SELECT ID FROM UniqueIDs);
246
247SELECT M1.*
248FROM MESSAGES M1,
249(
250 SELECT SUBSTR(Others_data,1,2),MAX(Others_data) AS Max_Others_data
251 FROM MESSAGES
252 GROUP BY 1
253) M2
254WHERE M1.Others_data = M2.Max_Others_data
255ORDER BY Others_data;
256
257SELECT m1.* FROM messages m1 INNER JOIN (SELECT max(Id) as lastmsgId FROM messages GROUP BY Name) m2 ON m1.Id=m2.lastmsgId
258
259SELECT t1.Id,
260 t1.Name,
261 t1.Other_Columns
262FROM
263(
264 SELECT Id,
265 Name,
266 Other_Columns,
267 (
268 CASE Name WHEN @curA
269 THEN @curRow := @curRow + 1
270 ELSE @curRow := 1 AND @curA := Name END
271 ) + 1 AS rn
272 FROM messages t,
273 (SELECT @curRow := 0, @curA := '') r
274 ORDER BY Name,Id DESC
275)t1
276WHERE t1.rn = 1
277ORDER BY t1.Id;
278
279SELECT *
280FROM messages a
281WHERE Id = (SELECT MAX(Id) FROM messages WHERE a.Name = Name)
282
283Id Name Other_Columns
2843 A A_data_3
2855 B B_data_2
2866 C C_data_1
287
288SELECT DISTINCT ON (name) *
289FROM messages
290ORDER BY name, id DESC;
291
292select * from messages group by name desc