CODEWITHSUNDEEP
sqloracle-database
Jestem_z_Kozanowa
Jestem_z_Kozanowa

Reputation: 637

Returning max values of query based on different column

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.

Upvotes: 0

Views: 115

Answers (2)

Justin
Justin

Reputation: 9724

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)

Upvotes: 1

schurik
schurik

Reputation: 7928

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
;

Upvotes: 1

Related Questions