![]() |
Retrieving the last record in each group - MySQL - Printable Version +- 0Day Forums (https://0day.red) +-- Forum: Coding (https://0day.red/Forum-Coding) +--- Forum: Database (https://0day.red/Forum-Database) +---- Forum: MySQL (https://0day.red/Forum-MySQL) +---- Thread: Retrieving the last record in each group - MySQL (/Thread-Retrieving-the-last-record-in-each-group-MySQL) |
Retrieving the last record in each group - MySQL - Prodriveled965 - 07-27-2023 There is a table `messages` that contains data as shown below: 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 If I run a query `select * from messages group by name`, I will get the result as: 1 A A_data_1 4 B B_data_1 6 C C_data_1 What query will return the following result? 3 A A_data_3 5 B B_data_2 6 C C_data_1 That is, the last record in each group should be returned. At present, this is the query that I use: SELECT * FROM (SELECT * FROM messages ORDER BY id DESC) AS x GROUP BY name But this looks highly inefficient. Any other ways to achieve the same result? RE: Retrieving the last record in each group - MySQL - DeeAnn697480 - 07-27-2023 Use your [subquery][1] to return the correct grouping, because you're halfway there. Try this: 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 If it's not `id` you want the max of: 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 This way, you avoid correlated subqueries and/or ordering in your subqueries, which tend to be very slow/inefficient. [1]: [To see links please register here] RE: Retrieving the last record in each group - MySQL - biswajitais - 07-27-2023 Here are two suggestions. First, if mysql supports ROW_NUMBER(), it's very simple: 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; I'm assuming by "last" you mean last in Id order. If not, change the ORDER BY clause of the ROW\_NUMBER() window accordingly. If ROW\_NUMBER() isn't available, this is another solution: Second, if it doesn't, this is often a good way to proceed: SELECT Id, Name, OtherColumns FROM messages WHERE NOT EXISTS ( SELECT * FROM messages as M2 WHERE M2.Name = messages.Name AND M2.Id > messages.Id ) In other words, select messages where there is no later-Id message with the same Name. RE: Retrieving the last record in each group - MySQL - siewehlwhx - 07-27-2023 Try this: 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 ) RE: Retrieving the last record in each group - MySQL - Sirboroughpdjbztdlui - 07-27-2023 The below query will work fine as per your question. 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; RE: Retrieving the last record in each group - MySQL - citadel269291 - 07-27-2023 I arrived at a different solution, which is to get the IDs for the last post within each group, then select from the messages table using the result from the first query as the argument for a `WHERE x IN` construct: SELECT id, name, other_columns FROM messages WHERE id IN ( SELECT MAX(id) FROM messages GROUP BY name ); I don't know how this performs compared to some of the other solutions, but it worked spectacularly for my table with 3+ million rows. (4 second execution with 1200+ results) *This should work both on MySQL and SQL Server.* RE: Retrieving the last record in each group - MySQL - beflowered658558 - 07-27-2023 I've not yet tested with large DB but I think this could be faster than joining tables: SELECT *, Max(Id) FROM messages GROUP BY Name RE: Retrieving the last record in each group - MySQL - Sirinterpolaterzbfzrttu - 07-27-2023 Here is another way to get the last related record using `GROUP_CONCAT` with order by and `SUBSTRING_INDEX` to pick one of the record from the list SELECT `Id`, `Name`, SUBSTRING_INDEX( GROUP_CONCAT( `Other_Columns` ORDER BY `Id` DESC SEPARATOR '||' ), '||', 1 ) Other_Columns FROM messages GROUP BY `Name` Above query will group the all the `Other_Columns` that are in same `Name` group and using `ORDER BY id DESC` will join all the `Other_Columns` in a specific group in descending order with the provided separator in my case i have used `||` ,using `SUBSTRING_INDEX` over this list will pick the first one [Fiddle Demo][1] - [1]: [To see links please register here] RE: Retrieving the last record in each group - MySQL - tenillenoua - 07-27-2023 SELECT column1, column2 FROM table_name WHERE id IN (SELECT MAX(id) FROM table_name GROUP BY column1) ORDER BY column1 ; RE: Retrieving the last record in each group - MySQL - bromoform323 - 07-27-2023 Hi @Vijay Dev if your table **messages** contains **Id** which is auto increment primary key then to fetch the latest record basis on the primary key your query should read as below: SELECT m1.* FROM messages m1 INNER JOIN (SELECT max(Id) as lastmsgId FROM messages GROUP BY Name) m2 ON m1.Id=m2.lastmsgId |