CODEWITHSUNDEEP
sqloracle-databasedatetimesql-order-bygreatest-n-per-group
Ray
Ray

Reputation: 351

Oracle SQL query to extract data for a maximum date for rows contianing the same ID

I have a table, say Table1:

enter image description here

And, I am trying to extract data with the following conditions:

  1. select all entries in column A which are 2 and 5,
  2. All entries in column B which are 100
  3. All data which have the contract ID 15 in column C
  4. All dates, in Column D which are less than 31.02.2016, for example
  5. Finally, the row(s), which has (have) the maximum value in Column G

If I use the following code (except finding the maximum date in column G), it works fine:

Select * from Table1
where
A in (2 , 5)
and B = 100
and C = '15'
and D <= TO_DATE ('31.01.16', 'DD.MM.YY HH24:MI:SS')

and gives me the following result: enter image description here

Now, I want to find all those rows, which have the maximum date value in column G. If I use the following to find the row in this case corresponding to maximum date in G, the query runs and I get an empty table with just the column names:

Select * from Table1 t1
where
A in (2 , 5)
and B = 100
and C = '15'
and D <= TO_DATE ('31.01.16', 'DD.MM.YY HH24:MI:SS')
and G = (select MAX(G) from Table1 where G = t1.G)

The desired output is:

enter image description here

What am I doing wrong?

Upvotes: 0

Views: 124

Answers (3)

Olga Romantsova
Olga Romantsova

Reputation: 1081

If you need to get to find all those rows, which have the maximum date value in column Get, then you can use window function dense_rank(). Rows with the same values for the rank criteria will receive the same rank values:

--get all rows with num=1
Select * from
(
Select *, dense_rank() over (order by G desc) num
where
A in (2 , 5)
and B = 100
and C = '15'
and D <= TO_DATE ('31.01.16', 'DD.MM.YY HH24:MI:SS')
) X
Where num=1

Upvotes: 1

GMB
GMB

Reputation: 222712

If you just want one row, you can order by and limit:

Select * 
from Table1
where
    A in (2 , 5)
    and B = 100
    and C = 15
    and D <= date '2016-01-31'
order by d desc
fetch first 1 row only

If you want to allow top ties, then you can use fetch first 1 row with ties instead.

Notes

  • I used a literal date rather than to_date(): this is simpler to write and more efficient (note that your original format specification was wrong, as the string has no time portion)

  • it looks like column C is numeric, so I removed the single quotes around the literal value in the condition (you can change it back if the column is of a string datatype)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can use ORDER BY and FETCH:

select *
from Table1
where A in (2 ,5) and
      B = 100 and
      C = '15' and
      D <= date '2016-01-31'
order by g desc
fetch first 1 row only;

Note that I also simplified the syntax for the date constant.

If you want all rows in the event of ties, then use:

fetch first 1 row with ties;

Upvotes: 1

Related Questions