Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p><code>DISTINCT</code> in SQL Server applies to the <em>entire row</em> you are returning from your result set. So if your data contains, for example:</p> <pre><code>mobileNo workNo homeNo name ... 1234 1234 5678 Joe ... 1234 867 5309 Stacy ... 1 555 5555 Sentinel ... </code></pre> <p>Your <code>DISTINCT</code> will not remove Joe or Stacy, because the entire rows are not equal. On the other hand, if your data contained:</p> <pre><code>mobileNo workNo homeNo name ... 1234 1234 5678 Joe ... 1234 1234 5678 Joe ... 1234 1234 5678 Joe ... 1234 1234 5678 Joe ... 1234 1234 5678 Joe ... 1234 867 5309 Stacy ... 1 555 5555 Sentinel ... </code></pre> <p>Your query would return only a single entry with the name "Joe", because it would return only the distinct rows from the result set.</p> <p>You should know that a <code>DISTINCT</code> is usually something I consider an <a href="http://en.wikipedia.org/wiki/Anti-pattern" rel="nofollow noreferrer">anti-pattern</a> in SQL. It's usually evidence of SQL that has been modified to produce results the author thinks are correct but with a query that does not truly represent their intentions.</p> <p>So I have to ask you, what is your goal in adding a DISTINCT to your query? Or, better yet, imagine if you were asked to return only the distinct <code>mobileNo</code> from the following table:</p> <pre><code>mobileNo workNo homeNo name ... 1234 1234 5678 Joe ... 1234 867 5309 Stacy ... 1 555 5555 Sentinel ... </code></pre> <p>If you were a SQL database, which row would you not return? Would you reject Stacy or Joe? That is the fundamental problem with your <code>DISTINCT</code> query - there is no way for the database to know which row you want to exclude. It cannot, based on the information you have given it, only return distinct <code>mobileNo</code>s, because two rows with different names but the same <code>mobileNo</code> are both equally valid to be excluded.</p> <h2>The X-Y Problem</h2> <p>So, ask yourself, what problem are you <strong>really</strong> trying to solve? I suspect your post here is a prime example of what's called the X-Y problem. <a href="https://meta.stackexchange.com/a/66378/180934">Here is a link</a> to the best example I have ever come across of this problem, and I've reproduced the it here below:</p> <blockquote> <h1>What is it?</h1> <p>The <a href="http://www.perlmonks.org/index.pl?node_id=542341" rel="nofollow noreferrer">XY</a> <a href="http://mywiki.wooledge.org/XyProblem" rel="nofollow noreferrer">problem</a> is asking about your attempted <em>solution</em> rather than your <em>actual problem.</em></p> <p>That is, you are trying to solve problem <code>X</code>, and you think solution <code>Y</code> would work, but instead of asking about <code>X</code> when you run into trouble, you ask about <code>Y</code>.</p> <h1>The Problem</h1> <p>This can lead to frustration by people who are trying to help you solve the problem because by the time you ask about it, the solution that you need help with might not have any obvious connections to the problem that you are trying to solve.</p> <h1>How to Avoid</h1> <p>To avoid falling into this trap, always include information about a broader picture along with any attempted solution. If someone asks for more information, or especially a more specific question, do provide details. If there are other solutions which you believe will be suggested and which you've already ruled out, then don't try to avoid going over them again – instead state why you've ruled them out, as this gives more information about your requirements and helps others provide better answers.</p> <h1>An Example</h1> <p>A recent IRC conversation for illustration:</p> <hr> <blockquote> <p>&lt;Q> Is there a function to return a string between two delimiters? </p> <p>&lt;B> i don't understand what you mean, but i doubt there's already a function </p> <p>&lt;C> split and slice </p> <p>&lt;D> partition too </p> <p>&lt;Q> I tried partition<br> &lt;Q> I was trying to use built-ins to get the number between something like this in a string "attribute1: 50.223, attribute2: 442.1" </p> <p>&lt;D> why not just parse the string? </p> <p>&lt;Q> I thought there may have been some built in parsing stuff </p> <p>&lt;D> pairs = [x.strip() for x in s.split(",")]; attribs = {k: v for x in pairs for k, v in x.split(": ")}<br> &lt;D> there's a few libraries, but simplistic formats are easy enough -- if you don't care about error handling<br> &lt;D> changing the source to use a well known format, e.g. json or yaml, is preferred when possible </p> <p>&lt;Q> This code actually comes from HTML<br> &lt;Q> but I don't know how to parse Javascript with HTMLParser or whatever it's called </p> <p>&lt;D> is it merely embedded in html, or some mangled version of html? </p> <p>&lt;Q> It's embedded in the HTML </p> <p>&lt;D> if it's javascript (and that is, except for missing outer braces), json can probably parse it </p> <p>&lt;Q> thanks </p> <p>&lt;D> I didn't say it explicitly: json only parses data structures, not js code </p> <p>&lt;Q> That's all I need parsed is a data structure </p> </blockquote> <hr> <p>The problem is really about how to parse Javascript data structures, not find "a string between two delimiters", yet it takes quite a bit of time and intuition to get to the real issue. </p> <p>This is easier to do in a fully interactive chat (regardless of what mode), but on a SE site, where you polish a post a bit, post it, and then have 5-30 mins, or longer, before feedback, it really helps to head in the right direction from the start.</p> </blockquote> <p>I believe based on your query that you are not actually trying to find the distinct <code>mobileNo</code>s of your database, because you are including all of this other data in your query. So what are you <em>really</em> trying to accomplish?</p>
    singulars
    1. This table or related slice is empty.
    1. PODISTINCT is not working in join query Cakephp
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USAaron Friel
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COsir thankyou for your detailed answer ... on Messages table there are are duplicate numbers in field Message.mobileNo ... i just want to pick the distinct numbers and then on the bases of those distinct numbers i am comparing or joining the contacts table in which there are mobileNo,workNo etc fields and see that wether those numbers are in the contacts table or not .. if they are grab the contact name for me ..hope you understand it
      singulars
      1. PO
      1. USmynameisjohn
    2. COok i am adding some detail on my question
      singulars
      1. PO
      1. USmynameisjohn
    3. COAh, what you want is the CakePHP equivalent to the `IN` expression, you want all the `Contact`s where their `MobileNo` is in the `Message` table. What you want to do is then change your `'contain'` expression so that the subquery is just `Message.mobileNo` and the `contain` is a condition of a query on your `Contact` table.
      singulars
      1. PO
      1. USAaron Friel
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload