SQL - Find rows with duplicate values

The ideal is to group and count the number of rows that have the same values. If there are more than 1 row, then it has duplicates. Here is the syntax of the query:

SELECT column1, column2, columnX, COUNT(*) AS Duplicates FROM tablename
GROUP BY column1, column2, columnX
HAVING COUNT(*) > 1


An example

-- Create a table t.
CREATE TABLE t
(
  a INT,
  b VARCHAR(5),
  c VARCHAR(5)
);
 
-- Insert some values into table t.
INSERT INTO t VALUES(3, 'j', 'h' );
INSERT INTO t VALUES(3, 'j', 'h' );

Debian - Install JDK

  1. Add non-free in your /etc/apt/source.lst.
    deb http://debian.yorku.ca/debian/ lenny contrib non-free
     
  2. Update the packages list.

XSLT - xsltproc

Description xsltproc is a command line XSLT processor. It is used to apply XSL sheet on XML files.

Commands To get all options available for xsltproc, run

xsltproc --help

Apply XSL sheet on XML file:

xsltproc Data.xsl Data.xml > Data.output.txt

Passing string parameters into XSL sheet:

xsltproc --stringparam filename "my filename string" Data.xsl Data.xml > Data.output.txt

XSLT - Using XPath's text() function

The XML file:

<?xml version="1.0" encoding="UTF-8"?>
<root>
  <textarea name="123">a</textarea>
  <textarea name="123">b</textarea>
  <textarea name="123">a</textarea>
  <textarea name="123">c</textarea>
  <textarea name="555">a</textarea>
</root>

The XSL below shows how to use text() in XPath:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
  <xsl:output method="text" encoding="UTF-8" />
 
  <xsl:template match="/">

XSLT - How to handle apostrophe and quote in XSLT

The XML sample

<?xml version="1.0" encoding="UTF-8"?>
<root>
  <textarea name="123'_a">apostrophe: 123'_a</textarea>
  <textarea name="123">b</textarea>
  <textarea name="123">a</textarea>
  <textarea name="123'_&quot;_a">apostrophe and quote: 123'_&quot;_a</textarea>
  <textarea name="123345">c</textarea>
  <textarea name="555">a</textarea>    
</root>

The XSL codes:

<?xml version="1.0" encoding="UTF-8"?>
<!--
Below are examples showing how to handle apostrophe and quote in XSLT
-->

XSLT - Remove Namespace

<?xml version="1.0" encoding="UTF-8"?>
<!-- Remove Namespace -->
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
    <xsl:template match="*">
      <!-- remove element prefix (if any) -->
      <xsl:element name="{local-name()}">
        <!-- process attributes -->
        <xsl:for-each select="@*">
          <!-- remove attribute prefix (if any) -->
          <xsl:attribute name="{local-name()}">
            <xsl:value-of select="."/>

XSLT - Remove Element Without Child Nodes

<?xml version="1.0" encoding="UTF-8"?>
<!-- Remove Element Without Child Nodes -->
<!-- Only the 1st level is removed. -->
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
    <xsl:template match="*">
        <xsl:if test="count(@*) &gt; 0 or count(node()) &gt; 0">
            <xsl:copy>
                <xsl:apply-templates select="@* | node()"/>
            </xsl:copy>
        </xsl:if>
    </xsl:template>
    <xsl:template match="@* | text()">

XSLT - Remove attributes or / and nodes

<?xml version="1.0" encoding="UTF-8"?>
<!-- Remove unwanted attributes or/and nodes -->
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format">
    <xsl:output method="xml" encoding="UTF-8" indent="yes"/>
 
    <!-- Copy everything -->
    <xsl:template match="@*|node()|text()|comment()|processing-instruction()">
        <xsl:copy>
            <xsl:apply-templates select="@*|node()|text()|comment()|processing-instruction()"/>
        </xsl:copy>
    </xsl:template>
 

XSLT - Loops

Decrement Looping

<?xml version="1.0" encoding="UTF-8"?>
<!-- Decrement Looping: Recursively decrementing specified value down to 1. -->
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format">
    <xsl:output method="text" encoding="UTF-8"/>
 
    <xsl:template match="/">
        <!-- Calling DecrementLooping template -->
        <xsl:call-template name="DecrementLooping">
          <xsl:with-param name="i" select="9"/>
        </xsl:call-template>
     </xsl:template>

XSLT - Indent content of XML file

<?xml version="1.0" encoding="UTF-8"?>
<!--
Description: Indent the content of the XML file.
-->
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format">
  <xsl:output method="xml" encoding="UTF-8"/><!--Instruct how to output-->
 
  <xsl:param name="indent-increment" select="'   '"/>
  <xsl:template match="*">
    <xsl:param name="indent" select="'&#xA;'"/>
    <xsl:value-of select="$indent"/>
    <xsl:copy>
      <xsl:copy-of select="@*"/>
      <xsl:apply-templates>

Pages

Subscribe to OpenWritings.net RSS