I have a file SRQ which is having 10000 SRQ_ID which are unique. I have one table(TABLE1) which is having 2 columns namely SRQ_ID,WORK_ID . I needs to write a query which will search the table(TABLE1) for all the SRQ_ID's in the file SRQ and will display the output with corresponding WORK_ID.  I tried the below code. But IN clause is only applicable for 1000 records. How to run the same if I have 100k records?  select WO_ID  from TABLE1  where SRQ_ID in ('B6512DF0','5838FABC','EC5D804C','074DD65C')
96289
Is there any way to get around the Oracle 10g limitation of 1000 items in a static IN clause?  I have a comma delimited list of many of IDs that I want to use in an IN clause,  Sometimes this list can exceed 1000 items, at which point Oracle throws an error.  The query is similar to this...  select * from table1 where ID in (1,2,3,4,...,1001,1002,...)
31050
I need to run a query like:  SELECT p.id, p.name,         (SELECT name            FROM sites s           WHERE s.id = p.site_id) AS site_list   FROM publications p   But I'd like the sub-select to return a comma separated list, instead of a column of data.  Is this even possible, and if so, how?
65404
I have a large amount of duplicate records (25.000) in an even larger table (250.000 records). I've done the detection using the reporting function. The criteria I used to detect the duplicates were 3 fields (unique account identifier, unique user identifier and date). Based on a list of criteria, I would like to either merge or delete these records. I've got a handle on the logic associated with the merging and the deleting but what I'm having trouble with is defining the duplicate detection process.   I'm looking at either creating a custom object to take care of the detection or dumping the records into an SQL database and taking care of it offline. Any thoughts on which one of the two is the best approach?  Edit:  After reading the first comment, I think the more appropriate question would be "How do I approach the problem in SOQL".  The object I'm working with is the Event object and I'm really only looking at the standard fields, AccountId, OwnerId and ActivityDate. From what I've found online, there's no easy way to find records that have the same value for these three fields. I think it could be accomplished by nesting SOQL queries but I'd like to use a different approach if at all possible.
9915
Query 1:  select count(UserID) from users where UserID IN (select UserID from otherTable)   Returns: 178  Query 2:: (simply changed IN to NOT IN)  select count(UserID) from users where UserID NOT IN (select UserID from otherTable)   Returns: 0  Query 3: (remove the IN-clause completely)  select count(UserID) from users   Returns: 1047123 (over a million)  What? I can't... Even...   The only explanation I have is my database in inconsistent/corrupted, I have to run DBCC CHECKDB or something.  PS. UserID is a primary key, so - no dupes.
49893
Found the following in an Oracle-based application that we're migrating (generalized):  SELECT     Table1.Category1,     Table1.Category2,     count(*) as Total,     count(Tab2.Stat) AS Stat FROM Table1, Table2 WHERE (Table1.PrimaryKey = Table2.ForeignKey(+)) GROUP BY Table1.Category1, Table1.Category2   What does (+) do in a WHERE clause? I've never seen it used like that before.
81999
What's the most efficient way to select the last n number of rows in a table using mySQL? The table contains millions of rows, and at any given time I don't know how large the table is (it is constantly growing). The table does have a column that is automatically incremented and used as a unique identifier for each row.
3396
How do I parameterize a query containing an IN clause with a variable number of arguments, like this one?  SELECT * FROM Tags  WHERE Name IN ('ruby','rails','scruffy','rubyonrails') ORDER BY Count DESC   In this query, the number of arguments could be anywhere from 1 to 5.  I would prefer not to use a dedicated stored procedure for this (or XML), but if there is some elegant way specific to , I am open to 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.
53567
I have a query which is meant to show me any rows in table A which have not been updated recently enough. (Each row should be updated within 2 months after "month_no".):  SELECT A.identifier      , A.name      , TO_NUMBER(DECODE( A.month_no              , 1, 200803               , 2, 200804               , 3, 200805               , 4, 200806               , 5, 200807               , 6, 200808               , 7, 200809               , 8, 200810               , 9, 200811               , 10, 200812               , 11, 200701               , 12, 200702              , NULL)) as MONTH_NO      , TO_NUMBER(TO_CHAR(B.last_update_date, 'YYYYMM')) as UPD_DATE   FROM table_a A      , table_b B  WHERE A.identifier = B.identifier    AND MONTH_NO &gt; UPD_DATE   The last line in the WHERE clause causes an "ORA-00904 Invalid Identifier" error. Needless to say, I don't want to repeat the entire DECODE function in my WHERE clause. Any thoughts? (Both fixes and workarounds accepted...)
52107
When I run the following code it takes 22.5 minutes and does 106million reads. However, if I run just the inner select statement by itself it only takes 15 seconds and does 264k reads. As a side note, the select query returns no records.  Any idea why the IF EXISTS would make it run so much longer and do so many more reads? I also changed the select statement to do SELECT TOP 1 [dlc].[id] and I killed it after 2 minutes.   As a temporary fix I have changed it to do a count( * ) and assign that value to a variable @cnt. Then it does an IF 0 &lt;&gt; @cnt statement. But I thought EXISTS would be better, because if there were records returned in the select statement it would stop performing the scan/seeks once it found at least one record, whereas the count(*) will complete the full query. What am I missing?  IF EXISTS    (SELECT [dlc].[ID]    FROM TableDLC [dlc]    JOIN TableD [d]    ON [d].[ID] = [dlc].[ID]    JOIN TableC [c]    ON [c].[ID] = [d].[ID2]    WHERE [c].[Name] &lt;&gt; [dlc].[Name]) BEGIN    &lt;do something&gt; END
49890
I'm currently trying to create a View from a Table that does the following:  Table A ╔═════════════╦═══════════════╗  ║ PART NUMBER ║   FILE NAME   ║ ╠═════════════╬═══════════════╣ ║  PN0001-01  ║      FOO      ║ ║  PN0002-01  ║      BAR      ║ ║  PN0003-01  ║    FOO,BAR    ║ ║  PN0004-01  ║  BAR,FOO,FOB  ║ ╚═════════════╩═══════════════╝   To create  View B ╔═════════════╦═══════════════╗  ║ PART NUMBER ║   FILE NAME   ║ ╠═════════════╬═══════════════╣ ║  PN0001-01  ║      FOO      ║ ║  PN0002-01  ║      BAR      ║ ║  PN0003-01  ║      FOO      ║ ║  PN0003-01  ║      BAR      ║ ║  PN0004-01  ║      BAR      ║ ║  PN0004-01  ║      FOO      ║ ║  PN0004-01  ║      FOB      ║ ╚═════════════╩═══════════════╝   So, essentially, I need to take records from Table A and insert them into View B, where if the record from A has a LIKE '[,]' property, then that field is split into multiple records in B, depending on the number of times the ',' is present, with the text between each ',' being given its own record in View B.
39428
I'm looking to find records in a table that match a specific number that the user enters.  So, the user may enter 12345, but this could be 123zz4-5 in the database. I imagine something like this would work, if PHP functions worked in MySQL. SELECT * FROM foo WHERE preg_replace(&quot;/[^0-9]/&quot;,&quot;&quot;,bar) = '12345'  What's the equivalent function or way to do this with just MySQL? Speed is not important.
82706
What is the equivalent of varchar(max) in MySQL?
17367
Having trouble indexing and executing a query in O (log n) time.  The query includes an INNER JOIN, an ORDER BY, and an equality operation. If I understand the laws of databases correctly, a query can be indexed and executed in O (log n) time (or thereabouts) if a non-equality operator is not used on more than one column. In this case, I believe the INNER JOIN does count as an equality operator and the non-equality operator would be the ORDER BY portion of the query. This table has upwards of 10,000,000 rows and needs to handle several reads and writes per second.  Using PostgreSQL. This is what the table looks like. As you can see, the column 'Names' is a list property and it is the column that the INNER JOIN goes against:  Age Names                       Date 34  ['carla', 'john', 'sam']    3/13/2011 26  ['json', 'cindy', 'joel']   3/13/2011 72  ['beth', 'amber', 'susie']  3/13/2011 14  ['john', 'jim', 'debie']    3/13/2011   This is the query that we are trying to do:  SELECT * FROM the_table WHERE Age==26 AND Names=='john' ORDER BY Date   My background is from using App Engine's Big Table, so I've used equality operators here to indicate that 'john' should be one of the names in the Names column. This would be an acceptable query in GAE's big table, it would execute in O (log N) time as all Big Table queries are reqyured to do. I am assuming there is a way to do this in PostgreSQL as well since PostgreSQL accepts list data types as columns.  Is this possible to do in PostgreSQL?  If so, how should the index be set up (we can't figure out how to set up an index that takes into account the three properties)?
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?
115369
I am trying to accomplish the following in MySQL (see pseudo code)  SELECT DISTINCT gid FROM `gd` WHERE COUNT(*) &gt; 10 ORDER BY lastupdated DESC   Is there a way to do this without using a (SELECT...) in the WHERE clause because that would seem like a waste of resources.
74456
I want to write a query like this:  SELECT o.OrderId, MAX(o.NegotiatedPrice, o.SuggestedPrice) FROM Order o   But this isn't how the MAX function works, right?  It is an aggregate function so it expects a single parameter and then returns the MAX of all rows.     Does anyone know how to do it my way?