Are there better ways to use SELECT?
Yes there are.. and now you will learn some of the better ways of using the SELECT along with some other SQL terms such as DISTINCT , ORDER , MAX , MIN , AVG , etc..

For all the examples in this article we would be using a sample database table which is shown below

Table Name : people

How could I get distinct entries from a table?
The SELECT statement in conjunction with DISTINCT lets you select a set of distinct values from a table in a database. The values selected from the database table would ofcourse depend on the various conditions that are specified in the SQL query. Here are some ways to use the DISTINCT keyword.

SELECT DISTINCT lastname FROM people
Would return a recordset with 3 records. Each record would have 1 value in it. So basically the first record would contain 'Gates', the second would contain 'Hunter' and the third would contain 'Kanetkar'. Inspite of the lastname 'Gates' being present twice in the table, only one occurrence of it will be considered since the DISTINCT keyword was used in the SQL statement.


Is there a way to get the results of a Query sorted in any order?
Yes there are ways which will sort the results and return the sorted results to your program.. thus saving you the pain of carrying out the sorting yourself. The ORDER BY keyword is used for sorting.

SELECT firstname, age, city FROM people ORDER BY firstname
Would return a recordset with 4 records. Each record would have 3 values corresponding to firstname, age and city. But the specialty of this statement is that the records would be sorted according to the firstname in ascending alphabetical order (A first - Z last).
e.g. The first record would be that corresponding to the person whose firstname is 'Anthony' , followed by 'Bill' and then 'Jason' and finally 'Yashwant'.

SELECT firstname, age, city FROM people ORDER BY firstname DESC
Would return a recordset with 4 record as the above case, but this time the records would be in the reverse order. Namely the first record would be 'Yashwant' and the fourth one would be 'Anthony'

How can I find the total number of records in a table?
You could use the COUNT keyword in many ways.. here are some ways.

SELECT COUNT(*) FROM people WHERE age>40
Would return a recordset consisting of 1 value. Contrary to previous SQL statements the COUNT statement return one value which directly indicates the total number of records existing in the database that fulfill your conditions
e.g. In our case the above statement would return a value of 2

SELECT COUNT(city) FROM people
Would return a recordset consisting of 1 value. And that value would be equal to 4. The important point to note is that this statement return the total number of Non-Null entries only.

SELECT DISTINCT COUNT(lastname) FROM people
Would return a recordset consisting of 1 value. And that value would be equal to 3. Remember that when you use the COUNT keyword you do not get the actual lastname of the persons but you only get the total number of records that exist in the database that match your requirements. And in this case since DISTINCT was also used it would find the total number of records where there are distinct firstname only.

I heard there is some mathematical stuff in SQL?
Yeah.. there are many simple operations that you could do in order to formulate some useful information from a database rather than getting simple records from the database. Here are a few examples of these mathematical operations

SELECT AVG(age) FROM people
Would return 1 value corresponding to the average age of all the persons that exist in the table people.

SELECT AVG(age) FROM people WHERE age>30
You should be able to figure that out yourself.. if not please start reading right from the first article in this series ;-)

SELECT MAX(age) FROM people
Returns the maximum age among all the persons in the table people.

SELECT MIN(age) FROM people
Returns the minimum age among all the persons in the table people.

SELECT SUM(age) FROM people WHERE age>20
Returns the total sum of all the ages of the persons whose age is above 20 from the table people.