SQL, problem with OrderBy / GroupBy

In my table I have two fields among others : article_id and version

Example:

   article_id | version
   -----------|----------   
        5     |   1
        5     |   2
        6     |   1

What I want to do is to retrieve the latest version for each article id. (In my example I want to retrieve the article 5 version 2 object and article 6 and version 1 object).

The problem is that mysql is doing the group by instead of the order by so it returns to me the FIRST version of each article, but开发者_开发知识库 I want the opposite.

Do you have an idea please ?

Solution

select *
from article r
where r.version=(
 select max(version) 
 from article r2 
 where r2.article_id = r.article_id
);

Your question is a bit vague, but I believe is is more or less this what you want:

select * from (
   select
      <table>.*,
      row_number() over (partition by article_id order by version desc) r  
   from 
      <table>
)
where r = 1

The query returns one record for each (distinct) article_id. This record is the one with the highest version for the article_id returned.

So, together with a "test case", this can be seen in action:

create table tq84_resorces (
  id           number primary key,
  article_id   number not null,
  version      number not null
);

insert into tq84_resorces values (50, 5, 1);
insert into tq84_resorces values (60, 5, 2);
insert into tq84_resorces values (70, 6, 1);


select * from (
   select
      tq84_resorces.*,
      row_number() over (partition by article_id order by version desc) r  
   from 
      tq84_resorces
)
where r = 1

which returns:

        ID ARTICLE_ID    VERSION          R
---------- ---------- ---------- ----------
        60          5          2          1
        70          6          1          1

select
    article_id,
    max(version) as Version

from article

group by article_id

select yt.id, yt.article_id, yt.version
    from (select article_id, max(version) as max_version
              from YourTable
              group by article_id) t
        inner join YourTable yt
            on t.article_id = yt.article_id
                and t.max_version = yt.version

Will this work:

select articleId, (select top 1 version 
                  from article ar2 
                  where ar2.articleId = ar.articleId
                  order by version desc) as version
from article ar
group by ar.articlId

Works in sql server 2005, did not test in mysql.

First you should change the version column into integer (maybe with a prefix column if you strongly need the String), than you are able to

Select MAX(version) 
...
Group By article_id