Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p><strong>Updated to use your newly provided data:</strong></p> <p>The solutions using the original data may be found at the end of this answer.</p> <p>Using your new data:</p> <pre><code>DECLARE @T TABLE( [contract] INT, project INT, activity INT ) INSERT INTO @T VALUES( 1000, 8000, 10 ) INSERT INTO @T VALUES( 1000, 8000, 20 ) INSERT INTO @T VALUES( 1000, 8001, 10 ) INSERT INTO @T VALUES( 2000, 9000, 49 ) INSERT INTO @T VALUES( 2000, 9001, 49 ) INSERT INTO @T VALUES( 3000, 9000, 79 ) INSERT INTO @T VALUES( 3000, 9000, 78 ) SELECT DISTINCT [contract], activity FROM @T AS A WHERE (SELECT COUNT( DISTINCT activity ) FROM @T AS B WHERE B.[contract] = A.[contract]) = 1 </code></pre> <p>returns: 2000, 49</p> <p><strong>Solutions using original data</strong></p> <p><strong>WARNING:</strong> The following solutions use the data previously given in the question and may not make sense for the current question. I have left them attached for completeness only.</p> <pre><code>SELECT Col1, Count( col1 ) AS count FROM table GROUP BY col1 HAVING count &gt; 1 </code></pre> <p>This should get you a list of all the values in col1 that are not distinct. You can place this in a table var or temp table and join against it.</p> <p>Here is an example using a sub-query:</p> <pre><code>DECLARE @t TABLE( col1 VARCHAR(1), col2 VARCHAR(1), col3 VARCHAR(1) ) INSERT INTO @t VALUES( 'A', 'B', 'C' ); INSERT INTO @t VALUES( 'D', 'E', 'F' ); INSERT INTO @t VALUES( 'A', 'J', 'K' ); INSERT INTO @t VALUES( 'G', 'H', 'H' ); SELECT * FROM @t SELECT col1, col2 FROM @t WHERE col1 NOT IN (SELECT col1 FROM @t AS t GROUP BY col1 HAVING COUNT( col1 ) &gt; 1) </code></pre> <p>This returns:</p> <pre><code>D E G H </code></pre> <p>And another method that users a temp table and join:</p> <pre><code>DECLARE @t TABLE( col1 VARCHAR(1), col2 VARCHAR(1), col3 VARCHAR(1) ) INSERT INTO @t VALUES( 'A', 'B', 'C' ); INSERT INTO @t VALUES( 'D', 'E', 'F' ); INSERT INTO @t VALUES( 'A', 'J', 'K' ); INSERT INTO @t VALUES( 'G', 'H', 'H' ); SELECT * FROM @t DROP TABLE #temp_table SELECT col1 INTO #temp_table FROM @t AS t GROUP BY col1 HAVING COUNT( col1 ) = 1 SELECT t.col1, t.col2 FROM @t AS t INNER JOIN #temp_table AS tt ON t.col1 = tt.col1 </code></pre> <p>Also returns:</p> <pre><code>D E G H </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POSQL: How do I SELECT only the rows with a unique value on certain column?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USvfilby
    1. USvfilby
    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. VTDownMod
    1. COI'm not sure what the original question looked like when you answered this, but given what is there now, it misses the target and is extremely difficult to map to the target. I suspect that is because you had less information to work with then than we do now.
      singulars
      1. PO
      1. USJonathan Leffler
    2. COYes, the asker changed the question and used a different data set. The answers given above were complete with the dataset given. I have updated the answer to reflect this and added a solution using the new dataset.
      singulars
      1. PO
      1. USvfilby
 

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