Rank SQL query Result

I was playing with mysql client,suddenly found that it would be really great if I can rank the query results.Lets say we have a table like below:

Now we want to rank  the students based on their Roll No.To do it,we have to define a variable with the initial value of 0 and in next query we will show it by as one of the resulted field  query

like below:


set @counter=0;

select @counter:=@counter+1 as Rank,LastName,Roll_no as Roll  from Students order by Roll_no ASC;

and in next query we will show it by as one of the resulted field ,that results:

Check mysql docs from here to know details about user defined variable in mysql

Advertisements

SQL in Deep

SQL commands are the only one way to retrive,update,delete data from database.As I worked with several small projects where I have to play with sql quiries.A strong query can minimize hard work of the developer,even can make an application dynamic and well structured.Couple of months ago Saikat and me was wondering to find out the higest value of a field that means in a field the higest number of entity was inserted.Expalin with an example suppose a table is “A” and in this table a field is “B”,B is filled with respectively a,a,b,c,s,s,s,a,x so I have to find out which value is populated the field “B” most of the time?The real life problem I faced during my “Blood Donation Management System” that I have to find out the Blood Group that is most available(frequent blood_group) in the system.So I tried with my sql knowledge(basics L).Then just googled,but unfortunately I found nothing.Then I just pack the problem L.After couple of days just trying to learn the SQL queries and practice in to HeidiSQL.Just got an old function “count” and trying to play with that.Suddenly I tought “Why I should not solve the problem with this COUNT?”

My table name is:’info’;
Field name is : ‘blood_group’;
SQL QUERY>>


 ”select blood_group,count(*) from info group by blood_group”;

Output>>

blood_group Count(*)
O+ 3
A- 10
B+ 1
B- 1
Ab+ 1
A+ 1
O- 1


 

 

 

 

 

The next step to add order by,limt to find out the most frequent value.

”select blood_group,count(*) from info group by blood_group order by count(*) desc limit 0,1”;

Output>>

bood_group Count(*)
A- 10

 

 

 

If I wanted to to know the least number of entity in the blood_group field,the command wil be as belows:

”select blood_group,count(*) from info group by blood_group order by count(*) asc limit 0,1”;

That’s all  🙂

Date difference in SQL

অনেক সময় আমাদের কে দুটি date এর difference বের করতে হয়,application এ কোড করে আগে করতাম,mysql থেকেও করা যায়,

Input: SELECT DATEDIFF("1950-08-15","1947-08-15");

Outut: 1096

রেজাল্টটা আসে total date হিসেব করে 🙂

Data encrption & decyption using SQL

বিভিন্ন আপ্লেকেশনে আমদেরকে এনক্রিপ্ট data use করতে হয়,যেমন আমরা অনেক সময় user password encryptionএর জন্যে php তে md5() function use করি।কিত্নু তাতে code এ কাজ করতে হয়…sql এ এরকম function আছে যার সাহায্যে ]খুব সহযেই data encryption,decryption করা যায়ঃ
name of the dbase:test
tables:cste,ohramacy
table cste :course,faculty
command encryption: insert into cste (course,faculty) values (aes_encrypt(‘cste420′,’mr’),aes_encrypt(‘cste420′,’mr’));

command deception: select aes_decrypt(course,’cste420′), aes_decrypt(faculty, ‘mr’) from test_1;