• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

How can I select data with the largest version number?

Hi I have this code here but something is wrong:

select a.Title
  from t_ABC a
 where a.version = Max(a.version);

The table contains multiple entries with the same title, having a different version number. Is it possible to select the ones with the greatest version for each Title?

Thanks.
0
Rassac
Asked:
Rassac
  • 5
  • 4
  • 4
  • +2
1 Solution
 
aaronakinCommented:
select a.Title, MAX(a.version)
  from t_ABC a
  group by a.Title
0
 
sdstuberCommented:
select title from (
select title from t_abc
order by version desc
)
where rownum = 1
0
 
sdstuberCommented:
ooops, ignore my previous post, that was to get the title for highest overall version
not version per title.

select * from
(
select t_abc.*, row_number() over(partition by title order by version desc) rn from t_abc
)
where rn = 1
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
RassacAuthor Commented:
The problem is that I am using a nested query and cannot have two columns. Is there any other way please?

thanks.
0
 
aaronakinCommented:
SELECT Title
  FROM
    (select a.Title, MAX(a.version)
  from t_ABC a
  group by a.Title
    )b
0
 
aaronakinCommented:
If you want just the version number...

SELECT Version
  FROM
    (select a.Title, MAX(a.version) AS Version
  from t_ABC a
  group by a.Title
    )b
0
 
Kevin CrossChief Technology OfficerCommented:
I am not an Oracle guru, so forgive me -- hopefully you get the idea.
-- use IN statement
select a.Title
  from t_ABC a
 where a.version IN (select Max(version) from t_ABC b where b.Title = a.Title);
 
-- use INNER join
select a.Title
  from t_ABC a INNER JOIN 
(select Title, Max(version) As version from t_ABC) b 
ON b.Title = a.Title AND b.version = a.version;

Open in new window

0
 
BrandonGalderisiCommented:
"The problem is that I am using a nested query and cannot have two columns. Is there any other way please?"

You can't do it with a nested query.  You have to use a derived table.

ex:

select a.Title
  from t_ABC a
  join (select title,max(version) maxVersion from t_ABC group by Title)b
on a.title=b.title
and a.version = b.maxversion

0
 
sdstuberCommented:
If you only want the title and no other columns, then who cares about the version...


select distinct title from t_abc
0
 
aaronakinCommented:
Maybe it would be better to provide us with the fully query so we know the context of how it's being used.

Also, is this for SQL Server or Oracle?  What version?
0
 
Kevin CrossChief Technology OfficerCommented:
That way, you can have other columns in the select portion as well.  Since this is a version, I would suspect only one row can have a version, so either method should work.
0
 
Kevin CrossChief Technology OfficerCommented:
Oracle 8.x in zones it looks like, but yes please confirm.
0
 
sdstuberCommented:
aaronakin,  I believe it's Oracle and version 8, based on the zones the query was posted to.

Rassac,  if none of the above options work please post some sample data and expected output
there are multiple ways to interpret what it is you are requesting.
0
 
Kevin CrossChief Technology OfficerCommented:
Rassac, for my second example (http:#22634241), I was trying to suggest same as Brandon in comment following mine.  I left out the Group By Title, so if you liked second method better look at Brandon's comment - http:#22634249.
0
 
sdstuberCommented:
can version be NULL?

if not, then the query in 22634249 is just a complicated (slower) way to write

select distinct title from t_abc

if version can be NULL, then how do you want those nulls processed?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now