Wednesday, October 22, 2008

Oracle - Rank Function Overview

Rank calculates the rank of a value in a group of values. Rows with equal values for the ranking criteria receive the same rank. Oracle then adds the number of tied rows to the tied rank to calculate the next rank. Therefore, the ranks may not be consecutive numbers.

Consider the following Schema

CREATE TABLE USER_TEST
(
user_id numeric(12),
first_name varchar2(32),
last_name varchar2(32),
age numeric(3),
salary numeric(7)
);

insert into user_test values(1, 'Pardeep', 'Kumar', 26, 20000);
insert into user_test values(2, 'Raj', 'Sharma', 23, 15000);
insert into user_test values(3, 'Jai', 'Singh', 30, 30000);
insert into user_test values(4, 'Rana', 'Pratap', 32, 35000);
insert into user_test values(5, 'Nakul', 'Gupta', 23, 16000);
insert into user_test values(6, 'Ritu', 'Kumar', 22, 10000);
insert into user_test values(7, 'Sita', 'Dikshit', 27, 22000);
insert into user_test values(8, 'Gurpal', 'Bhatia', 38, 49000);
insert into user_test values(9, 'Bhim', 'Kumar', 23, 19000);
insert into user_test values(10, 'Lokesh', 'Shriram', 22, 11500);

Aggregation

As an aggregate function, RANK calculates the rank of a hypothetical row identified by the arguments of the function with respect to a given sort specification.

The constant argument expressions and the expressions provided in the ORDER BY clause of the aggregate match by position. Therefore, the number of arguments must be the same and their types must be compatible.

Single Column RANK as Aggregation Function

Syntax:

RANK () WITHIN GROUP (ORDER BY NULLS )

Example:
The following query returns the rank for a user with salary 10,000SELECT RANK(20000) WITHIN GROUP
(ORDER BY salary DESC NULLS LAST) SAL_RANK
FROM user_test;

Result: SAL_RANK = 5

Multiple Column RANK as Aggregation Function

RANK () WITHIN GROUP (ORDER BY NULLS )

The following query returns the rank of a user a salary of 15,000 and age 23 years.

SELECT RANK(23, 15000) WITHIN GROUP
(ORDER BY age desc, salary desc) RANK
FROM user_test;

Result: Rank = 8

Analytic
As an analytic function, RANK computes the rank of each row returned from a query with respect to the other rows returned by the query, based on the values of the value_exprs in the order_by_clause.

RANK Analytic Function

RANK () OVER ()

Find the user with the 2nd highest salarySELECT * FROM (SELECT user_id, first_name, last_name, salary, RANK() OVER (ORDER BY salary DESC) userrank
FROM user_test) WHERE userrank = 2;

Result:

USER_ID FIRST_NAME LAST_NAME SALARY USERRANK
4 Rana Pratap 35000 2 RANK() OVER ( )

Arrange table by age and then salary

SELECT user_id,first_name, last_name, age, salary,
RANK() OVER (PARTITION BY age ORDER BY salary DESC) RANK
FROM user_test;

Result:

USER_ID FIRST_NAME LAST_NAME AGE SALARY RANK
10 Lokesh Shriram 22 11500 1
6 Ritu Kumar 22 10000 2
9 Bhim Kumar 23 19000 1
5 Nakul Gupta 23 16000 2
2 Raj Sharma 23 15000 3
1 Pardeep Kumar 26 20000 1
7 Sita Dikshit 27 22000 1
3 Jai Singh 30 30000 1
4 Rana Pratap 32 35000 1
8 Gurpal Bhatia 38 49000 1


Return all users with age 23 ordered by salary

SELECT user_id,first_name, last_name, age, salary,
RANK() OVER (PARTITION BY age ORDER BY salary DESC) RANK
FROM user_test where age = 23;

USER_ID FIRST_NAME LAST_NAME AGE SALARY RANK
9 Bhim Kumar 23 19000 1
5 Nakul Gupta 23 16000 2
2 Raj Sharma 23 15000 3

Although the examples quoted here may looks like that we are paying more cost for the same things that can be done by simple order by, but simple examples have been chosen to make it easy to understand how to use RANK.

Tuesday, October 7, 2008

Learn to Play Keyboard

One day I started searching internet about the same thing, how to play keyboard, went through a number of sites, blogs etc.. etc. But finally i learned it on my own, yes dont get surprised this is truth, because most of the sites will just provide you information on keyboard notes etc. But none of them tell you what goes wrong that you dont end up with success in keyboard playing. Well I am sharing my experience here, along with a couple of steps which I hope will be really helpful to you. Please read it like a story not like a lesson about keyboard learning, this will make it easy for you to learn keyboard. Also, this is for those who are busy with their life but still want to lean to play keyboard.

First and foremost thing:
a) Keep your keyboard at a place, where you can see it, see it in the morning when you get up, see it easily when you go around in your home, dont dump it into an almirah or trunk, this helps, dont get surprised, because everytime you see it at the back of the mind you will receive a motivation.
b) Next, keep the keyboard ready for use, starting to play should be as easy as possible, like just switch on and play it, this way you will be able to spend time with keyboard, even if you are free for some time.

With the above two tips, I am sure you will be spending time with keyboard daily and that is most important. Next few tips are bit serious.

a) Learn to play Sa, Re, Ga, Ma, Pa, Da, Ni, Sa with both hands. Remember Sa will be played with middle finger of left and hand and with index finger of right hand and so on. First play with right and then play with left and then play with both hands. While playing with both hands it may be bit confusing initially as different fingers of both hands are used, but this is important.
b) Learn to play various sequences, which we call ALANKAR or PALTAS, think of any sequence, e.g. Sa Sa, Re Re, Ga Ga, Ma Ma, Pa Pa, Ni Ni, Da Da, Sa Sa and then reverse of it, with right hand, then left hand and then with both hands. The more you practice this the more you will get knowledge of song notes, remember if you get easy with keyboard in coming times, remember to play these ALANKARs daily.
c) Next thing try to get notes of few songs which you like and which you love to listen to, practice this song, so that when ever you spend time on keyboard, you play these songs and it makes your practice session interesting, just practicing the notes sequences would certainly make it boring and you would end of spending time thinking that i am not able to do anything fruitful.

As you practice more and play some of the songs slowly you will be able to listen the song in form of notes and play them on your keyboard. Once you are ready to play notes and comfortable, try to sing it and play it and mix it with rythms. So, wish you luck, i can provide you notes for few songs and notes for sequences that you must practice to learn keyboard.

Monday, October 6, 2008

MUSSOORIE - Queen of Hills

Well, it was just 15 days to long weekend of 2nd october 2008 and we started thinking where do we go this time. Tired up of my daily drive to Gurgaon from Delhi, I wanted to go to a relaxing place, after some searching, we freezed mussoorie because of the following reasons.

a) Its easy to reach just 300 kms, and their is Dehradun Shatabdi which runs daily, and after dehradun you can get cabs.

b) Its queen of hills, now I too beleive it.
c) Very good hotels available on all the major sites. You can make a booking before you leave.
d) Place to go around are very nearby, that reduces the travel you do on a vacations.

These were my priorities and hence i freezed for mussoorie, next difficult question "Where to Say?", yes this was the most difficult, when i started giving thoughts initially, but then it because simpler when i have thought to my reason for the vacation "RELAX", so it became simple look out for a place away from crowds, I listed a few of them:

a) Surbhi Resorts located at "6, Mile Stone, Kempty Fall Road"
b) Country Inn located at "Kincreig, Nalapani House, Hayacoolie Estates"

Most of the people suggested to say near to Mall Road, but again since it was a long weekend, i thought staying near the crow will defeat the purpose of vacation, Surbhi give a free drop to Mall Road but it is around 7 Km from Mall road, Country Inn is around 2 Kms approx from Mall Road, so we finalized country Inn.

We started off from delhi at 6:50 A:M by shatabdi, i made an online booking of hotel before i left from delhi. To my surprise things were much better the movement we landed up at Dehradun, the cabs were easily available, they asked to charge Rs.450 for dropping to hotel, but the movement we started the person smiled and said "Sir agar upar hotel tak chodna hai to 500 lagenge", we agreed because we dont wanted to spoil mood because of 50 Rs. After driving through serpent climbing ways through beautiful mountains, we reached hotel in 1 hour 20 minutes.

I was delighted to receive warm welcome from the staff, everybody was very polite, and the manager handed over me key saying sir we are giving you best room. It was on the topmost side of resort. Was a seperate cottage and the views were amazing.

Best thing about mussoorie we liked was that you dont need to worry about fare while hiring a taxi, they have fix charges for everything and everyone charge the same.

First day we went to mall road and to Gun hill through ropeway it was beautiful, second day we hired a taxi for 1000Rs, the person picked us up from hotel and took us to Temple, Kempty Fall, Monestry and Lal Tibba. Kempty fall was very crowded and not that much clean, rest of the places were very good, last day we went to Municipal garden, it was really very beautiful and one of the best places in mussoorie, cabs do not go to municipal garden, you need to hire a rickshaw from Gunhill point on mall road, we were also surprise to hear about rickshaw in hilly area but its truth and it charges 180 Rs for two person.

Overall the trip was excellent, the best thing we liked were walking on the mall road and on the tracks in hills. Its really very beautiful and relaxing, but if you are truly going to relax my advise stay it bit away from mall road, although it costs a bit of extra pennies because you need to hire a cab but its worth and Country Inn was on amazing location with amazing views, i remember my morning tea and breakfast that we use to have in our room sitting near the window. When you take first sip of tea, it may be sunny and when you are about to finish your tea, you will find clouds near your window. Although their are better hotels to stay in mussorrie but i found country in the best buy, the whole package cost was 11,000 for 3N/4D including breakfast dinner and their was a acitivity room, where we played TT two three times and it was fun.

So, go there and have best days of your life :-)

I have uploaded a few pics:

a) View from the room.



b) Country Inn Resort


c) Municipal Garden Mussoorie



Creating Image Thumbnails

When I started working on this initially, I started looking into open source tools for this, after spending couple of hours on open source tools I suddenly jumped into oracle site and was pleased to see a good support for image processing in oracle.

Oracle provides support to create thumbnail of images stored as blob.

Here is simplest way to create image thumbnails.

Create a table that will store the original image and the thumbnail image content as blob.

CREATE TABLE test_thumbnail
(
id NUMERIC(12) PRIMARY KEY,
image_content BLOB,
thumbnail_content BLOB,
);

Create a stored procedure that will update the thumbnail_content field after image is stored in the database.

create or replace PROCEDURE IMAGE_THUMB_PROCEDURE (imgId IN NUMBER , imgAttribute IN varchar2) AS
imageId integer := 0;
verb varchar2(100);
src_blob BLOB;
dst_blob BLOB;
BEGIN

imageId := imgId;
verb := imgAttribute;

update test_thumbnail set thumbnail_content=empty_blob() where id = imageId;

select image_content into src_blob from test_thumbnail where id = imageId;

select thumbnail_content into dst_blob from test_thumbnail where id = imageId for update;

ordsys.ordimage.processCopy(src_blob ,verb ,dst_blob);

update test_thumbnail set thumbnail_content = dst_blob where id = imageId;

END IMAGE_THUMB_PROCEDURE;

Invoke the stored procedure passing the appropriate imgAttribute, passing different kind of image attributes you can process the original image content, for scaling down the image to a thumbnail of 100x100 pixels, pass the following parameter:

“maxscale=100 100 fileformat=jfif”

Maxscale is the size of the generated image and fileformat has its obvious meaning.

The method ordsys.ordimage.processCopy(src_blob ,verb ,dst_blob) copy a source blob into a destination blob doing the processing as per attributes passed as verb.

For more details on oracle media solution support please read the following article:
http://www.oracle.com/technology/sample_code/products/intermedia/index.html

Once the stored procedure is invoked image thumbnail is stored as blob in field thumbnail_content.