CODEWITHSUNDEEP
sqloracle-databaseora-01427
Coder
Coder

Reputation: 97

Date query in Sql

I have a table where there are 5 columns let say a,b,c,d,tran_date.

I want to generate a query to find out the minimum tran_date for every a,b,c,d in the table.

Any help how this can be done.

EDIT:The result of this query needs to be subtracted from a single date which is a result from the query:

  select ref_date from ref_table

How this can be done because the error ORA-01427: single row subquery returns more than one row.

Upvotes: 0

Views: 135

Answers (3)

Alex Poole
Alex Poole

Reputation: 191570

Expanding on @astander's answer to include the additional subtratction requirement:

select a, b, c, d,
    min(tran_date) as min_tran_date,
    min(tran_date) - (select ref_date from ref_table) as diff
from my_table
group by a, b, c, d;

Note that the diff may be positive or negataive, depending on whether the ref_dateis before all tran_date values, after them all, or somewhere in the middle.

If you're only interested in certain diff values - say, where the minimnum tran_date is after the ref_date - you can add a having clause to filter the results; in this case:

having min(tran_date) - (select ref_date from ref_table) > 0

or perhaps to be clearer:

having min(tran_date) > (select ref_date from ref_table)

Upvotes: 1

Lasse Edsvik
Lasse Edsvik

Reputation: 9298

SELECT A, Min(SomeDate) FROM Foo GROUP BY A

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166576

If I understand you correctly, try something like

SELECT  a,
        b,
        c,
        d,
        MIN(tran_date) MIN_tran_date
FROM    Table
GROUP BY    a,
            b,
            c,
            d

Upvotes: 1

Related Questions