SQL Server Ranking Functions

There are four ranking functions included with SQL Server (starting with SQL Server 2005). Those functions; ROW_NUMBER, RANK, DENSE_RANK, and NTILE; can be used to rank the rows of your result set over a partition. This article will present those functions and examples of their use.

Ranking Functions

Ranking functions are a subset of the built in functions in SQL Server. They are used to provide a rank of one kind or another to a set of rows in a partition. The partition can be the full result set, if there is no partition. Otherwise, the partitioning of the result set is defined using the partition clause of the OVER clause. The order of the ranking within the partition is defined by the order clause of OVER. Order is required even though partition is optional. The OVER clause syntax can be found here.

In the following, each ranking function will be discussed and an example shown. The examples for this article use tables in the AdventureWorks2008R2 database.

ROW_NUMBER

The ROW_NUMBER ranking function is the simplest of the ranking functions. Its purpose in life is to provide consecutive numbering of the rows in the result set by the order selected in the OVER clause for each partition specified in the OVER clause.

If no partition is specified, ROW_NUMBER will provide a consecutive numbering of the rows based on the order clause. If a partition is provided, the numbering is consecutive within the partition and begins again at 1 when the partition changes.

Example

Here we have a query to find the top 10 products that sold in 2008. ROW_NUMBER is used here to designate the ordering from highest qty ordered to lowest. The order clause in the OVER clause tells it to rank the rows by the sum of all the order quantities for that product.

SELECT TOP(10) ProductID, SUM(OrderQty) AS ProductOrderCount, ROW_NUMBER() OVER (ORDER BY SUM(OrderQty) DESC) AS Row

FROM Sales.SalesOrderDetail sod

JOIN Sales.SalesOrderHeader soh

ON sod.SalesOrderID = soh.SalesOrderID

WHERE OrderDate BETWEEN '1/1/2008' AND '12/31/2008'

GROUP BY ProductID

ORDER BY Row

Results

ProductID ProductOrderCount Row

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

870 3519 1

712 2376 2

711 2133 3

708 2064 4

707 2053 5

873 2051 6

864 1834 7

921 1782 8

884 1654 9

877 1475 10

(10 row(s) affected)

To give you an example of what ROW_NUMBER would do if there was a partition involved; consider this example where TerritoryID was added to the result set. In this case, we have to retrieve the top 20 so you can see the row number start over when the partition changes. The partition is by ProductID and the order by clause of the select statement changed to order the result set by ProductID and then Row. The GROUP BY clause also changed to include Territory ID. So, we are getting a summation of the OrderQty for each product in each territory. That result set then has the ROW_NUMBER applied to each product in the order of most quantity ordered in each territory. After the ranking is applied, the result set is ordered by ProductID and Row and the top 20 rows are returned.

SELECT TOP(20) ProductID, soh.TerritoryID,

SUM(OrderQty) AS ProductOrderCount,

ROW_NUMBER() OVER ( PARTITION BY sod.ProductID ORDER BY SUM(OrderQty) DESC) AS Row

FROM Sales.SalesOrderDetail sod

JOIN Sales.SalesOrderHeader soh

ON sod.SalesOrderID = soh.SalesOrderID

JOIN Sales.SalesTerritory st

ON soh.TerritoryID = st.TerritoryID

WHERE OrderDate BETWEEN '1/1/2008' AND '12/31/2008'

GROUP BY ProductID, soh.TerritoryID

ORDER BY ProductID, Row

Results

ProductID TerritoryID ProductOrderCount Row

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -

707 4 355 1

707 6 324 2

707 9 283 3

707 10 249 4

707 1 243 5

707 7 217 6

707 8 211 7

707 3 66 8

707 5 59 9

707 2 46 10

708 4 374 1

708 6 332 2

708 9 288 3

708 1 236 4

708 10 223 5

708 8 208 6

708 7 182 7

708 2 92 8

708 3 65 9

708 5 64 10

(20 row(s) affected)

RANK

The function RANK provides consecutive numbering except in the case of a tie. This function is used when you want all tied rows to have the same ranking, but the next row after the tied rows to have the rank it would have been assigned if there had been no tie. In other words, the numbers assigned by RANK are not necessarily consecutive.

Example

Let's take the first example from above (products ordered in 2008) and apply RANK instead of ROW_NUMBER. To get a result worth looking at, however, we have to limit the products to those having a ProductOrderCount between 400 and 500. You can see in the results that two of the rows had ProductOrderCount equal to 488. Therefore, with RANK, they both received the same ranking, in this case 2, and the next number is skipped. In this example the 2^nd and 3^rd items have a ranking of 2 and the fourth item has a ranking of 4.

- Advertisement -

SELECT ProductID, SUM(OrderQty) AS ProductOrderCount,

RANK() OVER (ORDER BY SUM(OrderQty) DESC) AS Ranking

FROM Sales.SalesOrderDetail sod

JOIN Sales.SalesOrderHeader soh

ON sod.SalesOrderID = soh.SalesOrderID

WHERE OrderDate BETWEEN '1/1/2008' AND '12/31/2008'

GROUP BY ProductID

HAVING SUM(OrderQty) BETWEEN 400 AND 500

ORDER BY Ranking

Results

ProductID ProductOrderCount Ranking

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

928 499 1

954 488 2

966 488 2

974 471 4

972 464 5

933 463 6

801 453 7

977 447 8

797 442 9

860 409 10

(10 row(s) affected)

DENSE_RANK

As with RANK, DENSE_RANK will divvy out the same rank to tying rows. In contrast, however, DENSE_RANK will not skip any numbers. So if the first two rows tie, they will both receive 1 for the rank. The third row will receive 2 for its rank, unlike with RANK where it would have been assigned 3.

Example

To display the difference, here is the same example query as above. Changing RANK to DENSE_RANK renders the same results with one distinction. Rows 2 and 3 are still tied and therefore receive the same rank of 2; however the fourth row now receives the rank of 3 instead of 4.

SELECT ProductID, SUM(OrderQty) AS ProductOrderCount,

DENSE_RANK() OVER (ORDER BY SUM(OrderQty) DESC) AS Ranking

FROM Sales.SalesOrderDetail sod

JOIN Sales.SalesOrderHeader soh

ON sod.SalesOrderID = soh.SalesOrderID

WHERE OrderDate BETWEEN '1/1/2008' AND '12/31/2008'

GROUP BY ProductID

HAVING SUM(OrderQty) BETWEEN 400 AND 500

ORDER BY Ranking

Results

ProductID ProductOrderCount Ranking

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

928 499 1

954 488 2

966 488 2

974 471 3

972 464 4

933 463 5

801 453 6

977 447 7

797 442 8

860 409 9

(10 row(s) affected)

NTILE

When you want to group rows equally across a partition, the NTILE function is the one you want. For this ranking function you need to provide it the number of groups into which you want the rows equally divided. Should the row count not be equally divisible by the number of groups, there will be a group or groups with one more row than the rest. Those groups with the extra row will come first in the ranking.

For instance, if you had 26 rows and desired 5 groups, the first group would contain 6 rows and the remaining 4 groups would contain 5 rows each.

Example

Again using the same query to identify products with a total quantity ordered in 2008 between 400 and 500, this time we are grouping them into 4 groups using NTILE and supplying the group count in the function call. Since this query returns 10 rows and 10 is not equally divided into 4 groups, you can see in the results that the first two groups contain 3 rows each and the last two groups contain just two rows each.

SELECT ProductID, SUM(OrderQty) AS ProductOrderCount,

NTILE(4) OVER (ORDER BY SUM(OrderQty) DESC) AS Ranking

FROM Sales.SalesOrderDetail sod

JOIN Sales.SalesOrderHeader soh

ON sod.SalesOrderID = soh.SalesOrderID

WHERE OrderDate BETWEEN '1/1/2008' AND '12/31/2008'

GROUP BY ProductID

HAVING SUM(OrderQty) BETWEEN 400 AND 500

ORDER BY Ranking

Results

ProductID ProductOrderCount Ranking

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

928 499 1

954 488 1

966 488 1

974 471 2

972 464 2

933 463 2

801 453 3

977 447 3

797 442 4

860 409 4

(10 row(s) affected)

Conclusion

SQL Server includes a subset of built-in functions called ranking functions. These ranking functions provide various means of ranking and grouping result sets by utilizing the OVER clause to identify the partitioning of the result set and the order for the ranking.

By Deanna Dicken

Using iconv to change character encodings

Introduction

iconv is used for character set conversion facility. With this command, you can turn a string represented by a local character set into the one represented by another character set, which may be the Unicode character set. Supported character sets depend on the iconv implementation of your system. Note that the iconv function on some systems may not work as you expect. In such case, it'd be a good idea to install the » GNU libiconv library. It will most likely end up with more consistent results.

Detail

The basic command is:

iconv -f old-encoding -t new-encoding file.txt > newfile.txt

You can get a list of supported encodings with (that's a lower-case L, not a one):

iconv -l

Example

iconv -f ISO-8859-1 -t UTF8 sample.txt > sample.txt

It is converting from ISO_8859-1 to UTF8.

Resources

• The official iconv man page.
• For Linux users who don't have it in their distribution, the iconv source is available from GNU.

Remember 4 sites for Java programming

Well, there are thousands of sites related to Java programming. But to start, you just need to remember 4 sites which I’m about to tell you now.


* The first site is java.com:

This is the official site for Java trademark. On this site you can verify whether your computer having Java installed or not, and download the latest version of Java. I usually tell my customers go to this website in order to check, download and install Java for their computers.

java.com also introduces some interesting teaching tools like Alice and Greenfoot which allow students or young people to begin learning Java in a fun and easy way with 3D graphics environment. Those are worth exploring.



* The second site is java.oracle.com:

This is the official site for Java technology. It’s the ultimate, complete and authoritative source of technical information about Java.

I often check this site to get updates or download installations and libraries for all Java editions: Java for desktop (Java SE); Java for enterprise (Java EE); Java for mobile (Java ME), etc.



* The third site is The Java Tutorial series:

Oracle (the company owns Java) has its own staffs who are dedicated to write tutorials for beginners. There are two very well-known series:

- The Java Tutorial series: you can find step-by-step tutorials for learning everything about Java core and Swing to build Java desktop applications. Here’s the link:

http://docs.oracle.com/javase/tutorial

- The Java EE Tutorial series: here you can learn everything about Java enterprise such as JavaServer Pages, Servlet… to build Java web applications. Here’s the link:

http://docs.oracle.com/javaee/7/tutorial

I like these tutorials a lot because they help me in the early days of my Java learning. Whenever I need to learn new features, I always start from those tutorials.



* The fourth site is the Javadocs:

As a Java programmer, you will definitely need to check the Javadocs (or Java API) frequently to understand what a class means or what a method does. For example: checking the Collections class and its utility methods. Here are the links:

- Java SE Javadocs: http://docs.oracle.com/javase/8/docs/api

- Java EE Javadocs: http://docs.oracle.com/javaee/7/api

I always bookmark these URLs in my browser for quick reference. I also saved several copies on my computer so I can access the API whenever I need it. Each copy is for a specific version, such as JDK 6, JDK 8, Java EE 6, Java EE 7…



so far I have shared with you the 4 sites that I think every Java programmer should know and visit frequently. Sometimes you can visit java.com or java.oracle.com to check for new stuffs. But the Java Tutorials series are recommended for everyone.

And the Javadocs sites should be bookmarked or better, downloaded and saved as copies for quick reference.

For me, the Javadocs are the documents I read most frequently in my Java programming career. And I think you too (or will too).

courtesy : Nam Ha Minh

Spring framework

Introduction

The Spring Framework provides a comprehensive programming and configuration model for modern Java-based enterprise applications - on any kind of deployment platform. A key element of Spring is infrastructural support at the application level: Spring focuses on the "plumbing" of enterprise applications so that teams can focus on application-level business logic, without unnecessary ties to specific deployment environments.

Features

Dependency Injection
Aspect-Oriented Programming including Spring's declarative transaction management
Spring MVC web application and RESTful web service framework
Foundational support for JDBC, JPA, JMS
Much more...

Quick Start

Jars needed for spring framework

1. spring-framework-3.2.9.RELEASE (http://maven.springframework.org/release/org/springframework/spring/3.2.9.RELEASE/spring-framework-3.2.9.RELEASE-dist.zip)
2. asm-2.2.3.jar (http://www.java2s.com/Code/JarDownload/asm/asm-2.2.3.jar.zip)
3. commons logging.jar
4. log4j jar

Once you downloaded this jars ,add to your libraries, you'll be able to do the following:

Example

here we have an interface named MessageService which have getMessage() unimplemented method.

hello/MessageService.java


package hello;
public interface MessageService {
    String getMessage();
}


the above Interface is implemented below

hello/MessagePrinter.java

package hello;

package hello;

public class MessagePrinter implements MessageService {

    private String name;
   
    public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

@Override
public String getMessage() {

// TODO Auto-generated method stub
return "Hello "+name;
}
}


hello/Application.java


package hello;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class Application {

/**
* @param args
*/
public static void main(String[] args) {
ApplicationContext ctx=new ClassPathXmlApplicationContext("spring.xml");
MessagePrinter mp=(MessagePrinter) ctx.getBean("messageprinter");
System.out.println(mp.getMessage());



}

}

spring.xml

<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="
      http://www.springframework.org/schema/beans
      http://www.springframework.org/schema/beans/spring-beans-2.0.xsd">

<bean name="messageprinter" class="hello.MessagePrinter">
<property name="name" value="jhon"></property>
</bean>

</beans>


 
The example above shows the basic concept of dependency injection.Here we are avoiding concept of creating new operator for object initialization.


If your intrested to learn Spring follow these sites which are very useful:
http://www.tutorialspoint.com/spring/index.htm
for video tutorials follow
https://www.youtube.com/playlist?list=PL2882729612B70122

ZIP and UNZIP with Passwords in Java

Article Summary:

ZIP and UNZIP with Passwords in Java — Zip and Unzip are a very common activities for a computer user. A user normally uses the zip utility to compress a directory to create a zip file. There are many ready-made software such as winzip,7zip, and winrar that are available to achieve this. However, it is also possible to protect the zip file with a password so that the end user has to provide the password to unzip the zip file. This is the very common scenario that can be achieved by a zip utility tool. The significant part of my article is to provide you with the solution to achieve this using a Java program. While developing the project you may encounter a scenario in which you have to create a password-protected zip file that can be unzipped by any zip tool like winzip. Let me provide a complete scenario for your understanding.

Article URL:

             http://java.sys-con.com/node/1258827 

Sending Email with Attachments

//Java program for sending Email

public class SendEmail{
public static void sendEmailWithAttachments(String host, String port,final String userName, final String password, String toAddress,String subject, String 
message, String[] attachFiles)throws AddressException, MessagingException {

// sets SMTP server properties

try {
Properties properties = new Properties();
properties.put("mail.smtp.host", host);
properties.put("mail.smtp.port", port);
properties.put("mail.smtp.auth", "true");
properties.put("mail.smtp.starttls.enable", "true");
properties.put("mail.user", userName);
properties.put("mail.password", password);

// creates a new session with an authenticator
Authenticator auth = new Authenticator() {
public PasswordAuthentication getPasswordAuthentication() {
return new PasswordAuthentication(userName, password);
}
};
Session session = Session.getInstance(properties, auth);

// creates a new e-mail message
Message msg = new MimeMessage(session);
msg.setFrom(new InternetAddress(userName));
InternetAddress[] toAddresses = { new InternetAddress(toAddress) };
msg.setRecipients(Message.RecipientType.TO, toAddresses);
msg.setSubject(subject);
msg.setSentDate(new Date());

// creates message part
MimeBodyPart messageBodyPart = new MimeBodyPart();
messageBodyPart.setContent(message, "text/html");

// creates multi-part
Multipart multipart = new MimeMultipart();
multipart.addBodyPart(messageBodyPart);

// adds attachments
if (attachFiles != null && attachFiles.length > 0) {

MimeBodyPart attachPart = null;
try{
for(int i=0;i<attachFiles.length;i++){
attachPart=new MimeBodyPart();
System.out.println(attachFiles[i]);
attachPart.attachFile(attachFiles[i]);
multipart.addBodyPart(attachPart);
}

     }
// sets the multi-part as e-mail's content
msg.setContent(multipart);
// sends the e-mail
Transport.send(msg);

} catch(javax.mail.MessagingException me) {
System.out.println("Email Not Sent ");
me.printStackTrace();
}
System.out.println("Email sent.");

}

/**
*   * Test sending e-mail with attachments   
*/
public static void main(String[] args) {
// SMTP info
String host = "smtp.gmail.com";
String port = "587";
String mailFrom = "your email address";
String password = "your password";
// message info
String mailTo = "reciepient email";
String subject = "send Email";
String message = "I have some attachments for you.";

// attachments
String attachFiles[] =new String[2]; //no of attachments
attachFiles[0] = "E:/src/names.xml";
attachFiles[1] = "E:/src/rose.png";
try {
sendEmailWithAttachments(host, port, mailFrom, password, mailTo,
subject, message, attachFiles);
} catch (Exception ex) {
System.out.println("Could not send email.");
ex.printStackTrace();
}

}
}

How to Learn a Programming Language

Whether you want to design a video game, develop some cool apps for iPhone or Android or just want to do it for fun, programming is the way to go. There are countless programming languages for all sorts of uses, but learning them is easy once you learn how to use one. As a beginner, you'll probably start with Java or HTML. Once you become proficient in a language, you'll be able to create all sorts of new programs and really let your creativity show through!

Steps

1. 
   1
   Decide your goal. Learning a programming language can be both quite interesting and challenging. Even university graduates study a language for years but they might not be able to figure out their specialty. So first decide what you want to become, for example a programmer, a game developer or a robotics expert.
2. 
   2
   Choose a programming language. Always start to learn from a core language. When you first begin to learn, choose mid-level language like C and afterwards C++. Learning these two is a must for any competent and complete programmer, as these are industry standards. Don't start off learning high level languages such as Java or something else as they can prove to be very confusing for a beginner. (You can always learn other languages later, of course, but you'll want to have C and C++, at a minimum, under your belt.). Since C/C++ could look too hard to true beginner, you may prefer Python also. It is widely considered as a good language for beginners. Study a language for at least one year. Learn the common programming paradigms, especially procedural and object-oriented. Read a lot and practice with a compiler or IDE (there are many free ones online). Once you've mastered a language, do not rush to implement. Instead buy some good books on data structure and learn the key subjects like searching, sorting, binary tree and linked list creation etc. Whether it is about game or software development, data structure is the protege. Later, you may move on to a high level language such as Java[1]. For serious programming learn computer algorithms like divide and conquer, greedy method, backtracking, etc. for another year at least.
3. 
   3
   Fall in love with mathematics. For various reasons, many avoid mathematics or just hate it. But to become a good programmer you must solve and exercise a good number of problems on a daily basis, say one problem for one day. As you can see, it is easy to say but difficult to do. It is not important how much knowledge you have in a language but its relevance to a particular problem. Most problems are related to mathematical series (Fibonacci, Floyd triangle, Fourier Series etc). Not only that, there are many mathematical equations that can save a lot of time. So, start to learn new equations and their aspects. Learn discrete mathematics and calculus. Without mathematics, coding is like grassing the cattle.
4. 
   4
   Be self-motivated. Remember, Rome was not built in a day. So don't rush to learn all in a day. Learning programming is not so easy because it employs logic frequently. So, try to understand the logic and practice it, practice until you memorize it. If you are only trying to memorize, you will miss the fun and the concept will be left unclear to you. So, stretch yourself to the limit, but don't try to run before you can walk.
5. 
   5
   Take your time. Even the most accomplished programmers fail to solve a simple problem on certain occasions. So, try to relax when solving a particular problem. Take notes, find a proper algorithm or prepare your own. This is why you must learn data structure and study mathematics. It takes many hours of practicing problem-solving skills on different types of problems before you can call yourself an expert. Sites like Project Euler or Codecademy[2] have many small programming assignments and tutorials that will help you practice and hone your skills.
6. 
   6
   Never back down. Programming can be very frustrating and annoying too if you act carelessly but once you solve a problem all frustration will disappear. Do a lot of calculus (Fourier, Vector, Boundary Problem, Linear Algebra, Matrix). When working on a particularly intricate problem, take periodic breaks to let your brain relax and relegate the problem to your subconscious mind. Make a good schedule for working. When you feel bored, take a nap or walk but never give up, never back down.
7. 
   7
   Become a master. Try to teach others and show your developed applications to others. If you know someone who is better than you, share your thoughts and problems with them, share experience as it will enrich your arsenal. Try to develop your own application imitating professional software like text editor, windows application. Take part in competitions to push yourself to the limit if you dare. Knowing one programming language is good, but mastering more is better, as you'll not only have more tools in your toolbox, but expose yourself to other ways of solving problems, because Regardless of what language you use most often, having knowledge of others to draw on will make you a better programmer and better able to understand common constructs and problems in the abstract. So learn several programming languages, especially two or three with different design philosophies, such as Lisp, Java, and Perl. But learn each of them properly.
8. 
   8
   Be a bookworm. Most well versed programmers are good readers too. They think before they even type a line of code. So, read a lot and think. Recheck your works once a week. (Document your code well, in order to make said rechecking easier.) Further, invest some money in good books. Try to buy the best (not necessarily the best selling) resources. Never slavishly follow a single resource as it will make your outlook narrower. So once you master something, research on it for more use and variations.
9. 
   9
   Invest in Training Centers. If you are not satisfied with your teach-yourself technique, enroll yourself into some beginner's course. But before the enrollment, always research on the previous graduates.
10. 
    10
    Have a lot of practice. Solve hundreds of programming problems. It would be the best if you can invent a lot of them yourself. However, if you can't, several web-sites could be useful:
    • TopCoder - popular US competitive programming website - it can really boost your algorithm knowledge.
    • Codeforces - other competitive programming website - contests here are held bit more often than at TopCoder.
    • Project Euler - great web-site with math-related programming problems.
    • CodeAbbey - problems targeted to real newcomers in programming, simpler then at the resources above.