How can I select the row with the highest ID in MySQL?
SELECT * FROM permlog ORDER BY id DESC LIMIT 0, 1
Select entire row with max ID
Just use order by
:
select top (1) m.*
from measurements m
where m.client_id = 1
order by m.Measure_ID desc;
SQL select only rows with max value on a column
At first glance...
All you need is a GROUP BY
clause with the MAX
aggregate function:
SELECT id, MAX(rev)
FROM YourTable
GROUP BY id
It's never that simple, is it?
I just noticed you need the content
column as well.
This is a very common question in SQL: find the whole data for the row with some max value in a column per some group identifier. I heard that a lot during my career. Actually, it was one the questions I answered in my current job's technical interview.
It is, actually, so common that Stack Overflow community has created a single tag just to deal with questions like that: greatest-n-per-group.
Basically, you have two approaches to solve that problem:
Joining with simple group-identifier, max-value-in-group
Sub-query
In this approach, you first find the group-identifier, max-value-in-group
(already solved above) in a sub-query. Then you join your table to the sub-query with equality on both group-identifier
and max-value-in-group
:
SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
SELECT id, MAX(rev) rev
FROM YourTable
GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev
Left Joining with self, tweaking join conditions and filters
In this approach, you left join the table with itself. Equality goes in the group-identifier
. Then, 2 smart moves:
- The second join condition is having left side value less than right value
- When you do step 1, the row(s) that actually have the max value will have
NULL
in the right side (it's aLEFT JOIN
, remember?). Then, we filter the joined result, showing only the rows where the right side isNULL
.
So you end up with:
SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;
Conclusion
Both approaches bring the exact same result.
If you have two rows with max-value-in-group
for group-identifier
, both rows will be in the result in both approaches.
Both approaches are SQL ANSI compatible, thus, will work with your favorite RDBMS, regardless of its "flavor".
Both approaches are also performance friendly, however your mileage may vary (RDBMS, DB Structure, Indexes, etc.). So when you pick one approach over the other, benchmark. And make sure you pick the one which make most of sense to you.
MySQL - How to select rows with max value of a field
If you want to get ties, then you can do something like this:
select s.*
from scores s
where s.score = (select max(s2.score) from scores s2 where s2.level = s.level);
You could get one row per level by aggregating this:
select s.level, s.score, group_concat(s.user_id)
from scores s
where s.score = (select max(s2.score) from scores s2 where s2.level = s.level)
group by s.level, s.score;
This combines the users (if there is more than one) into a single field.
SQL select row where id=max(id)
try to add one more clause to your where with pd_id = (select max(pd_id) from ... )
SELECT pd_id, pd_title, pd_description, pd_colour,
pd_price,pd_large_image,pd_date,cat_sub_id_3,pd_new
FROM product
WHERE
cat_sub_id_1 = '".$cat_sub_id."'
AND cat_parent_id='".$cat_parent_id."'
AND pd_id = (select max(pd_id) from product)
GROUP BY pd_title
This query is not optimal but it would do the job.
SELECT row with MAX id from each GROUP BY (unique_id) and ORDER BY number
You can use your query as a subquery:
SELECT *
FROM table
WHERE id IN (SELECT MAX(id) AS id
FROM table
WHERE final=0 AND username='$username' AND active=1
GROUP BY unique_id)
ORDER BY order_number
or, if id
is not unique, use JOIN
:
SELECT t1.*
FROM table AS t1
JOIN (SELECT MAX(id) AS max_id, unique_id
FROM table
WHERE final=0 AND username='$username' AND active=1
GROUP BY unique_id
) AS t2 ON t1.unique_id = t2.unique_id AND t1.id = t2.unique_id
ORDER BY order_number
How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?
You are so close! All you need to do is select BOTH the home and its max date time, then join back to the topten
table on BOTH fields:
SELECT tt.*
FROM topten tt
INNER JOIN
(SELECT home, MAX(datetime) AS MaxDateTime
FROM topten
GROUP BY home) groupedtt
ON tt.home = groupedtt.home
AND tt.datetime = groupedtt.MaxDateTime
Related Topics
Where Value in Column Containing Comma Delimited Values
Identity Column Value Suddenly Jumps to 1001 in SQL Server
Is There Something Wrong With Joins That Don't Use the Join Keyword in SQL or MySQL
How to Find All Connected Subgraphs of an Undirected Graph
Nvarchar(Max) Still Being Truncated
Decision Between Storing Lookup Table Id'S or Pure Data
SQL Server Convert Varchar to Datetime
Sql: Repeat a Result Row Multiple Times, and Number the Rows
How to Turn Identity_Insert on and Off Using SQL Server 2008
Physical Vs. Logical (Hard Vs. Soft) Delete of Database Record
Find Most Frequent Value in SQL Column
Rounding Off to Two Decimal Places in Sql
Simulate Create Database If Not Exists For Postgresql
SQL Statement to Select All Rows from Previous Day
Fastest Way to Update 120 Million Records
What Is the Maximum Number of Columns in a Postgresql Select Query