Given two columns, t1 with distinct values, but a superset of t2, how is it possible to operate over t1 to subtract the elements of t2 of t1? I utilize MySQL 5.7 CREATE TABLE IF NOT EXISTS t1 (    id_ INTEGER  NOT NULL,    value1 INT NOT NULL INSERT INTO t1 (id_, value1) VALUES (1), (2), (3), (4), (5), (6), (7), (8)  CREATE TABLE IF NOT EXISTS t1 (    id_ INTEGER  NOT NULL,    value1 INT NOT NULL INSERT INTO t2 (id_, value2) VALUES (1), (2), (3), (4)
121058
I want to find out unmatched records of two tables in MySQL.
20783
I have a table that looks like this:  +----------------------------------------+ |Name                | kode      | jum   | +----------------------------------------+ | aman               |kode1      | 2     | | aman               |kode2      | 1     | | jhon               |kode1      | 4     | | amir               |kode2      | 4     | +--------------------+-----------+-------+   How can I make a view like this with MySQL?                      kode1    kode2     count aman                  2         1        3   jhon                  0         4        4 amir                  0         4        4
26778
How can I pivot the following SELECT statement:           SELECT sc.CID,                 sc.CodeName     as OverviewText,                 scRAG.CodeName  as RAGStatusText             FROM StatusCode      sc LEFT OUTER JOIN ProjectOverview po               ON sc.CID          = po.ProjectOverviewCID              AND po.ProjectId    = 180 LEFT OUTER JOIN StatusCode      scRAG               ON po.RAGStatusCID = scRAG.CID           WHERE sc.SCID         = 18   Such statement yields the result set that follows:  CID OverviewText    RAGStatusText --- ------------    ------------- 153 Cost            Green 154 Requirements    Yellow 155 Schedule        NULL 156 Technical       NULL 157 Testing         NULL   But, I want it to return one single row with 10 values, as shown bellow:   ----------------------------------------------------------------------------------------------  | Cost | Green | Requirements | Yellow | Schedule | NULL | Technical | NULL | Testing | NULL |  ----------------------------------------------------------------------------------------------   Can I pivot on CID?
88547
I can select all the distinct values in a column in the following ways:   SELECT DISTINCT column_name FROM table_name; SELECT column_name FROM table_name GROUP BY column_name;   But how do I get the row count from that query? Is a subquery required?
39637
What is the simplest SQL query to find the second largest integer value in a specific column?   There are maybe duplicate values in the column.
67901
I have the following data:        road        |  ID  | other column  | other column |  ------------------+------+---------------+--------------+           MAIN ST           |  1   |     ...       |     ...      | MAIN ST           |  2   |     ...       |     ...      |         MAIN ST           |  3   |     ...       |     ...      | BERRY LN          |  4   |     ...       |     ...      | BERRY LN          |  5   |     ...       |     ...      | BERRY LN          |  6   |     ...       |     ...      | JOHN RD           |  7   |     ...       |     ...      | JOHN RD           |  8   |     ...       |     ...      |   I want all rows with duplicates in the road column removed. It does not matter to me which duplicates are removed. The end result can look something like this:        road        |  ID  | other column  | other column |  ------------------+------+---------------+--------------+           MAIN ST           |  3   |     ...       |     ...      | BERRY LN          |  4   |     ...       |     ...      | JOHN RD           |  8   |     ...       |     ...      |   where the id's are any value, as long as the road column is unique.   Is there a simple way to do this in SQL or T/SQL? I followed the guide , but it only applies to duplicate keys with non-unique rows.
6834
Given a number, how do I discover in what table and column it could be found within? I don't care if it's fast, it just needs to work.
91184
I have following MySQL db structure:  content ======= id | tags --------- 1  | ,abc,def,ghi, 2  | ,abc,foo, 3  | NULL 4  | ,foo,bar,   categories ========== id -- 1 2   categories_to_content ===================== id | category_id | content_id -----------------------------  1 |           1 |          1  2 |           1 |          2  3 |           2 |          3  4 |           2 |          4   What I'm trying to do is to SELECT tags assigned to category and count them. Result for given schema:  tag | category | count ---------------------- abc |        1 |     2 def |        1 |     1 ghi |        1 |     1 foo |        1 |     1 foo |        2 |     1 bar |        2 |     1   Decomposition isn't possible (I hook up into existing CMS database).  My existing query:  SELECT tags, category_id as category FROM content JOIN categories_to_content ON content_id = content.id
56409
I have found a query to find the nth highest salary from Employee table , but i don't understand the logic of (N-1)?  EmpID         Salary 1             90000 2             80000 3             54000 4             37000 5             12000 6             69000 7             50000  SELECT * FROM Employee E1 WHERE (N-1) = (                 SELECT COUNT(DISTINCT(E2.Salary))                 FROM Employee E2                 WHERE E2.Salary &gt; E1.Salary               )   If N= 4, then how does the query work? I'm a complete beginner in SQL, please help!
18391
I'm trying to write a query that extracts and transforms data from a table and then insert those data into another table. Yes, this is a data warehousing query and I'm doing it in MS Access. So basically I want some query like this:  INSERT INTO Table2(LongIntColumn2, CurrencyColumn2) VALUES   (SELECT LongIntColumn1, Avg(CurrencyColumn) as CurrencyColumn1 FROM Table1 GROUP BY LongIntColumn1);   I tried but get a syntax error message.  What would you do if you want to do this?
36179
I am reviewing take aways. I am having trouble  How do I do $342 - 58$?  For the ones column I made the $2$ into a $12$ so I can do $12 - 8 = 4$ but I must take away one tens. So I get $3 - 5$ in tens column but I cant do $3 - 5$. What do I do now do I borrow something else?
103055
Please look at this code:  create table #t1(   id int identity (1,1),   val varchar(10) );   insert into #t1 values ('a'); insert into #t1 values ('b'); insert into #t1 values ('c'); insert into #t1 values ('d');   Now, whenever you execute this  select *,      ( select top 1 val from #t1 order by NEWID()) rnd  from #t1 order by 1;   you will get a result with where all rows have the same random value. e.g.  id          val        rnd ----------- ---------- ---------- 1           a          b 2           b          b 3           c          b 4           d          b   I know a way using a cursor to loop throw the rows and get different random values, but that is not performant.   A clever solution to this is   select t1.id, t1.val, t2.val from #t1 t1     join (select *, ROW_NUMBER() over( order by NEWID()) lfd from #t1) as t2 on  t1.id = t2.lfd    But I simplified the query. The real query looks more like  select *,      ( select top 1 val from t2 where t2.x &lt;&gt; t1.y order by NEWID()) rnd  from t1 order by 1;   and the simple solution doesn't fit. I'm looking for a way to force repeated evaluation of  ( select top 1 val from #t1 order by NEWID()) rnd    without the use of cursors.  Edit: Wanted output:  perhaps 1 call  id          val        rnd ----------- ---------- ---------- 1           a          c 2           b          c 3           c          b 4           d          a   and a second call  id          val        rnd ----------- ---------- ---------- 1           a          a 2           b          d 3           c          d 4           d          b   The value for each row just should be a random value independent from the other rows  Here is the cursor version of the code:  CREATE TABLE #res ( id INT, val VARCHAR(10), rnd VARCHAR(10));  DECLARE @id INT DECLARE @val VARCHAR(10) DECLARE c CURSOR FOR SELECT id, val FROM #t1 OPEN c FETCH NEXT FROM c INTO @id, @val WHILE @@FETCH_STATUS = 0 BEGIN     INSERT INTO #res     SELECT @id, @val, ( SELECT TOP 1 val FROM #t1 ORDER BY NEWID()) rnd      FETCH NEXT FROM c INTO @id, @val END CLOSE c DEALLOCATE c  SELECT * FROM #res
71622
I need to do a join with a table/result-set/whatever that has the integers n to m inclusive. Is there a trivial way to get that without just building the table?  (BTW what would that type of construct be called, a "Meta query"?)  m-n is bounded to something reasonable ( &lt; 1000's)
28370
I have a table similar to this:  CREATE TABLE example (   id integer primary key,   name char(200),   parentid integer,   value integer);   I can use the parentid field to arrange data into a tree structure.  Now here's the bit I can't work out. Given a parentid, is it possible to write an SQL statement to add up all the value fields under that parentid and recurse down the branch of the tree ?  UPDATE: I'm using posgreSQL so the fancy MS-SQL features are not available to me. In any case, I'd like this to be treated as a generic SQL question.  BTW, I'm very impressed to have 6 answers within 15 minutes of asking the question! Go stack overflow!
10446
How do you return 1 value per row of the max of several columns:  TableName  [Number, Date1, Date2, Date3, Cost]   I need to return something like this:  [Number, Most_Recent_Date, Cost]   Query?
95576
Supposing we have a table with four columns (a,b,c,d) of the same data type.  Is it possible to select all distinct values within the data in the columns and return them as a single column or do I have to create a function to achieve this?
53630
If I run this code:  select 99999 * 9999   result = 999890001  If I run this code:  select 9999923 * 99999999   result:     Msg 8115, Level 16, State 2, Line 1   Arithmetic overflow error converting expression to data type int.   And If I run the following code:  select 922337299987987689745893 * 999999994564   result = 922337294974162127011191918541325652  Why am I getting this arithmetic overflow error within a specific range?