 |
|
|
|
Next: Merge Agent
|
| Author |
Message |
External
Since: Aug 23, 2005
Posts: 4
|
(Msg. 1) Posted: Wed Dec 21, 2005 1:48 am
Post subject: Find the first record, for every day
Archived from groups: comp>databases>oracle>misc (more info?)
|
|
|
Hi !
I have a tricky problem, and i can't solve it.
I have the following table T1, with the following fields and records:
Date_ Status
---------- -------
2005-12-01 2
2005-12-01 1
2005-12-17 1
2005-12-18 2
2005-12-18 10
I want to generate on output, that gives me the "first" record every
day. The output should look like this:
Date_ Status
---------- -------
2005-12-01 2
2005-12-17 1
2005-12-18 2
This appears to be a very simple problem, but i can't find the
solution. Can anybody give me a hint ? (I have tried with rownum, but
that didn't work).
Regards,
Ole |
|
| Back to top |
|
 | |
External
Since: Jan 22, 2004
Posts: 91
|
(Msg. 2) Posted: Wed Dec 21, 2005 2:16 am
Post subject: Re: Find the first record, for every day [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Assuming you have hh24:mm information in your date columns
select date_, status
from t1 x
where date_ =
(
select min(date_) from t1 y where trunc(y.date_) = trunc(x.date_)
)
needless to say you would need a function based index on the date
column or the statement will perform horribly, and won't scale.
Hth
--
Sybrand Bakker
Senior Oracle DBA |
|
| Back to top |
|
| |
External
Since: Aug 23, 2005
Posts: 4
|
(Msg. 3) Posted: Wed Dec 21, 2005 2:43 am
Post subject: Re: Find the first record, for every day [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
There is no difference. The to dates are the same. (No difference in
time).
What you see is a result of a complex query. If the date is confusing,
we could change the case to this:
Name Status Rownum
------ ------- ------
OLE 2 1
OLE 1 2
PETER 1 3
JOHN 2 4
JOHN 10 5
And then the output should look like this:
Name Status Rownum
------ ------- ------
OLE 2 1
PETER 1 3
JOHN 2 4
Regards,
Ole >> Stay informed about: Find the first record, for every day |
|
| Back to top |
|
| |
External
Since: Dec 06, 2004
Posts: 141
|
(Msg. 4) Posted: Wed Dec 21, 2005 4:44 am
Post subject: Re: Find the first record, for every day [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
wrote:
> There is no difference. The to dates are the same. (No difference in
> time).
>
> What you see is a result of a complex query. If the date is confusing,
> we could change the case to this:
>
> Name Status Rownum
> ------ ------- ------
> OLE 2 1
> OLE 1 2
> PETER 1 3
> JOHN 2 4
> JOHN 10 5
>
> And then the output should look like this:
>
> Name Status Rownum
> ------ ------- ------
> OLE 2 1
> PETER 1 3
> JOHN 2 4
>
> Regards,
> Ole
Then dump the data to af ile where theer is a "first record" because
there is not such concept in a relational database. ROWNUM assigns
values are Oracle retrieves them from the DB. There is no guarantee
they will be retrieved in the same order nexct time you run the query.
So can you live with it returning a different result on the next run?
That's why we ask what your REAL crriteria is for preferring the final
result include OLE 2 versus OLE 1
Stop treating the result set like a file.
Ed >> Stay informed about: Find the first record, for every day |
|
| Back to top |
|
| |
External
Since: Jun 30, 2005
Posts: 24
|
(Msg. 5) Posted: Wed Dec 21, 2005 5:55 am
Post subject: Re: Find the first record, for every day [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On 21.12.2005 10:48 wrote:
> Hi !
>
> I have a tricky problem, and i can't solve it.
>
> I have the following table T1, with the following fields and records:
>
> Date_ Status
> ---------- -------
> 2005-12-01 2
> 2005-12-01 1
> 2005-12-17 1
> 2005-12-18 2
> 2005-12-18 10
>
> I want to generate on output, that gives me the "first" record every
> day. The output should look like this:
>
>
> Date_ Status
> ---------- -------
> 2005-12-01 2
> 2005-12-17 1
> 2005-12-18 2
>
>
> This appears to be a very simple problem, but i can't find the
> solution. Can anybody give me a hint ? (I have tried with rownum, but
> that didn't work).
>
The question is: what is the difference between the date 2005-12-01 with
Status 2 and the date for 2005-12-01 with the status 1. How do you
distinguish between the two? Which one is the "first", you don't seem to
have an additional sorting criteria that could be used to find the
"first" of a given day.
Thomas
--
It's not a RootKit - it's a Sony >> Stay informed about: Find the first record, for every day |
|
| Back to top |
|
| |
External
Since: Sep 12, 2005
Posts: 2
|
(Msg. 6) Posted: Wed Dec 21, 2005 5:55 am
Post subject: Re: Find the first record, for every day [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
wrote in message
> Hi !
>
> I have a tricky problem, and i can't solve it.
>
> I have the following table T1, with the following fields and records:
>
> Date_ Status
> ---------- -------
> 2005-12-01 2
> 2005-12-01 1
> 2005-12-17 1
> 2005-12-18 2
> 2005-12-18 10
>
> I want to generate on output, that gives me the "first" record every
> day. The output should look like this:
>
>
> Date_ Status
> ---------- -------
> 2005-12-01 2
> 2005-12-17 1
> 2005-12-18 2
>
>
> This appears to be a very simple problem, but i can't find the
> solution. Can anybody give me a hint ? (I have tried with rownum, but
> that didn't work).
>
>
> Regards,
> Ole
>
if i understood,
try to use analytic function,
else explain Status a bit more..
select Date_, Status, min(SEQ) from
(
select Date_, Status, Row_Number() over (partition by Date_, Status order by
Date_, Status) Seq
from Table
)
group by Date_, Status >> Stay informed about: Find the first record, for every day |
|
| Back to top |
|
| |
External
Since: Sep 12, 2005
Posts: 2
|
(Msg. 7) Posted: Wed Dec 21, 2005 6:55 am
Post subject: Re: Find the first record, for every day [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
"T" wrote in message
>
> wrote in message
>
>> Hi !
>>
>> I have a tricky problem, and i can't solve it.
>>
>> I have the following table T1, with the following fields and records:
>>
>> Date_ Status
>> ---------- -------
>> 2005-12-01 2
>> 2005-12-01 1
>> 2005-12-17 1
>> 2005-12-18 2
>> 2005-12-18 10
>>
>> I want to generate on output, that gives me the "first" record every
>> day. The output should look like this:
>>
>>
>> Date_ Status
>> ---------- -------
>> 2005-12-01 2
>> 2005-12-17 1
>> 2005-12-18 2
>>
>>
>> This appears to be a very simple problem, but i can't find the
>> solution. Can anybody give me a hint ? (I have tried with rownum, but
>> that didn't work).
>>
>>
>> Regards,
>> Ole
>>
>
>
>
> if i understood,
> try to use analytic function,
> else explain Status a bit more..
>
>
>
> select Date_, Status, min(SEQ) from
> (
> select Date_, Status, Row_Number() over (partition by Date_, Status order
> by Date_, Status) Seq
> from Table
> )
> group by Date_, Status
>
Date must have date/time information and
sorry SEQ must be (partition by Date_ order by Date_) Seq >> Stay informed about: Find the first record, for every day |
|
| Back to top |
|
| |
| Related Topics: | Record and Raw types - HI I've got a simple structure like the following: DECLARE TYPE LOGON_PACKET IS RECORD( szUsername CHAR(256), szPwdEnc CHAR(256)); logonPKT LOGON_PACKET; and when I call ret_val := utl_tcp.write_raw(c,logonPKT); I get type mismatch error..
HowTo find out used (useful) and unused (usesless) indexes? - Hi, Is there a way to find out what indexes are used(useful)/unused(useless) while the last few days/weeks? If possible so tell me a way via data dictionary without using any external tools. Andreas Mosmann -- wenn email, dann AndreasMosmann <be...
Copy a record from 1-table to another - Greetings... This is an extremely simplified version of the actual problem, just to make it easier. I have 2-tables: R(a) and S(a,b) I want to do the following (which doesn't work), and I don't know what I need to do to make it work. This is on a..
problems with picking up the latest record.. - Hi, I have a table in my database which has a date column...I need to pick up a resultset based on a where clause, identify the latest record based on the date column and then go ahead and pick up other values of the latest record. Here is the rough..
Help!! Trigger for delete a record after update - Hi I need to delete relative record from table B when the table A is updated. Table C is joing the tables A and B. Thanks Jsutha |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
