0

I have a primary keyed access log table in MySQL with a column, say "section".

I am trying to get the most recent entry for multiple "section" values ie. main, settings, etc.

Am looking to do this in the quickest, most efficient way possible without multiple queries or an entire dump of the whole table each time, ie. to avoid SELECT * FROM table WHERE section = 'main' ORDER BY id DESC LIMIT 1 x however many sections am requesting (which is all of them) or SELECT * FROM table WHERE section = 'main' OR section = 'settings' OR etc... ORDER BY id and have a gajillion rows of data running around from server-to-script kind of deal.

I am requesting the data on behalf of a variable number of "section" panels that display when each was last accessed.

Is there a quick, single query that can return only the information needed (if there are 3 sections I need 3 unique rows, the most recent of each section, unless or course 1 section is not present in the table in which case I would need those that are) or must I essentially dump the whole table and parse it "myself" in PHP to obtain what I need?

John Smith
  • 490
  • 2
  • 11
  • 1
    You could use the simple `SELECT * FROM table WHERE section =
    ORDER BY id DESC LIMIT 1` query and `(...) UNION (...) UNION (...)` them. This is similar to running multiple queries, but it is in 1 query.
     – KIKO Software Oct 22 '22 at 06:40
  • @KIKOSoftware Thanks, dutchy! I seem to recall somewhere folks saying UNION vs. multiple queries for simple operations are more of a simplicity/organizational preference as opposed to any real optimization benefit/milliseconds saved but have not tested. Have you heard anything similar? – John Smith Oct 22 '22 at 07:16
  • 1
    I think there will be very little difference in practice, especially if you have a gajillion rows of data. What will make a difference is an index on the `section` column. – KIKO Software Oct 22 '22 at 07:34
  • Also covered by "[SQL query for finding representative rows in a table](/q/375262/90527)", "[How to find the record in a table that contains the maximum value?](/q/376518/90527)" – outis Oct 22 '22 at 08:41

1 Answers1

0

By Grouping the section and filtering by descending order makes unique id as top .

Query : SELECT * FROM table GROUP BY section DESC

Also it works

Naveen
  • 246
  • 1
  • 8
  • Although this seems a good idea, grouping rows together doesn't result in the data of the latest row for a group. You would need to use [grouping function](https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html). Now this might work, depending on the data in the rows, but I doubt it will in this case. – KIKO Software Oct 22 '22 at 09:16