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

1

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)

answered Aug 28, 2013 at 10:23

Justin

9,7336 gold badges38 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

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

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related