Ok, this is my query:
SELECT video_category, video_url, video_date, video_title, short_description, MAX(video_id) FROM videos GROUP BY video_category
When it pulls the data, I get the correct row for the video_id, but it pulls the first row for each category for the others. So when I get the max result for the video_id of category 1, I get the max ID, but the first row in the table for the url, date, title, and description.
How can I have it pull the other columns that correspond with the max ID result?
Edit: Fixed.
SELECT * FROM videos WHERE video_id IN ( SELECT DISTINCT MAX(video_id) FROM videos GROUP BY video_category ) ORDER BY video_category ASC
In SQL Server there are several ways to get the MIN or MAX of multiple columns including methods using UNPIVOT, UNION, CASE, etc… However, the simplest method is by using FROM … VALUES i.e. table value constructor. Let's see an example. In this example, there is a table for items with five columns for prices.
SQL MIN() and MAX() Functions The MIN() function returns the smallest value of the selected column. The MAX() function returns the largest value of the selected column.
Here's the generic SQL query to two compare columns (column1, column2) in a table (table1). mysql> select * from table1 where column1 not in (select column2 from table1); In the above query, update table1, column1 and column2 as per your requirement.
I would try something like this:
SELECT s.video_id ,s.video_category ,s.video_url ,s.video_date ,s.video_title ,short_description FROM videos s JOIN (SELECT MAX(video_id) AS id FROM videos GROUP BY video_category) max ON s.video_id = max.id
which is quite faster that your own solution
I recently released a new technique to handle this type of problem in MySQL.
SCALAR-AGGREGATE REDUCTION
Scalar-Aggregate Reduction 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:
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With