CODEWITHSUNDEEP
sqloracle-database
Sisse
Sisse

Reputation: 291

How to choose a max value of a column

My problem seems simple, but I just can't figure it out.

This is what I have:

TIME           MAXVERSION
13-12-31          1
13-12-31          2
14-12-31          1
14-12-31          2
14-12-31          3
15-12-31          1
15-12-31          2
15-12-31          3
16-12-31          1

and this is what I would like to have:

TIME           MAXVERSION
13-12-31          2
14-12-31          3
15-12-31          3
16-12-31          1

I have tried this:

select referencetid as time max(version) over (partition by referencetid order by version desc) as maxversion
from D101200.PSD_BEFOLKNING_REFERENCE
where to_char(referencetid, 'mm-dd') = '12-31'
and to_char(referencetid, 'yy-mm-dd') between ('07-12-31') and ('16-12-31')
order by referencetid;

which gives me sort of whant I want, but not completely:

TIME           MAXVERSION
13-12-31          2
13-12-31          2
14-12-31          3
14-12-31          3
14-12-31          3
15-12-31          3
15-12-31          3
15-12-31          3
16-12-31          1

This gives me the right versions, but the dataset has not reduced in size, which is what I would like.

Any help is greately appreciable!

Upvotes: 0

Views: 79

Answers (5)

Carmine Tambascia
Carmine Tambascia

Reputation: 1928

As already answered by many, with some consideration, you ware looking for the group by, please have a look at this or other examples here to learn how to use it, this a nice service to try queries sqlfiddle 

select 
    referencetid as Time,
    max(version) as maxversion

from D101200.PSD_BEFOLKNING_REFERENCE

where to_char(referencetid, 'mm-dd') = '12-31'
   and to_char(referencetid, 'yy-mm-dd') between ('07-12-31') and ('16-12-
 31')
group by referencetid 
order by referencetid;

Upvotes: 0

MT0
MT0

Reputation: 167962

Seems like lots of questions today asking about 31st December for the last N years

SELECT referencetid as time,
       max(version) as maxversion
FROM   D101200.PSD_BEFOLKNING_REFERENCE
WHERE  referencetid IN (
         DATE '2016-12-31',
         DATE '2015-12-31',
         DATE '2014-12-31',
         DATE '2013-12-31',
         DATE '2012-12-31',
         DATE '2011-12-31',
         DATE '2010-12-31',
         DATE '2009-12-31',
         DATE '2008-12-31',
         DATE '2007-12-31'
       )
GROUP BY referencetid
ORDER BY referencetid;

Using TO_CHAR (or another function) on the date column as part of the filter will prevent the query from using any indexes on that column (you would need a function-based index).

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You need to fix your date arithmetic! Always use ISO standard YYYY-MM-DD (or YYYYMMDD) formats in queries. Don't do date comparisons using strings.

The answer to your question is then a simple group by:

select referencetid as time, max(version)
from D101200.PSD_BEFOLKNING_REFERENCE
where to_char(referencetid, 'mm-dd') = '12-31' and
      referencetid between date '2007-12-31' and date '2016-12-31'
group by referencetid
order by referencetid;

Assuming you have an index on referencetid, I would suggest you just explicitly list the values:

select referencetid as time, max(version)
from D101200.PSD_BEFOLKNING_REFERENCE
where referencetid in (date '2007-12-31', date '2008-12-31', date '2009-12-31',
                       date '2010-12-31', date '2011-12-31', date '2012-12-31',
                       date '2013-12-31', date '2014-12-31', date '2015-12-31',
                       date '2016-12-31')
group by referencetid
order by referencetid;

This makes the query easier to optimize using an index.

Upvotes: 3

Hakan Hifzioglu
Hakan Hifzioglu

Reputation: 35

select 
referencetid as time, 
max(version)as maxversion
from D101200.PSD_BEFOLKNING_REFERENCE
where to_char(referencetid, 'mm-dd') = '12-31'
and to_char(referencetid, 'yy-mm-dd') between ('07-12-31') and ('16-12-31')
group by referencetid 
order by referencetid

you can use this code

Upvotes: 0

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186668

Are you looking for GROUP BY?

     select Time,
            Max(MaxVersion) as MaxVersion  
       from MyTable
   group by Time
   order by Time

Upvotes: 3

Related Questions