Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Id Name Other_Columns
- -------------------------
- 1 A A_data_1
- 2 A A_data_2
- 3 A A_data_3
- 4 B B_data_1
- 5 B B_data_2
- 6 C C_data_1
- 1 A A_data_1
- 4 B B_data_1
- 6 C C_data_1
- 3 A A_data_3
- 5 B B_data_2
- 6 C C_data_1
- select * from (select * from messages ORDER BY id DESC) AS x GROUP BY name
- SELECT m1.*
- FROM messages m1 LEFT JOIN messages m2
- ON (m1.name = m2.name AND m1.id < m2.id)
- WHERE m2.id IS NULL;
- SELECT p1.postid
- FROM Posts p1
- INNER JOIN (SELECT pi.owneruserid, MAX(pi.postid) AS maxpostid
- FROM Posts pi GROUP BY pi.owneruserid) p2
- ON (p1.postid = p2.maxpostid)
- WHERE p1.owneruserid = 20860;
- 1 row in set (1 min 17.89 sec)
- +----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
- | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 76756 | |
- | 1 | PRIMARY | p1 | eq_ref | PRIMARY,PostId,OwnerUserId | PRIMARY | 8 | p2.maxpostid | 1 | Using where |
- | 2 | DERIVED | pi | index | NULL | OwnerUserId | 8 | NULL | 1151268 | Using index |
- +----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
- 3 rows in set (16.09 sec)
- SELECT p1.postid
- FROM Posts p1 LEFT JOIN posts p2
- ON (p1.owneruserid = p2.owneruserid AND p1.postid < p2.postid)
- WHERE p2.postid IS NULL AND p1.owneruserid = 20860;
- 1 row in set (0.28 sec)
- +----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
- | 1 | SIMPLE | p1 | ref | OwnerUserId | OwnerUserId | 8 | const | 1384 | Using index |
- | 1 | SIMPLE | p2 | ref | PRIMARY,PostId,OwnerUserId | OwnerUserId | 8 | const | 1384 | Using where; Using index; Not exists |
- +----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
- 2 rows in set (0.00 sec)
- CREATE TABLE `posts` (
- `PostId` bigint(20) unsigned NOT NULL auto_increment,
- `PostTypeId` bigint(20) unsigned NOT NULL,
- `AcceptedAnswerId` bigint(20) unsigned default NULL,
- `ParentId` bigint(20) unsigned default NULL,
- `CreationDate` datetime NOT NULL,
- `Score` int(11) NOT NULL default '0',
- `ViewCount` int(11) NOT NULL default '0',
- `Body` text NOT NULL,
- `OwnerUserId` bigint(20) unsigned NOT NULL,
- `OwnerDisplayName` varchar(40) default NULL,
- `LastEditorUserId` bigint(20) unsigned default NULL,
- `LastEditDate` datetime default NULL,
- `LastActivityDate` datetime default NULL,
- `Title` varchar(250) NOT NULL default '',
- `Tags` varchar(150) NOT NULL default '',
- `AnswerCount` int(11) NOT NULL default '0',
- `CommentCount` int(11) NOT NULL default '0',
- `FavoriteCount` int(11) NOT NULL default '0',
- `ClosedDate` datetime default NULL,
- PRIMARY KEY (`PostId`),
- UNIQUE KEY `PostId` (`PostId`),
- KEY `PostTypeId` (`PostTypeId`),
- KEY `AcceptedAnswerId` (`AcceptedAnswerId`),
- KEY `OwnerUserId` (`OwnerUserId`),
- KEY `LastEditorUserId` (`LastEditorUserId`),
- KEY `ParentId` (`ParentId`),
- CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`PostTypeId`) REFERENCES `posttypes` (`PostTypeId`)
- ) ENGINE=InnoDB;
- SELECT test_id, MAX(request_id), request_id
- FROM testresults
- GROUP BY test_id DESC;
- SELECT test_id, request_id
- FROM (
- SELECT test_id, MAX(request_id), request_id
- FROM testresults
- GROUP BY test_id DESC) as ids
- ORDER BY test_id;
- SELECT test_id, request_id
- FROM testresults, (SELECT @group:=NULL) as init
- WHERE IF(IFNULL(@group, -1)=@group:=test_id, 0, 1)
- ORDER BY test_id DESC, request_id DESC
- select
- a.*
- from
- messages a
- inner join
- (select name, max(id) as maxid from messages group by name) as b on
- a.id = b.maxid
- select
- a.*
- from
- messages a
- inner join
- (select name, max(other_col) as other_col
- from messages group by name) as b on
- a.name = b.name
- and a.other_col = b.other_col
- WITH Ranked AS (
- SELECT Id, Name, OtherColumns,
- ROW_NUMBER() OVER (
- PARTITION BY Name
- ORDER BY Id DESC
- ) AS rk
- FROM messages
- )
- SELECT Id, Name, OtherColumns
- FROM messages
- WHERE rk = 1;
- SELECT
- Id, Name, OtherColumns
- FROM messages
- WHERE NOT EXISTS (
- SELECT * FROM messages as M2
- WHERE M2.Name = messages.Name
- AND M2.Id > messages.Id
- )
- select * from messages where id in
- (select max(id) from messages group by Name)
- select m1.* from messages m1
- left outer join messages m2
- on ( m1.id<m2.id and m1.name=m2.name )
- where m2.id is null
- SELECT jos_categories.title AS name,
- joined .catid,
- joined .title,
- joined .introtext
- FROM jos_categories
- INNER JOIN (SELECT *
- FROM (SELECT `title`,
- catid,
- `created`,
- introtext
- FROM `jos_content`
- WHERE `sectionid` = 6
- ORDER BY `id` DESC) AS yes
- GROUP BY `yes`.`catid` DESC
- ORDER BY `yes`.`created` DESC) AS joined
- ON( joined.catid = jos_categories.id )
- SELECT *, Max(Id) FROM messages GROUP BY Name
- SELECT M1.*
- FROM MESSAGES M1,
- (
- SELECT SUBSTR(Others_data,1,2),MAX(Others_data) AS Max_Others_data
- FROM MESSAGES
- GROUP BY 1
- ) M2
- WHERE M1.Others_data = M2.Max_Others_data
- ORDER BY Others_data;
- SELECT id, name, other_columns
- FROM messages
- WHERE id IN (
- SELECT MAX(id)
- FROM messages
- GROUP BY name
- );
- DELETE FROM messages WHERE id NOT IN
- (SELECT m1.id
- FROM messages m1 LEFT JOIN messages m2
- ON (m1.name = m2.name AND m1.id < m2.id)
- WHERE m2.id IS NULL)
- DROP TABLE IF EXISTS UniqueIDs;
- CREATE Temporary table UniqueIDs (id Int(11));
- INSERT INTO UniqueIDs
- (SELECT T1.ID FROM Table T1 LEFT JOIN Table T2 ON
- (T1.Field1 = T2.Field1 AND T1.Field2 = T2.Field2 #Comparison Fields
- AND T1.ID < T2.ID)
- WHERE T2.ID IS NULL);
- DELETE FROM Table WHERE id NOT IN (SELECT ID FROM UniqueIDs);
- SELECT
- `Id`,
- `Name`,
- SUBSTRING_INDEX(
- GROUP_CONCAT(
- `Other_Columns`
- ORDER BY `Id` DESC
- SEPARATOR '||'
- ),
- '||',
- 1
- ) Other_Columns
- FROM
- messages
- GROUP BY `Name`
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement