Selecting records by a maximum value

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

LVL 2
wellsoAsked:
Who is Participating?
 
Cvijo123Commented:
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
 
fluglashCommented:
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
 
fluglashCommented:
select *
from (Your_SQL_Query_Here
         order by conum desc)
where  rownum < Number_of_records_needed
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.

All Courses

From novice to tech pro — start learning today.