SQL Query to get column values that correspond with MAX value of another column?
sql select max value from multiple columns
sql select row with max value
postgres select row with max value
mysql> select max and corresponding row
mysql select row with max value group by
sql max and min in one select
sqlalchemy select row with max value
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?
SELECT * FROM videos WHERE video_id IN ( SELECT DISTINCT MAX(video_id) FROM videos GROUP BY video_category ) ORDER BY video_category ASC
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
Select min and max values along with other SQL Server columns , In the case where multiple rows contain the same minimum or maximum value I only want to select the first occurrence. Read this tip to learn� A good query engine should be able to deduce the same or similar execution plan regardless of the approach. That said, using a derived table (i.e. approach #1) may be a bit more explicit to the query engine (even if less explicit to the reader of the query) and thus might perform better.
I'm late to the party here, but I just published a blog post on a method I've used for this for a couple years, and I thought I'd share this with the world.
I call the method Scalar Aggregate Comparison, and it's 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:
- LPADs the comparison 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.
- CONCATs the padded identifier with the output column (so you get "0009myvalue" vs "0025othervalue")
- MAX the aggregate set, which will yield "0025othervalue" as the winner.
- 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/high-performance-correlated-aggregate-sql-queries-without-ctes
Getting Max of a Column And Corresponding Value In Other Column, I have table table1 with columns A, B,C. I need a query to select value of C corresponding to max value of B. select A,B,C from table1, (select A max(B) B from� MaxDate = SUMMARIZE(signintelemetry,signintelemetry[UserId],"Max Date", MAX(signintelemetry[Timestamp])) Then do an inner join of this table to the original table on UserId and timestamp. When I try to do a merge though, PowerBI doesn't allow me to merge on a derived table.
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;
Selecting the row having the max value for one field, grouping by , Values in the table can look like: matchnum time entrant1 votes1 1305 2010-02- 06 Selecting the row having the max value for one field, grouping by another field select * from updates where 1 group by matchnum having (mysql calls this a “hidden” column), the value chosen for votes1 in the result� No problem :) If you want the exact output as shown in your example you can use the following SQL query to create a Power Pivot table: WITH CTE_MaxRev AS ( SELECT ID, MAX(Rev) OVER (PARTITION BY ID) AS MaxRev FROM #Table AS t2 ) SELECT ID, Rev, [Duration Time] FROM #Table AS t1 WHERE EXISTS ( SELECT ID, MaxRev FROM CTE_MaxRev AS mr WHERE t1.ID = mr.ID AND t1.Rev = mr.MaxRev )
MySQL, SELECT rows with MAX(Column value), DISTINCT by another column such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more. Input : '3' (This corresponds to tagid columns in Mappings table) Output Expected : 4 (This corresponds to productid column value int he last row of Mappings. please note productid 1 is not returned because 1 has other mappings (tag 1 and tag 2) associated with it).
How to select a row based on the maximum value of a column, when , select subscriber, date_column, time_column, value from ( select subscriber, date_column, time_column, value, rank() over (partition by subscriber order by� Obtain the value of any grouping column I know the basics in SQL programming and I know how to apply some tricks in SQL Server in order to get the result set, but I don't know all tricks in Oracle. I have these columns: col1 col2 col3 And I wrote this query SELECT col1, MAX(col3) AS mx3 FR
SQL: MAX Function, The SQL MAX function is used to return the maximum value of an expression in a one column in your SQL SELECT statement that is not encapsulated in the MAX This syntax is optimized for Oracle and may not work for other database� See this SQL Fiddle here for an online live demo. This CTE "partitions" your data by (ID, BLOWNUMBER), and the ROW_NUMBER() function hands out numbers, starting at 1, for each of those "partitions", ordered by the [Time] columns (newest time value first). Then, you just select from that CTE and use RN = 1 to get the most recent of each data
- why is the DISTINCT necessary?
- @carillonator its not.. its actually redundant, as MAX() will provide a unique result.. obviously
- Not sure how it's faster, but I'll use it. Still works, and that's all I care about. Thanks.
- I tested both on a similar table I have, mine took 0.02s yours 0.19s :)
- This only works if there is only one max video_id for each video category. Assuming the OP would arbitrarily select mong these duplicates, what is the solution to the more general problem?
- FWIW this approach helped me with a similar problem, many thanks
- FYI, benchmarked on a 1-million row production table in Aurora, Scalar Aggregate Comparison performed 20% better than the Subquery method.
- This is amazing, and EXACTLY what I was looking for. Thank you for this. I look forward to finding additional use cases for this method.
- I just spent a few hours optimizing a similar query on SQL Server 2014 and was coming on here to report the performance improvements that can be gained from this technique but found that you beat me to it. In my case, I was looking for the first & last date in an activity log along with the person who performed it. The T-SQL expressions I used to find the first action date and person were MIN(ActivityDate) FirstActivityDate and SUBSTRING(MIN(CONVERT(VARCHAR, ActivityDate, 21) + ActivityBy), 24, 256) FirstActivityBy. I got a 3X performance improvement over the CTE method for 130,000 rows.