I have a &quot;sSamples&quot; table which contains UniqueID, sDate and sID-s like:  I need the SID-s by the latest sDate and grouped by UniqueID. Result should look like: UniqueID  |  SID --------------------- 031311579 | 11641325 053600109 | 11641353 066051428 | 11641379 093468114 | 11641350  I tried with different query versions but the perfomance was very slow. What is the best solution for this? Thanks for the answers!
2551
Table:   UserId, Value, Date.   I want to get the UserId, Value for the max(Date) for each UserId. That is, the Value for each UserId that has the latest date. Is there a way to do this simply in SQL? (Preferably Oracle)  Update: Apologies for any ambiguity: I need to get ALL the UserIds. But for each UserId, only that row where that user has the latest date.
120659
Say I have the following query:  SELECT *  FROM AppDetails, AppTags  WHERE AppDetails.AppID = '1'    AND AppDetails.AppID = AppTags.AppID   Which gives the following results:  AppID    AppName        AppType    Tag 1        Application1   Utility    Test1 1        Application1   Utility    Test2 1        Application1   Utility    Test3   How can I modify the query to return something like this:  AppID    AppName        AppType    Tags 1        Application1   Utility    Test1,Test2,Test3
51081
I need to get a set of total transactions over time on a weekly interval. Essentially I need a total-transactions-to-date column. When I group by WEEK(Date) I get the amount of transactions for that week, but need to also get the transactions from any time before that week as well.  So let's say I have a table like this:  TransactionID  | Date ---------------|-----------              1 | 8-04-2014              2 | 8-05-2014              3 | 8-18-2014              4 | 8-18-2014              5 | 8-20-2014   I want to have a select statement that would provide me with something like  TotalTransactionsToDate | Week | Year ------------------------|------|------                       2 |    1 | 2014                       5 |    3 | 2014   I am using MySql 5.5.38
65979
I know I need to have (although I don't know why) a GROUP BY clause on the end of a SQL query that uses any aggregate functions like count, sum, avg, etc:  SELECT count(userID), userName FROM users GROUP BY userName   When else would GROUP BY be useful, and what are the performance ramifications?
119009
How do I take an efficient simple random sample in SQL? The database in question is running MySQL; my table is at least 200,000 rows, and I want a simple random sample of about 10,000. The &quot;obvious&quot; answer is to: SELECT * FROM table ORDER BY RAND() LIMIT 10000  For large tables, that's too slow: it calls RAND() for every row (which already puts it at O(n)), and sorts them, making it O(n lg n) at best. Is there a way to do this faster than O(n)? Note: As Andrew Mao points out in the comments, If you're using this approach on SQL Server, you should use the T-SQL function NEWID(), because RAND() . EDIT: 5 YEARS LATER I ran into this problem again with a bigger table, and ended up using a version of @ignorant's solution, with two tweaks:  Sample the rows to 2-5x my desired sample size, to cheaply ORDER BY RAND() Save the result of RAND() to an indexed column on every insert/update. (If your data set isn't very update-heavy, you may need to find another way to keep this column fresh.)  To take a 1000-item sample of a table, I count the rows and sample the result down to, on average, 10,000 rows with the the frozen_rand column: SELECT COUNT(*) FROM table; -- Use this to determine rand_low and rand_high    SELECT *     FROM table    WHERE frozen_rand BETWEEN %(rand_low)s AND %(rand_high)s ORDER BY RAND() LIMIT 1000  (My actual implementation involves more work to make sure I don't undersample, and to manually wrap rand_high around, but the basic idea is &quot;randomly cut your N down to a few thousand.&quot;) While this makes some sacrifices, it allows me to sample the database down using an index scan, until it's small enough to ORDER BY RAND() again.
13466
I am looking for a way to concatenate the strings of a field within a group by query. So for example, I have a table:  ID   COMPANY_ID   EMPLOYEE 1    1            Anna 2    1            Bill 3    2            Carol 4    2            Dave   and I wanted to group by company_id to get something like:  COMPANY_ID   EMPLOYEE 1            Anna, Bill 2            Carol, Dave   There is a built-in function in mySQL to do this
27629
I have a SQL Server instance that has a linked server to an Oracle server. There is a table on the Oracle server called PersonOptions which contains the following data:  ╔══════════╦══════════╗ ║ PersonID ║ OptionID ║ ╠══════════╬══════════╣ ║        1 ║ A        ║ ║        1 ║ B        ║ ║        2 ║ C        ║ ║        3 ║ B        ║ ║        4 ║ A        ║ ║        4 ║ C        ║ ╚══════════╩══════════╝   I need to pivot that data so the results are:  ╔══════════╦═════════╦══════════╦══════════╗ ║ PersonID ║ OptionA ║ Option B ║ Option C ║ ╠══════════╬═════════╬══════════╬══════════╣ ║        1 ║       1 ║        1 ║          ║ ║        2 ║         ║          ║        1 ║ ║        3 ║         ║        1 ║          ║ ║        4 ║       1 ║          ║        1 ║ ╚══════════╩═════════╩══════════╩══════════╝   Any suggestions?
82651
I have an output of a query as follows:  bid  | code | amount        | ----------------------------- 2915 | BF   |   -10700.00   | 2915 | YQ   |   -300.00     | 2915 | YR   |   0.00        | 2915 | YM   |   0.00        | 2915 | WO   |   -153.00     | 2915 | IN   |   -329.00     | 2915 | K3   |   0.00        | 2915 | CUTE |   -50.00      | -----------------------------   I need to convert the values in code column to individual columns with column name as corresponding code and amount shall be the value.  bid  | BF       | YQ     | ... | CUTE  | ---------------------------------------- 2915 | -10700.00| -300.00| ... | -50.00|   The issue is that the result of my initial query has dynamic codes and hence need to generate columns accordingly.  Already tried the following logic but couldn't achieve the goal:   , ,   All the above links use some expression or the other to generate the columns.   My original resulting rows are result of simple select on multiple joined tables, and has no calculation or expression scope.  Kindly suggest some pointer or hint on how to achieve this.  Many thanks in advance.
21326
comes close to what I need, but my scenario is slightly different. The source table and destination table are the same and the primary key is a uniqueidentifier (guid). When I try this:  insert into MyTable     select * from MyTable where uniqueId = @Id;   I obviously get a primary key constraint violation, since I'm attempting to copy over the primary key. Actually, I don't want to copy over the primary key at all. Rather, I want to create a new one. Additionally, I would like to selectively copy over certain fields, and leave the others null. To make matters more complex, I need to take the primary key of the original record, and insert it into another field in the copy (PreviousId field).  I'm sure there is an easy solution to this, I just don't know enough TSQL to know what it is.
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?
6644
I've got two tables:  TableA ------ ID, Name  TableB ------ ID, SomeColumn, TableA_ID (FK for TableA)   The relationship is one row of TableA - many of TableB.  Now, I want to see a result like this:  ID     Name      SomeColumn  1.     ABC       X, Y, Z (these are three different rows) 2.     MNO       R, S   This won't work (multiple results in a subquery):  SELECT ID,        Name,         (SELECT SomeColumn FROM TableB WHERE F_ID=TableA.ID) FROM TableA   This is a trivial problem if I do the processing on the client side. But this will mean I will have to run X queries on every page, where X is the number of results of TableA.   Note that I can't simply do a GROUP BY or something similar, as it will return multiple results for rows of TableA.   I'm not sure if a UDF, utilizing COALESCE or something similar might work?
64634
I have a table that has a schema like this:  create_table "questions_tags", :id =&gt; false, :force =&gt; true do |t|         t.integer "question_id"         t.integer "tag_id"       end        add_index "questions_tags", ["question_id"], :name =&gt; "index_questions_tags_on_question_id"       add_index "questions_tags", ["tag_id"], :name =&gt; "index_questions_tags_on_tag_id"   I would like to remove records that are duplicates, i.e. they have both the same tag_id and question_id as another record.  What does the SQL look like for that?
118306
I'm trying to convert a single-columned subquery into a command-separated VARCHAR-typed list of values.  This is identical to , but for Oracle rather than SQL Server or MySQL.
2625
What is the best way to remove duplicate rows from a fairly large SQL Server table (i.e. 300,000+ rows)?  The rows, of course, will not be perfect duplicates because of the existence of the RowID identity field.  MyTable  RowID int not null identity(1,1) primary key, Col1 varchar(20) not null, Col2 varchar(2048) not null, Col3 tinyint not null
62987
I have a query where I chain two columns. Why does the alias not work in the GROUP BY clause but in ORDER BY clause, it does work? How can I write the select correctly?  SELECT   KOS_VER_ID        AS "Vertrag"   , WHR_ISO_3_CODE  AS "Waehrung"   , KOS_KOA_ST_KZN || '  -  ' || ST_LANGBEZ_EN as "Kostenart"   , SUM (KOS_BETRAG) AS "Summe pro KOA" FROM   KOSTEN   , WAEHRUNG   , SCHLUESSELTABELLE WHERE    KOSTEN.KOS_VERHI_WHR_ID = WAEHRUNG.WHR_ISO_ID   AND KOSTEN.KOS_KOA_ST_KZN = SCHLUESSELTABELLE.ST_ID   AND KOS_VER_ID in (2509, 2510, 2511) GROUP BY   KOS_VER_ID   , WHR_ISO_3_CODE   , KOS_KOA_ST_KZN || '  -  ' || ST_LANGBEZ_EN ORDER BY   "Vertrag"   , "Kostenart" ;
48546
I've got some customer_comments split out into multiple rows due to database design, and for a report I need to combine the comments from each unique id into one row. I previously tried something working with this  trick but I can't recall it and must not have saved it. I can't seem to get it to work in this case either, only seems to work on a single row.  The data looks like this:  id  row_num  customer_code comments ----------------------------------- 1   1        Dilbert        Hard 1   2        Dilbert        Worker 2   1        Wally          Lazy   My results need to look like this:  id  customer_code comments ------------------------------ 1   Dilbert        Hard Worker 2   Wally          Lazy   So for each row_num there's really only one row of results; the comments should be combined in the order of row_num. The above linked SELECT trick works to get all the values for a specific query as one row, but I can't figure out how to make it work as part of a SELECT statement that spits all these rows out.   My query has to go through the whole table on its own and output these rows. I'm not combining them into multiple columns, one for each row, so PIVOT doesn't seem applicable.
118397
I have a large MyISAM table A (500m entries) that connects two other tables B,C with a n:m relation.  So my table A looks like this:  CREATE TABLE `A` (   `id_b` int(10) DEFAULT NULL,   `id_c` int(12) DEFAULT NULL,   UNIQUE KEY `Uniqueness` (`id_c`,`id_b`),   KEY `id_b` (`id_b`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;   Every entity in B is related to 1-12 entities in C with an average of 8 and a mode of 10.  Every entitiy in C is related to 100-500 entities in B (but this is linearly growing over time).  Now for a specific entitiy c I want a list of all other entities in C that it is connected to through B and how many entities in B they are connect through. Or more directly: I have a specific c, that is connected 100-500 b's which are each connected to a handful of other entities in C. I want to know which C's the b's are connect with, and how often.  My simplified query looks like this:  SELECT COUNT(OtherA.id_b), OtherA.id_c FROM A INNER JOIN A OtherA ON OtherA.id_b = A.id_b WHERE A.id_c=[MY_SPECIFIC_C_ID] GROUP BY OtherA.id_c   And takes over a second to execute. As described above, the inner join yields at max a few thousand results and the group reduces it to a few hundred. As far as I can tell I indexed correctly, so I am confused why this query is taking so long.  EXPLAIN yields the following result:  row1 id--------------1 select_type-----SIMPLE table-----------A type------------ref possible_keys---Uniqueness,id_b key-------------Uniqueness key_len---------5 ref-------------const rows------------462 Extra-----------Using where; Using index; Using temporary; Using filesort row2 id--------------1 select_type-----SIMPLE table-----------OtherA type------------ref possible_keys---id_b key-------------id_b key_len---------5 ref-------------THISDB.A.id_b rows------------8 Extra-----------Using where   As suggested I have tried manually extracting the list of id_b's:  [concated_list] = select group_concat(distinct id_b) as id_list from A where id_c = ...;   And then running  SELECT count(id_b), id_c FROM A WHERE id_b IN([concated_list]) GROUP BY id_c   The first query is instant, the second query takes almost as long as the original query, so it was suggested that I need to fine-tune my MySQL Vars. Where do I start?  EXPLAIN SELECT count(id_b), id_c FROM A WHERE id_b IN([concated_list]) GROUP BY id_c  results in:  id--------------1 select_type-----SIMPLE table-----------A type------------range possible_keys---id_b key-------------id_b key_len---------5 ref-------------NULL rows------------802 Extra-----------Using where; Using temporary; Using filesort   MySQL version is 5.5.35-0ubuntu0.12.04.2.