I'm getting error while running the following query. I think it's the proper syntax. Please help.  SELECT * from TESTTABLE1 ORDER BY USER_ID WHERE rownum&gt;=100 AND rownum&lt;=200
13936
Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select *  from sometable order by name limit 20,10  to get the 21st to the 30th rows (skip the first 20, give the next 10). The rows are selected after the order by, so it really starts on the 20th name alphabetically. In Oracle, the only thing people mention is the rownum pseudo-column, but it is evaluated before order by, which means this: select *  from sometable where rownum &lt;= 10 order by name  will return a random set of ten rows ordered by name, which is not usually what I want. It also doesn't allow for specifying an offset.
103971
CREATE TABLE plan    (       id                VARCHAR(30) NOT NULL,       name              VARCHAR(20),       amount            INT,       interval          INT,       currency          CHAR(3),       object            VARCHAR(20),       livemode          BOOLEAN,       interval_count    INT,       trial_period_days INT,       created           TIMESTAMP,       updated           TIMESTAMP,       deleted           TIMESTAMP,       PRIMARY KEY (id)    );    Produces   'ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'interval          INT,       currency          CHAR(3),       object            ' at line 6'   Doesn't work. However if the 'l' at the end of 'interval' is removed. It works.  CREATE TABLE plan    (       id                VARCHAR(30) NOT NULL,       name              VARCHAR(20),       amount            INT,       interva           INT,       currency          CHAR(3),       object            VARCHAR(20),       livemode          BOOLEAN,       interval_count    INT,       trial_period_days INT,       created           TIMESTAMP,       updated           TIMESTAMP,       deleted           TIMESTAMP,       PRIMARY KEY (id)    );    I don't understand if this is a problem with attempting to use 'interval' and 'interval_count'. What's going on here.
1458
I tried running the following statement:  INSERT INTO VOUCHER (VOUCHER_NUMBER, BOOK_ID, DENOMINATION) SELECT (a.number, b.ID, b.DENOMINATION)  FROM temp_cheques a, BOOK b WHERE a.number BETWEEN b.START_NUMBER AND b.START_NUMBER+b.UNITS-1;   which, as I understand it, should insert into VOUCHER each record from temp_cheques with the ID and DENOMINATION fields corresponding to entries in the BOOK table (temp_cheques comes from a database backup, which I'm trying to recreate in a different format). However, when I run it, I get an error:  Error: Operand should contain 1 column(s) SQLState:  21000 ErrorCode: 1241   I'm running this in SQuirrel and have not had issues with any other queries. Is there something wrong with the syntax of my query?  EDIT:  The structure of BOOK is:  ID  int(11) START_NUMBER    int(11) UNITS   int(11) DENOMINATION    double(5,2)   The structure of temp_cheques is:  ID  int(11) number  varchar(20)
19638
I am trying to INSERT INTO a table using the input from another table. Although this is entirely feasible for many database engines, I always seem to struggle to remember the correct syntax for the SQL engine of the day (, , , , and ).  Is there a silver-bullet syntax coming from an SQL standard (for example, ) that would allow me to insert the values without worrying about the underlying database?
49941
I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases:    SQL Server MySQL PostgreSQL SQLite Oracle   I am currently doing something like the following in SQL Server 2005, but I'd be interested in seeing other's more agnostic approaches:  WITH Ordered AS ( SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNumber, OrderID, OrderDate FROM Orders) SELECT * FROM Ordered WHERE RowNumber = 1000000   Credit for the above SQL:   Update: See  regarding the SQL standard. Troels, have you got any links we can cite?
57472
I would like to construct a query that displays all the results in a table, but is offset by 5 from the start of the table. As far as I can tell, MySQL's LIMIT requires a limit as well as an offset. Is there any way to do this?
93054
I have a person table. I also have a car table and books table.  The car and book tables both have a foreign key back to person.  I am trying to count the number of books and cars each person has. I tried:  SELECT      Person.Name,      COUNT(BooK.bookid) books,      COUNT(Car.CarId) cars  FROM Person, Book, Car  WHERE Person.ID = Book.PersonID = Car.PersonID    but I get:  Incorrect syntax near '='
120449
I am getting this error with the code below. I've looked into the other cases on here. In my case I am referring to a relationship name (__r) which I am assuming is pulling correctly. It's a field which is a lookup to User :   Agreement_CSH_to_be_completed_by__r  Here is the code which is giving me problems :   public void doManagedClients() {         addMsg('Starting processing of Managed Clients.');         Integer startSize = CSH_Data.size();          String SOQL = 'Select Id, OwnerId, Owner.UserName, Account__c, RecordTypeId, Name, Agreement_Client_Status_History__c ' +             'From Contract_Terms__c Where ' +             '   ( recordtypeid = \''+ ((String)A_N_Rectype).substring(0,15) + '\' ) and ' +                '   Agreement_Client_Status_History__c = \'Monthly\' ' +             'order by createddate desc nulls first' ; system.debug('#####333#### ');               addMsg(DEBUG,'Managed Query is: ' + SOQL + '\n');         for (Contract_Terms__c a: Database.Query(SOQL)) {             if (!a.Agreement_CSH_to_be_completed_by__r.isActive) {                 totalInactiveOwners++;                  addMsg('Account: ' + a.Name +  ', has an inactive owner.  Username: ' + a.Agreement_CSH_to_be_completed_by__r.isActive + '.');             } else {                             totalManagedCSHs++;                 addMsg(DEBUG,'Creating a Managed CSH for Account: ' + a);                 CSH_Data.add(new CSH_and_Share_Pair(a, createClientStatusHistory(a)));                 needflush(false);                 if (error) break;             }         }         needflush(true);         addMsg('End of Managed Clients.');           }   Is it just because I don't have a.Agreement_CSH_to_be_completed_by__r included in the SOQL ? I thought I got different errors for that, so I'm a little confused.  Thank you for your help.
37040
I've been reading the documentatoin on  and also studied the api description for  and  but I fail to create a $query with 3 -&gt;conditions:   select all between 0 and 100 but exclude 23, 48 and 97 select also 107, 123, 126 and 128   That's what I have tried so far:  $query-&gt;db_or()   -&gt;condition('my_field', array(107, 123, 126, 128), 'IN')   -&gt;condition(db_and()-&gt;condition('my_field', array(0, 100), 'BETWEEN')                       -&gt;condition('my_field', array(23, 48, 97), 'NOT IN'));   But I get the following error:  PHP Fatal error:  Call to undefined method SelectQuery::db_or() in [... first line]   I've tried to play around with the syntax like that:  $query-&gt;condition(-&gt;db_or()   -&gt;condition('my_field', array(107, 123, 126, 128), 'IN')   -&gt;condition(db_and()-&gt;condition('my_field', array(0, 100), 'BETWEEN')                       -&gt;condition('my_field', array(23, 48, 97), 'NOT IN')));   But again getting errors like that:  PHP Parse error:  syntax error, unexpected T_OBJECT_OPERATOR, expecting ')' in [... first line]   I also tried working with variables:  $cond01 = array('my_field', array(107, 123, 126, 128), 'IN'); $cond02 = array('my_field', array(0, 100), 'BETWEEN'); $cond03 = array('my_field', array(23, 48, 97), 'NOT IN'); $db_and = db_and(); $db_and-&gt;condition($cond02); $db_and-&gt;condition($cond03); $db_or = db_or(); $db_or-&gt;condition($cond01); $db_or-&gt;condition($db_and); $query-&gt;condition($db_or);   But this again yields drupal errors like that:  SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Array' in 'where clause'   How to create a complex conditional database query with both AND and OR statements?
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.
25708
I thought that the quotation mark (") was simply a type of grouping marker but I'm debugging some NHibernate code and notice that while  SELECT * FROM site WHERE site_id = 3;   Works fine  SELECT * FROM "site" WHERE site_id = 3;   fails with a table or view does not exist error.  What gives?
17701
I wounder how I could use an alias in a where statement.  Example :  SELECT SUBSTRING(Column1, 1, 4) + SUBSTRING(Column1, 4, 3)  AS Col1 FROM MyTable WHERE Col1 = 'MySearch'   I use MSSQL 2005
30023
Why would someone use WHERE 1=1 AND &lt;conditions&gt; in a SQL clause (Either SQL obtained through concatenated strings, either view definition)  I've seen somewhere that this would be used to protect against SQL Injection, but it seems very weird.  If there is injection WHERE 1 = 1 AND injected OR 1=1 would have the same result as injected OR 1=1.  Later edit: What about the usage in a view definition?    Thank you for your answers.  Still, I don't understand why would someone use this construction for defining a view, or use it inside a stored procedure.  Take this for example:  CREATE VIEW vTest AS SELECT FROM Table WHERE 1=1 AND table.Field=Value
45618
I was browsing through the questions and noticed this:  SELECT prodid, issue FROM Sales  WHERE custid = @custid  AND datesold = SELECT MAX(datesold)               FROM Sales s               WHERE s.prodid = Sales.prodid                   AND s.issue = Sales.issue                   AND s.custid = @custid   I was wondering what the "@" does in front of custID?  Is it just a way of referencing the custID from the table being selected?
52560
I am getting "Error System.QueryException: List has no rows for assignment to SObject".   Line 1: Account ac = [select id from Account where name='testaccount' limit 1] Line 2: if(ac != null) {           //do something         }   I got the above error in line 2 if there is no record.   Any help?
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?
14497
I have an SQL select, that gives two rows back. The SQL update gives the error message 1093 back;     mysqli error: [1093: Table 'HD_TICKET' is specified twice, both as a   target for 'UPDATE' and as a separate source for data]   update HD_TICKET, HD_STATUS as T5     set HD_TICKET.HD_STATUS_ID = T5.ID,  HD_TICKET.TIME_OPENED  = IF(T5.STATE = 'opened', NOW(), HD_TICKET.TIME_OPENED),  HD_TICKET.TIME_CLOSED  = IF(T5.STATE = 'closed', NOW(), HD_TICKET.TIME_CLOSED),  HD_TICKET.TIME_STALLED = IF(T5.STATE = 'stalled', NOW(), HD_TICKET.TIME_STALLED),  HD_TICKET.SATISFACTION_RATING = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_RATING),  HD_TICKET.SATISFACTION_COMMENT = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_COMMENT),  HD_TICKET.TITLE = (SELECT NAME FROM ORG1.ASSET WHERE ORG1.ASSET.ID = (SELECT ASSET_ID FROM HD_TICKET WHERE HD_TICKET.ID = HD_TICKET.PARENT_ID))   where T5.NAME = 'New' and  HD_TICKET.HD_QUEUE_ID = T5.HD_QUEUE_ID and     (HD_TICKET.ID in (&lt;TICKET_IDS&gt;))   Can anybody help me, please? Thanks!