CODEWITHSUNDEEP
sqloracle-database
sf_tristanb
sf_tristanb

Reputation: 8855

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
);

Upvotes: 2

Views: 205

Answers (5)

2red13
2red13

Reputation: 11227

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

Upvotes: 1

René Nyffenegger
René Nyffenegger

Reputation: 40533

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

Upvotes: 5

Joe Stefanelli
Joe Stefanelli

Reputation: 135848

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

Upvotes: 2

Haeflinger
Haeflinger

Reputation: 465

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.

Upvotes: 1

Adam Robinson
Adam Robinson

Reputation: 185663

select
    article_id,
    max(version) as Version

from article

group by article_id

Upvotes: 2

Related Questions