Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

SQL Question: Selecting earliest date time

I've a table that contains multiple transactions per any given day.

For each day of the month, I'm trying to find the earliest transaction possible. Below is my table data:

---------------------------------------------------------------
DATE                    |QUANTITY    |DIVISION    |  LOCATION
---------------------------------------------------------------
01-JAN-2008 12:42:01 AM |1234        |1           |  NORTH
01-JAN-2008 05:42:22 AM |1200        |1           |  NORTH
01-FEB-2008 01:42:01 AM |1123        |1           |  NORTH
01-FEB-2008 03:11:01 AM |985         |1           |  NORTH

So the question is, how do I select the EARLIEST transaction row for each given month,

This is my current SQL:

select       min(DATE),
             QUANTITY,
             DIVISION,
             LOCATION
             from TABLE
             group by QUANTITY, DIVISION, LOCATION

DATE field is a datatype "DATE" in Oracle database

My goal is to pretty much have the results shown as follows:

---------------------------------------------------------------
DATE                    |QUANTITY    |DIVISION    |  LOCATION
---------------------------------------------------------------
01-JAN-2008 12:42:01 AM |1234        |1           |  NORTH
01-FEB-2008 01:42:01 AM |1123        |1           |  NORTH

replied

Hi Eric

Use the following query:

select DATE,

QUANTITY,

DIVISION,

LOCATION

from TABLE

where DATE in

(

select min(DATE) from TABLE

group by to_char(DATE,'YYYY')

)

group by DATE,QUANTITY, DIVISION, LOCATION

Hope this helps.

Regards

Nikhil

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question