mysql - SQL select only rows with max value on a column -


i have table documents (simplified version here):

+------+-------+--------------------------------------+ | id   | rev   | content                              | +------+-------+--------------------------------------+ | 1    | 1     | ...                                  | | 2    | 1     | ...                                  | | 1    | 2     | ...                                  | | 1    | 3     | ...                                  | +------+-------+--------------------------------------+

how select 1 row per id , greatest rev?
above data, result should contain 2 rows: [1, 3, ...] , [2, 1, ..]. i'm using mysql.

currently use checks in while loop detect , over-write old revs resultset. method achieve result? isn't there sql solution?

update
answers suggest, there is sql solution, , here sqlfiddle demo.

update 2
noticed after adding above sqlfiddle, rate @ question upvoted has surpassed upvote rate of answers. has not been intention! fiddle is based on answers, accepted answer.

at first glance...

all need group by clause max aggregate function:

select id, max(rev) yourtable group id

it's never simple, it?

i noticed need content column well.

this common question in sql: find whole data row max value in column per group identifier. heard lot during career. actually, 1 questions answered in current job's technical interview.

it is, actually, common stackoverflow community has created single tag deal questions that: .

basically, have 2 approaches solve problem:

joining simple group-identifier, max-value-in-group sub-query

in approach, first find group-identifier, max-value-in-group (already solved above) in sub-query. join table sub-query equality on both group-identifier , max-value-in-group:

select a.id, a.rev, a.contents yourtable inner join (     select id, max(rev) rev     yourtable     group id ) b on a.id = b.id , a.rev = b.rev

left joining self, tweaking join conditions , filters

in approach, left join table itself. equality, of course, goes in group-identifier. then, 2 smart moves:

  1. the second join condition having left side value less right value
  2. when step 1, row(s) have max value have null in right side (it's left join, remember?). then, filter joined result, showing rows right side null.

so end with:

select a.* yourtable left outer join yourtable b     on a.id = b.id , a.rev < b.rev b.id null;

conclusion

both approaches bring exact same result.

if have 2 rows max-value-in-group group-identifier, both rows in result in both approaches.

both approaches sql ansi compatible, thus, work favorite rdbms, regardless of "flavor".

both approaches performance friendly, mileage may vary (rdbms, db structure, indexes, etc.). when pick 1 approach on other, benchmark. , make sure pick 1 make of sense you.


Comments

Post a Comment

Popular posts from this blog

PHP and MySQL WP -

i trying accomplish 2 things here code. writing php directly wp page using insert php plugin. 1) query mysql database , return results 1 column 4 columns. 2) make each result own hyperlink directs user new wp page runs new query on page. i able query show results , turn results dynamic hyperlink, cannot formatting down. right returns result 1 column. here have: [insert_php] $servername = "localhost"; $username = "login"; //edited privacy $password = "password"; //edited privacy $database = "database"; //edited privacy // create connection $conn = new mysqli($servername, $username, $password, $database); // check connection if ($conn->connect_error) { die("connection failed: " . $conn->connect_error); } echo "connected successfully"; //get results database $result = mysqli_query($conn,"select mine mines order mine"); $all_property = array(); //declare array saving property //showing proper...
Read more

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

i'm implementing inapp billing in android app. works well, however, i'm trying decouple broadcast receiver activity manifest. suggestion in android's trivialdrive sample: // important: dynamically register broadcast messages updated purchases. // register receiver here instead of <receiver> in manifest // because call getpurchases() @ startup, therefore can ignore // broadcasts sent while app isn't running. // note: registering listener in activity bad idea, done here // because sample. regardless, receiver must registered after // iabhelper setup, before first call getpurchases(). currently there's class extends broadcastreceiver : public class iabbroadcastreceiver extends broadcastreceiver { /** * intent action receiver should filter for. */ public static final string action = "com.android.vending.billing.purchases_updated"; private final iabbroadcastlistener mlistener; public iabbroadcastreceiver(iabbroadcastlistener listener) { m...
Read more

go - golang pprof for c library code -

i have been trying profile golang application pprof, seems details of c code(linked static library) missed in output result. flame graph from frame graph can see c code time consuming reported within "runtime._externalcode", no details @ all. , sure static library compiled debug enable. so there way make pprof details(like stack trace) of 3rd party c code? or there other golang profiling tool this?
Read more