It's simple to remove duplicates from a table. Here is how.

Assume you have a table with duplicate rows:

Table: emp
id (Primary Key)	name
1	Ela
2	Ramesh
3	Ela
4	Ashok
5	Ela

In the table above, 'Ela' is present more than once with different ids. One way to remove the duplicate is GROUP the rows by name alone and then insert the results to a new table with newly generated ids. But the new ids will not match the old (at least for non-duplicate rows).

To ensure the ids will match, we need a workaround, which is comparatively simple.

Step 1:

Try to get the ids of the unique items present in the table. This can be done either by MIN or MAX function. For example,

SELECT
      MIN(id)
FROM
      emp
GROUP BY
      name;

This will result in the following table:

min

1

3

5

Step 2:

Hah... this gives me the unique id values. This is enough to remove the other rows using a sub query. Here it is:

DELETE
FROM
      emp 
WHERE
      id NOT IN
      (
        SELECT
               MIN(id)
        FROM
                emp
        GROUP BY
                name
       );

That's all folks! The result is a cleaned up table without duplicates:

Table: emp
id (Primary Key)	name
1	Ela
2	Ramesh
4	Ashok

Free Blog Counter

Folders Size calculator

Hello,

Here is a useful tool that lists each folder and their size. You can even download the source code and modify for your needs.

Download the tool
Download Source Code (VS 2008 Project)

Progress Bar and Threading in C# .NET

Hi,
Adding a progress bar and updating it without freezing the main UI Form has always been tough for many .NET programmers. Here is a skeleton (demo) application on how to update progress bar on the main window from a secondary thread. This is a skeleton application which you can use as such just by inserting your code snippet in the right place.

Download Source Code (Visual Studio 2008 Project)

You can download and modify the source code for any purpose free of cost.

"GROUP BY" does not group some data - Why?

"GROUP BY" clause (tested in PostgreSQL) does not group rows by on-the-fly temporary columns included in SELECT statement. For example, the below SQL will not group properly since 'manager_name' column is included in 'GROUP BY'. But, removing it from 'GROUP BY' will work properly.

Erroneous sql:

SELECT
    emp.name,
    salary.year,
    SUM(salary.amount),
    'Ela' as manager_name -- On the fly temporary column
FROM
    emp
JOIN
   salary ON salary.emp_id = emp.id
GROUP BY   
    emp.name,
    salary.year,
    manager_name -- <-- Here is the problem! This should be removed.
ORDER BY
    emp.name,
    salary.year;   

Corrected SQL:

SELECT
    emp.name,
    salary.year,
    SUM(salary.amount),
    'Ela' as manager_name
FROM
    emp
JOIN
   salary ON salary.emp_id = emp.id
GROUP BY   
    emp.name,
    salary.year
ORDER BY
    emp.name,
    salary.year;   

Introduction to Postgres Stored Procedure

Postgres does not directly seem to support stored procedures. However, it supports it through functions and cursors. Here is an example how to write a stored procedures in PostgreSQL:

1. Create a function that returns a cursor.

CREATE OR REPLACE FUNCTION
    get_employees(emp_cursor refcursor, dept_id int)
RETURNS refcursor AS 
$BODY$
    DECLARE
        sql_statement character varying;
    BEGIN

        sql_statement = 'SELECT id, name FROM emp WHERE dept_id = ' || CAST(dept_id AS character varying);
        
        OPEN emp_cursor FOR EXECUTE sql_statement;
        RETURN emp_cursor;
        
    END
$BODY$
LANGUAGE 'plpgsql' ;

That's all. Your stored procedure is ready to be accessed from your application code.

2. To call the sp from your code, you need to run two sql statements consecutively within a single transaction. Given below is sample JAVA code (A similar approach/workaround should be available in other languages too):

/*
Create your connection object here.
*/

connection.setAutoCommit(false);

/*
The above is required since every statement will be committed automatically by default. If your language does not support the above methodology, try database level transaction with BEGIN END statements.
*/

Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT get_employees('emp_cursor', 1);");
resultSet = statement.executeQuery("FETCH ALL IN emp_cursor;");

/*
Do whatever stuff you want to do with the result set.
*/

connection.commit(); //Commit the transaction
connection.close();

Wicket quickstart modifed for request response

Download Project Source

1. Initial Screen

2. Enter something and click "Update"

3. Resulting page

This is the first time I am trying a JAVA application (10 years back I had just opened the big JAVA book and left it as such and fully concentrated on C++ and .NET). The above application is a modified version of the quickstart wicket project available in apache website. I have modified it to a request response application which is quite easy to understand if you are a C++ or .NET programmer. (You must have some basic knowledge about wicket)

Apache wtih PHP - Ready to use Installation Package

This is a readymade PHP-Apache installation package to work with PHP on Apache Web Server on Windows. Simply download and install the setup. Your PHP development enviornment will be ready right away. This package comes with the following:
PHP Version 5.2.6
Apache Version 2.2.10 (Win32)
PHP MySQL extension

NOTE: Apache will be installed to port number 9090.

Download Setup

This setup is suitable for development environments only. Extra care/settings should be considered for production environments.