SQL Query to get column values that correspond with MAX value of another column?

I recently invented a new technique to simulate Window Functions in MySQL. I call it Scalar-Aggregate Reduction.

This is by far the highest-performance approach and simplest method (in DB engine terms) for accomplishing this, because it requires no joins, no subqueries, and no CTE.

For your query, it would look something like this:

SELECT
  video_category,
  MAX(video_id) AS video_id,
  SUBSTRING(MAX(CONCAT(LPAD(video_id, 11, '0'), video_url)), 12) AS video_url,
  SUBSTRING(MAX(CONCAT(LPAD(video_id, 11, '0'), video_date)), 12) AS video_date,
  SUBSTRING(MAX(CONCAT(LPAD(video_id, 11, '0'), video_title)), 12) AS video_title,
  SUBSTRING(MAX(CONCAT(LPAD(video_id, 11, '0'), short_description)), 12) AS short_description
FROM
  videos
GROUP BY
  video_category

The combination of scalar and aggregate functions does the following:

1. LPADs the intra-aggregate correlated identifier to allow proper string comparison (e.g. "0009" and "0025" will be properly ranked). I'm LPADDING to 11 characters here assuming an INT primary key. If you use a BIGINT, you will want to increase this to support your table's ordinality. If you're comparing on a DATETIME field (fixed length), no padding is necessary.
2. CONCATs the padded identifier with the output column (so you get "00000000009myvalue" vs "0000000025othervalue")
3. MAX the aggregate set, which will yield "00000000025othervalue" as the winner.
4. SUBSTRING the result, which will truncate the compared identifier portion, leaving only the value.

If you want to retrieve values in types other than CHAR, you may need to performa an additional CAST on the output, e.g. if you want video_date to be a DATETIME:

CAST(SUBSTRING(MAX(CONCAT(LPAD(video_id, 11, '0'), video_date)), 12) AS DATETIME)

Another benefit of this method over the self-joining method is that you can combine other aggregate data (not just latest values), or even combine first AND last item in the same query, e.g.

SELECT
    -- Overall totals
    video_category,
    COUNT(1) AS videos_in_category,
    DATEDIFF(MAX(video_date), MIN(video_date)) AS timespan,
    
    -- Last video details
    MAX(video_id) AS last_video_id,
    SUBSTRING(MAX(CONCAT(LPAD(video_id, 11, '0'), video_url)), 12) AS last_video_url,
    ...
    
    -- First video details
    MIN(video_id) AS first_video_id,
    SUBSTRING(MIN(CONCAT(LPAD(video_id, 11, '0'), video_url)), 12) AS first_video_url,
    ...
    
    -- And so on

For further details explaining the benefits of this method vs other older methods, my full blog post is here: https://www.stevenmoseley.com/blog/tech/high-performance-sql-correlated-scalar-aggregate-reduction-queries

A slightly more "rustic" solution, but should do the job just the same:

SELECT
  video_category,
  video_url,
  video_date,
  video_title,
  short_description,
  video_id
FROM
  videos
ORDER BY video_id DESC
LIMIT 1;

In other words, just produce a table with all of the columns that you want, sort it so that your maximum value is at the top, and chop it off so you only return one row.

Here is a more general solution (handles duplicates)

CREATE TABLE test(
  i INTEGER,
  c INTEGER,
  v INTEGER
);


insert into test(i, c, v)
values
(3, 1, 1),
(3, 2, 2),
(3, 3, 3),
(4, 2, 4),
(4, 3, 5),
(4, 4, 6),
(5, 3, 7),
(5, 4, 8),
(5, 5, 9),
(6, 4, 10),
(6, 5, 11),
(6, 6, 12);



SELECT t.c, t.v
FROM test t
JOIN (SELECT test.c, max(i) as mi FROM test GROUP BY c) j ON
  t.i = j.mi AND
  t.c  = j.c
ORDER BY c;

SELECT video_category,video_url,video_date,video_title,short_description,video_id FROM videos t1 where video_id in (SELECT max(video_id) FROM videos t2 WHERE t1.video_category=t2.video_category );

Please provide your input and output records so that it can be understood properly and tested.