Tag Archives: sql

T-SQL Pivots a Table

This post demos how to pivot a table using both the Pivot function and a home made approach. The code can be found from GIST. Problem An OrderItem table contains orderId, itemName and qty as its columns. Now we want … Continue reading

Posted in Database | Tagged , , | 1 Comment

Use CTE and Table Partition and Ranking Functions to Delete Rows with Duplicates

This post demonstrates to use T-SQL table partition, row_number ranking function, together with CTE to delete duplicated data from a table. The logic is saved in a stored procedure for reuse purpose. Two Tables in the Example: Orders table uses … Continue reading

Posted in Database | Tagged , , , , , | Leave a comment

Query the In-memory HSQLDB When Integrated With Hibernate

HSQLDB in-memory database is often used with Hibernate for testing, prototyping and even in production environment. With Hibernate, you can perform powerful database operations either using the ORM based APIs, HSQL or native SQL. As a developer, sometimes you want … Continue reading

Posted in Database, Java, Programming, Tool and Debug | Tagged , , , | 1 Comment

Copy or Duplicate a Database Record in SQL

There are a few ways to copy all the column values from a record (except for the auto-generated identity) into a new row. If the table only has a few columns (for example, a BookStore table has storeId as its … Continue reading

Posted in Database | Tagged , , | Leave a comment

SQL Finds and Deletes Duplicated Rows From a Table

Problem A table contains some rows with duplicated information caused by bugs. Now the bugs have been fixed, and you want to delete the duplicates to clean up the table. For example, from the OrderAudit table below, you want to … Continue reading

Posted in Database | Tagged , | Leave a comment

SQL Join Review

Here I just re-post a nice SQL JOIN diagram from stackoverflow by Lasse V. Karlsen, and adding a few examples to further illustrated the joins. Examples: First let’s create two tables and insert some data. The tables now look like … Continue reading

Posted in Database | Tagged , | Leave a comment

TSQL’s Equivalent to Create New Query Based on Previous Query Result

I have a table called PRICE with two columns, unitPrice and resellerId. From my application, I use the following code (pseudo) to query the unitPrice for a given reseller. Basically I use the unitPrice if the resellerId presents in the … Continue reading

Posted in Database | Tagged , | Leave a comment