Solved

How can I select data with the largest version number?

Posted on 2008-10-03
15
331 Views
Last Modified: 2013-12-19
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
Comment
Question by:Rassac
  • 5
  • 4
  • 4
  • +2
15 Comments
 
LVL 11

Expert Comment

by:aaronakin
ID: 22634134
select a.Title, MAX(a.version)
  from t_ABC a
  group by a.Title
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 22634145
select title from (
select title from t_abc
order by version desc
)
where rownum = 1
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 22634165
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
 

Author Comment

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

thanks.
0
 
LVL 11

Expert Comment

by:aaronakin
ID: 22634210
SELECT Title
  FROM
    (select a.Title, MAX(a.version)
  from t_ABC a
  group by a.Title
    )b
0
 
LVL 11

Expert Comment

by:aaronakin
ID: 22634227
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
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 22634241
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22634249
"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
 
LVL 73

Expert Comment

by:sdstuber
ID: 22634251
If you only want the title and no other columns, then who cares about the version...


select distinct title from t_abc
0
 
LVL 11

Expert Comment

by:aaronakin
ID: 22634270
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22634275
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22634290
Oracle 8.x in zones it looks like, but yes please confirm.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 22634308
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22634317
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 22634412
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Very interesting Access query problem. 13 70
Export table into csv file in oracle 10 43
xpath sql query 2008 8 42
Access 2010 Query Syntax 5 22
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now