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