Solved

Selecting records by a maximum value

Posted on 2008-10-09
3
765 Views
Last Modified: 2013-12-07
Hi there,

I am looking for a way to alter my select query to select only the highest values from the revision collumn.

SELECT c.check_id ,  c.doc_id,  c.revision, o.ORDER_NUM,d.doc_line FROM docsChecks c INNER JOIN docsDocuments d ON d.doc_id = c.doc_id INNER JOIN OPORDHD o ON c.order_no = o.ORDER_NUM

I sort of achieved whta i wanted by adding ' WHERE c.revision = (SELECT MAX(c.revision) from c)' but this only returns the single row with the highest revision number, I wish for multiple rows with the highest numbers. I have included before and after exmaples of the result sets I am wishing to acheive in the code snippets.
CHECK_ID  DOC_ID  REVISION  ORDER_NUM  	DOC_LINE

100 	  1 	  0 	    AM10000 	1

101 	  1 	  1 	    AM10000 	1

102 	  1 	  2 	    AM10000 	1

103 	  2 	  0 	    AM10000 	2

104 	  2 	  1 	    AM10000 	2

105 	  2 	  2 	    AM10000 	2

106 	  3 	  0 	    AM10000 	3
 

CHECK_ID  DOC_ID  REVISION  ORDER_NUM  	DOC_LINE

102 	  1 	  2 	    AM10000 	1

105 	  2 	  2 	    AM10000 	2

106 	  3 	  0 	    AM10000 	3

Open in new window

0
Comment
Question by:wellso
  • 2
3 Comments
 
LVL 5

Accepted Solution

by:
Cvijo123 earned 500 total points
Comment Utility
Something like this ?


SELECT 

	max(c.check_id) as check_id,

	c.doc_id,

	max(c.revision) as revision,

	max(o.ORDER_NUM) as ORDER_NUM,

	max(d.doc_line) as doc_line

FROM docsChecks c

	INNER JOIN docsDocuments d

			ON d.doc_id = c.doc_id

	INNER JOIN OPORDHD o 

		ON c.order_no = o.ORDER_NUM

group by 

 	c.doc_id

Open in new window

0
 
LVL 7

Expert Comment

by:fluglash
Comment Utility
add order by clause to your query and add the outer query to limit number of rows
select * 

from (<i>Your_SQL_Query_Here</i>

         order by conum desc)

where  rownum < <i>Number_of_records_needed</i>

Open in new window

0
 
LVL 7

Expert Comment

by:fluglash
Comment Utility
select *
from (Your_SQL_Query_Here
         order by conum desc)
where  rownum < Number_of_records_needed
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

743 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

11 Experts available now in Live!

Get 1:1 Help Now