| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
08-10-10, 17:52
|
|
Registered User
|
|
Join Date: Jun 2010
Posts: 17
|
|
|
First occurence count
|
|
How can I flag the first occurence of data??
Eg.
I have follwing table.
INVOICE_ID SALE_ID OBLIGOR_ID
1 123 [NULL]
2 693 12
2 254 20
3 789 96
3 789 59
3 789 26
Now there is one more column call invoice_count. It should display 1 for first occurence of every invoice_id and 0 for repeated occurence.
i.e
The result should look like this.
INVOICE_ID SALE_ID OBLIGOR_ID invoice_count
1 123 [NULL] 1
2 693 12 1
2 254 20 0
3 789 96 1
3 789 59 0
3 789 26 0
Can you please help me out.
|
|
08-10-10, 18:05
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
Data in a table have no intrinsic order. How do you define "first occurrence"?
|
|
08-10-10, 20:12
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
|
|
Quote:
|
Data in a table have no intrinsic order.
|
Yes! He is right.
In the following example, there is no gurantee to be chosen specific rows.
Code:
SELECT t.*
, 1 - SIGN( ROW_NUMBER() OVER(PARTITION BY invoice_id) - 1 )
AS invoice_count
FROM sample_data t
;
------------------------------------------------------------------------------
INVOICE_ID SALE_ID OBLIGOR_ID INVOICE_COUNT
----------- ----------- ----------- -------------
1 123 - 1
2 693 12 1
2 254 20 0
3 789 96 1
3 789 59 0
3 789 26 0
6 record(s) selected.
|
|
08-11-10, 09:27
|
|
Registered User
|
|
Join Date: Jun 2010
Posts: 17
|
|
I have invoice_ID, sale_ID and obligor_ID already present in the table order by invoice_id, sale_ID and obligor_id.
But the above set of code worked for me eventhough me invoice_id field is of Character Type.
But I did not get the concept behind it, Can you please elaborate on this?
Thanks...
|
Last edited by smartcooldevil; 08-11-10 at 10:01.
|
08-11-10, 16:01
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
Quote:
|
already present in the table order by invoice_id, sale_ID and obligor_id.
|
No!
Rows in a table is not ordered.
If the result of "SELECT * FROM table" showed a sequence of rows,
the order is eventualy.
You may get result with different order from later query.
For example, reorg table, create index or adding conditions to the query may change the resulting order.
|
|
08-11-10, 17:57
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Another solution is possible
Another solution without ROW_NUMBER
Code:
with sample_data
(INVOICE_ID, SALE_ID, OBLIGOR_ID, unique_id) as
(
select 1, 123, nullif(0,0) , generate_unique()
from sysibm.sysdummy1 union all
select 2, 693, 12, generate_unique()
from sysibm.sysdummy1 union all
select 2, 254, 20, generate_unique()
from sysibm.sysdummy1 union all
select 3, 789, 96, generate_unique()
from sysibm.sysdummy1 union all
select 3, 789, 59, generate_unique()
from sysibm.sysdummy1 union all
select 3, 789, 26, generate_unique()
from sysibm.sysdummy1
)
, sample_min_uid (INVOICE_ID, min_uid) as
(select INVOICE_ID, min(unique_id)
from sample_data group by INVOICE_ID
)
select sd.INVOICE_ID, sd.SALE_ID, sd.OBLIGOR_ID,
case when sd.unique_id = md.min_uid
then 1
else 0
end as INVOICE_COUNT
from
sample_data sd
join
sample_min_uid md
on sd.INVOICE_ID = md.INVOICE_ID
Result:
Quote:
INVOICE_ID SALE_ID OBLIGOR_ID INVOICE_COUNT
1 123--- 1
2 693 12 1
2 254 20 0
3 789 96 1
3 789 26 0
3 789 59 0
|
Lenny
|
Last edited by Lenny77; 08-11-10 at 18:00.
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
First occurence count
