Skip to main content

SQL using partition and connect by clause.

select id, max(ltrim(sys_connect_by_path(gred,' , '),' , ')) as mygred
from (select b.KJ19SIJIL||b.KJ19KOD as id, b.KJ19GRED as gred, row_number() over(partition by b.KJ19SIJIL||b.KJ19KOD order by b.KJ19GRED) as myrow
from kj19setara b)
start with myrow = 1
connect by prior myrow = myrow -1
and prior id = id
group by id
order by 1;
Labels:

Comments

Unknown said…
mender nie tak paham....sila jelaskan laa....
March 11, 2010 at 4:02 PM
Post a Comment

Popular posts from this blog

Why I Can't Move/Import VM in XenServer.

Ok this is what i found out, the metadata export/import via ' Backup, Restore and Update ' menu in xsconsole OR using ' xe vm-export filename=xxx metadata=true vm= ' will export all vm's metadata eventhough you've specify the vm in the latter, the former is ok because it is backup & restore tool. Problem happens when you want to restore the metadata or import to another xenserver. If you follow the method from 'Backup and Restore' in the reference guide, it should be ok if you're using pool because all pools are using shared storage; and if you don't use pool, your xenserver store all the VMs in the shared storage ONLY, means the Local SR is not use at all. This way, you wont face any problem. But, what if you have a mix of local & shared storage use. 
Post a Comment
more »

SQL Tips : EXTRACT function as alternative to TO_CHAR

You want to create a sql to list data group in year (datetime datatype). Example as below : The table is smp.SC03F_TEMP with over 10 million rows. Usually, if there something deals with date we will use to_char function. As an alternative, you can use extract function to extract certain part of the date. In belows example, we extract the year of a column. As we can see the time taken is almost half for 10 million rows. Happy trying. select count(*) from smp.SC03F_TEMP; -- 10,866,921 records select count(*), to_char(A.SC03TARIKH, 'YYYY') from smp.SC03F_TEMP a group by to_char(A.SC03TARIKH, 'YYYY'); -- Time taken 54sec
Post a Comment
more »