Date query in Sql

Question

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.

Not sure what you mean by 'the minimum tran_date for every a,b,c,d in the table'? Could you give some example data and what the output should be? Also what flavour of SQL you are using would help. — Cam Jackson, Jun 23 '11 at 05:19

score 1 · Answer 1 · answered Jun 23 '11 at 05:18

1

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

answered Jun 23 '11 at 05:18

Adriaan Stander

162,879
31
289
284

score 1 · Answer 2 · answered Jun 23 '11 at 08:44

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)

score 0 · Answer 3 · answered Jun 23 '11 at 05:18

0

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

answered Jun 23 '11 at 05:18

Lasse Edsvik

9,070
16
73
109

Date query in Sql

3 Answers3