Oracle query needed

Luuk wrote:
> Dutchie schreef:
>> Hi,
>>
>> I have difficulty creating a query, perhaps you can help me...
>>
>> I have an Oracle database with the following columns in a table:
>>
>> franchiser, store, cashregister, receiptdate
>>
>> Now I want to select the first receiptdate for every unique cashregister.
>> Can you tell me what query does this ?
>>
>>
>
> you want things for every unique cashregister, so you need to use the
> 'GROUP BY cashregister'
>
> and you need the 1st (or the smallest) receiptdate. This can be done
> with 'MIN(receiptdate)'
>
> so your query is something like:
> SELECT MIN(receiptdate), cashregister FROM table GROUP BY cashregister;
>
> but, its not a difficult query, so i'm not sue if this is right....

hi, and thanks
the cashregister value is not unique
below is some sample data, hope you can find a query for this.
i'm still practicing with sql, but could not tackle this one yet
(therefore i have a read-only account )

franchiser, store, cashregister, receiptdate
1, 1, 1, YYYY-MM-DD HH:MM:SS
1, 2, 1, YYYY-MM-DD HH:MM:SS
1, 2, 2, YYYY-MM-DD HH:MM:SS
1, 3, 1, YYYY-MM-DD HH:MM:SS
2, 1, 1, YYYY-MM-DD HH:MM:SS
3, 1, 1, YYYY-MM-DD HH:MM:SS
3, 1, 2, YYYY-MM-DD HH:MM:SS
etc...

Dutchie schreef:
> Luuk wrote:
>> Dutchie schreef:
>>> Hi,
>>>
>>> I have difficulty creating a query, perhaps you can help me...
>>>
>>> I have an Oracle database with the following columns in a table:
>>>
>>> franchiser, store, cashregister, receiptdate
>>>
>>> Now I want to select the first receiptdate for every unique
>>> cashregister.
>>> Can you tell me what query does this ?
>>>
>>>
>>
>> you want things for every unique cashregister, so you need to use the
>> 'GROUP BY cashregister'
>>
>> and you need the 1st (or the smallest) receiptdate. This can be done
>> with 'MIN(receiptdate)'
>>
>> so your query is something like:
>> SELECT MIN(receiptdate), cashregister FROM table GROUP BY cashregister;
>>
>> but, its not a difficult query, so i'm not sue if this is right....
>
> hi, and thanks
> the cashregister value is not unique
> below is some sample data, hope you can find a query for this.
> i'm still practicing with sql, but could not tackle this one yet
> (therefore i have a read-only account )
>
> franchiser, store, cashregister, receiptdate
> 1, 1, 1, YYYY-MM-DD HH:MM:SS
> 1, 2, 1, YYYY-MM-DD HH:MM:SS
> 1, 2, 2, YYYY-MM-DD HH:MM:SS
> 1, 3, 1, YYYY-MM-DD HH:MM:SS
> 2, 1, 1, YYYY-MM-DD HH:MM:SS
> 3, 1, 1, YYYY-MM-DD HH:MM:SS
> 3, 1, 2, YYYY-MM-DD HH:MM:SS
> etc...

so, did you at least TRY my query ?

with this sample data it should give:
YYYY-MM-DD, 1
YYYY-MM-DD, 2

Luuk wrote:
> Dutchie schreef:
>> Luuk wrote:
>>> Dutchie schreef:
>>>> Hi,
>>>>
>>>> I have difficulty creating a query, perhaps you can help me...
>>>>
>>>> I have an Oracle database with the following columns in a table:
>>>>
>>>> franchiser, store, cashregister, receiptdate
>>>>
>>>> Now I want to select the first receiptdate for every unique
>>>> cashregister.
>>>> Can you tell me what query does this ?
>>>>
>>>>
>>>
>>> you want things for every unique cashregister, so you need to use the
>>> 'GROUP BY cashregister'
>>>
>>> and you need the 1st (or the smallest) receiptdate. This can be done
>>> with 'MIN(receiptdate)'
>>>
>>> so your query is something like:
>>> SELECT MIN(receiptdate), cashregister FROM table GROUP BY cashregister;
>>>
>>> but, its not a difficult query, so i'm not sue if this is right....
>>
>> hi, and thanks
>> the cashregister value is not unique
>> below is some sample data, hope you can find a query for this.
>> i'm still practicing with sql, but could not tackle this one yet
>> (therefore i have a read-only account )
>>
>> franchiser, store, cashregister, receiptdate
>> 1, 1, 1, YYYY-MM-DD HH:MM:SS
>> 1, 2, 1, YYYY-MM-DD HH:MM:SS
>> 1, 2, 2, YYYY-MM-DD HH:MM:SS
>> 1, 3, 1, YYYY-MM-DD HH:MM:SS
>> 2, 1, 1, YYYY-MM-DD HH:MM:SS
>> 3, 1, 1, YYYY-MM-DD HH:MM:SS
>> 3, 1, 2, YYYY-MM-DD HH:MM:SS
>> etc...
>
> so, did you at least TRY my query ?
>
> with this sample data it should give:
> YYYY-MM-DD, 1
> YYYY-MM-DD, 2

Actually the database was inaccessible due to a backup
But hey, I changed the query to:
SELECT MIN(receiptdate), franchiser, store, cashregister FROM table
GROUP BY franchiser, store, cashregister;
and it works.
That easy !

I thought it would simply select all combinations of
franchiser-store-cashregister and then only show the one record that
was the oldest of that set.
But I am misunderstood the MIN() function
Thanks for the help !