Returning max values of query based on different column

Question

My table stores various versions of the few documents.

------------------------------- | id | doc_type | download | ------------------------------- | 1 | type1 | file | ------------------------------- | 2 | type2 | file | ------------------------------- | 3 | type3 | file | ------------------------------- | 4 | type1 | file | -------------------------------

The table stores different versions of the same type of documents. I need to build a query which will return distinct types of doc_type having max(id) - which is the newest version of the file. Number of doc_types is not limited and is dynamic. My query so far:

select max(id) from template_table where doc_type in (select distinct doct_type from template_table);

This returns only one largest result. If I could sort results by id ASC and the limit result to 4 largest but it will not guarantee that it will return distinct doc_types. Also number of document types in DB might be changing from 4 it needs to count how many there is.

select * from template_table order by id limit 4;

Thanks for any help.

Justin · Accepted Answer · 2013-08-28 10:23:06Z

Query:

SELECT t1.id, t1.doc_type, t1.download FROM template_table t1 JOIN (SELECT MAX(id) AS id, doc_typ FROM template_table GROUP BY doc_type) t2 ON t2.doc_type = t1.doc_type AND t2.id = t1.id

OR:

SELECT t1.id, t1.doc_type, t1.download FROM template_table t1 WHERE t1.id = (SELECT MAX(t2.id) FROM template_table t2 WHERE t2.doc_type = t1.doc_type)

schurik · Accepted Answer · 2013-08-28 10:14:38Z

1

you can use GROUP BY to get the desired result

select doc_type , max(id) AS last_id , max(download) KEEP (DENSE_RANK FIRST order by id desc) AS last_download from template_table group by doc_type ;

answered Aug 28, 2013 at 10:14

schurik

7,9582 gold badges26 silver badges30 bronze badges

2 Comments

schurik Over a year ago

KEEP DENSE_RANK FIRST returns the download with the greatest ID per doc_type. its supported by ORACLE.

Jestem_z_Kozanowa Over a year ago

Thanks for your answer it is very interesting. I have not come across this command before.

Collectives™ on Stack Overflow

Returning max values of query based on different column

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related