How about simply:

    select distinct c1, c2 from t

or

    select c1, c2, count(*)
    from t
    group by c1, c2


If you want distinct values from only two fields, plus return other fields with them, then the other fields must have some kind of aggregation on them (sum, min, max, etc.), and the two columns you want distinct must appear in the group by clause. Otherwise, it&#39;s just as Decker says.

You can get result distinct by two columns use below SQL:

    SELECT COUNT(*) FROM (SELECT DISTINCT c1, c2 FROM [TableEntity]) TE

If you still want to group only by one column (as I wanted) you can nest the query:


    select c1, count(*) from (select distinct c1, c2 from t) group by c1

Share my stupid thought: 

Maybe I can select distinct only on c1 but not on c2, so the syntax may be `select ([distinct] col)+ ` where `distinct` is a qualifier for each column.  

But after thought, I find that **distinct on only one column is nonsense**. Take the following relationship:

```
   | A | B
__________
  1| 1 | 2
  2| 1 | 1
```
If we `select (distinct A), B`, then what is the proper `B` for `A = 1`?

Thus, `distinct` is a qualifier for a `statement`.

Are there any **professional** Combobox controls (dropdown list with autosuggestion) based on the jQuery library?

It should be able to handle **large datasets** and have some **skinning** options. A **multi-column result list** would be great too. I&#39;m working with ASP.NET, but it&#39;s a not a problem if I had to write a wrapper for it.

![alt text][1]

*I&#39;m already using a third-party control, but I ran into some compatibilty issues between two vendor&#39;s controls. Well, I want to get rid of this kind of dependencies.*


  [1]: http://i.stack.imgur.com/ilhvD.png

Professional jQuery based Combobox control?

I&#39;ve got a list in a Python program that contains a series of numbers, which are themselves ASCII values.  How do I convert this into a &quot;regular&quot; string that I can echo to the screen?

How do I convert a list of ascii values to a string in python?

Can you get the distinct combination of 2 different fields in a database table?  if so, can you provide the SQL example.

SQL distinct for 2 fields in a database

<p>Can you get the distinct combination of 2 different fields in a database table?  if so, can you provide the SQL example.</p>


Consider a database table holding names, with three rows:

    Peter
    Paul
    Mary

Is there an easy way to turn this into a single string of `Peter, Paul, Mary`?




How to concatenate text from multiple rows into a single text string in SQL Server

When you run something similar to:

    UPDATE table SET datetime = NOW();

on a table with 1 000 000 000 records and the query takes 10 seconds to run, will all the rows have the exact same time (minutes and seconds) or will they have different times? In other words, will the time be when the query started or when each row is updated?

I&#39;m running MySQL, but I&#39;m thinking this applies to all dbs.

When running UPDATE ... datetime = NOW(); will all rows updated have the same date/time?

Does anyone know where I can find a library of common but difficult (out of the ordinary) SQL script examples. I am talking about those examples you cannot find in the documentation but do need very often to accomplish tasks such as finding duplicates etc.

It would be a big time saver to have something like that handy.

EDIT: Thanks everyone, I think this is turning into a great quick reference. The more descriptive the more effective it would be, so please if you see your way open - please edit and add some descriptions of what one could find. Many thanks to those that have already done so!

Library of Useful (Difficult) SQL scripts

I have a postgres table. I need to delete some data from it. I was going to create a temporary table, copy the data in, recreate the indexes and the delete the rows I need. I can&#39;t delete data from the original table, because this original table is the source of data. In one case I need to get some results that depends on deleting X, in another case, I&#39;ll need to delete Y. So I need all the original data to always be around and available.

However it seems a bit silly to recreate the table and copy it again and recreate the indexes. Is there anyway in postgres to tell it &quot;I want a complete separate copy of this table, including structure, data and indexes&quot;?

Unfortunately PostgreSQL does not have a &quot;CREATE TABLE .. LIKE X INCLUDING INDEXES&#39;

Copy a table (including indexes) in postgres

I have a Stored procedure which schedules a job. This Job takes a lot of time to get completed (approx 30 to 40 min). I need to get to know the status of this Job.
Below details would help me

1) How to see the list of all jobs that have got scheduled for a future time and are yet to start

2) How to see the the list of jobs running and the time span from when they are running

3) How to see if the job has completed successfully or has stoped in between because of any error.


How can I determine the status of a job?

Is there a way to create a Distinct query in HQL.  Either by using the &quot;distinct&quot; keyword or some other method.  I am not sure if distinct is a valid keywork for HQL, but I am looking for the HQL equivalent of the SQL keyword &quot;distinct&quot;.

How do you create a Distinct query in HQL

Given the following simple example:

        List&lt;string&gt; list = new List&lt;string&gt;() { &quot;One&quot;, &quot;Two&quot;, &quot;Three&quot;, &quot;three&quot;, &quot;Four&quot;, &quot;Five&quot; };

        CaseInsensitiveComparer ignoreCaseComparer = new CaseInsensitiveComparer();

        var distinctList = list.Distinct(ignoreCaseComparer as IEqualityComparer&lt;string&gt;).ToList();

It appears the CaseInsensitiveComparer is not actually being used to do a case-insensitive comparison.  

In other words **distinctList**  contains the same number of items as **list**.   Instead I would expect, for example, &quot;Three&quot; and &quot;three&quot; be considered equal.

Am I missing something or is this an issue with the Distinct operator?


LINQ Distinct operator, ignore case?

I&#39;m trying to implement paging using row-based limiting (for example: `setFirstResult(5)` and `setMaxResults(10)`) on a Hibernate Criteria query that has joins to other tables.

Understandably, data is getting cut off randomly; and the reason for that is explained [here][1].

As a solution, the page suggests using a &quot;second sql select&quot; instead of a join. 

How can I convert my existing criteria query (which has joins using `createAlias()`) to use a nested select instead?

  [1]: https://developer.jboss.org/wiki/HibernateFAQ-AdvancedProblems#jive_content_id_Hibernate_does_not_return_distinct_results_for_a_query_with_outer_join_fetching_enabled_for_a_collection_even_if_I_use_the_distinct_keyword

How to get distinct results in hibernate with joins and row-based limiting (paging)?

Here&#39;s what I&#39;m trying to do. I&#39;m querying an XML file using LINQ to XML, which gives me an IEnumerable`&lt;T`&gt; object, where T is my &quot;Village&quot; class, filled with the results of this query. Some results are duplicated, so I would like to perform a Distinct() on the IEnumerable&lt;Village&gt; object, like so:

    public IEnumerable&lt;Village&gt; GetAllAlliances()
    {
        try
        {
            IEnumerable&lt;Village&gt; alliances =
                 from alliance in xmlDoc.Elements(&quot;Village&quot;)
                 where alliance.Element(&quot;AllianceName&quot;).Value != String.Empty
                 orderby alliance.Element(&quot;AllianceName&quot;).Value
                 select new Village
                 {
                     AllianceName = alliance.Element(&quot;AllianceName&quot;).Value
                 };
                 
            // TODO: make it work...
            return alliances.Distinct(new AllianceComparer());
        }
        catch (Exception ex)
        {
            throw new Exception(&quot;GetAllAlliances&quot;, ex);
        }
    }

As the default comparer would not work for the Village object, I implemented a custom one, as seen here in the AllianceComparer class:

    public class AllianceComparer : IEqualityComparer&lt;Village&gt;
    {
        #region IEqualityComparer&lt;Village&gt; Members
        bool IEqualityComparer&lt;Village&gt;.Equals(Village x, Village y)
        {
            // Check whether the compared objects reference the same data.
            if (Object.ReferenceEquals(x, y)) 
                return true;

            // Check whether any of the compared objects is null.
            if (Object.ReferenceEquals(x, null) || Object.ReferenceEquals(y, null))
                return false;

            return x.AllianceName == y.AllianceName;
        }

        int IEqualityComparer&lt;Village&gt;.GetHashCode(Village obj)
        {
            return obj.GetHashCode();
        }
        #endregion
    }

The Distinct() method doesn&#39;t work, as I have exactly the same number of results with or without it. Another thing, and I don&#39;t know if it&#39;s usually possible, but I cannot step into AllianceComparer.Equals() to see what could be the problem.&lt;br/&gt;
I&#39;ve found examples of this on the Internet, but I can&#39;t seem to make my implementation work.

Hopefully, someone here might see what could be wrong here!
Thanks in advance!

C# Distinct on IEnumerable&lt;T&gt; with custom IEqualityComparer

I am playing with LINQ to learn about it, but I can&#39;t figure out how to use `Distinct` when I do not have a simple list (a simple list of integers is pretty easy to do, this is not the question). What I if want to use [Distinct][1] on a list of an Object on *one* or *more* properties of the object?

Example: If an object is `Person`, with Property `Id`. How can I get all Person and use `Distinct` on them with the property `Id` of the object?

    Person1: Id=1, Name=&quot;Test1&quot;
    Person2: Id=1, Name=&quot;Test1&quot;
    Person3: Id=2, Name=&quot;Test2&quot;

How can I get just `Person1` and `Person3`? Is that possible?

If it&#39;s not possible with LINQ, what would be the best way to have a list of `Person` depending on some of its properties in .NET 3.5?

  [1]: https://msdn.microsoft.com/library/system.linq.enumerable.distinct%28v=vs.100%29.aspx


Content Type	Original Author	Original Content on Stackoverflow
Question	leora	View Question on Stackoverflow
Solution 1 - Sql	Howard Pinsley	View Answer on Stackoverflow
Solution 2 - Sql	Jeffrey L Whitledge	View Answer on Stackoverflow
Solution 3 - Sql	Wilson Wu	View Answer on Stackoverflow
Solution 4 - Sql	Denno	View Answer on Stackoverflow
Solution 5 - Sql	youkaichao	View Answer on Stackoverflow

SQL distinct for 2 fields in a database

Sql Problem Overview

Sql Solutions

Solution 1 - Sql

Solution 2 - Sql

Solution 3 - Sql

Solution 4 - Sql

Solution 5 - Sql

How do I convert a list of ascii values to a string in python?

Professional jQuery based Combobox control?

Attributions