I have this query and i need to get the maxvalue of the CCSEQ camp, i have tried using this query but it doesn´t work, if anyone can help me i will be very grateful.  the query  select max(cc.CCSEQ), cc.ccline4, cc.ccstreetno, cccity from ccontact cc where customer_id = '724609' and ccbill = 'X';   EDIT  I have resolved the issue with this query  select cc.ccline4, cc.ccstreetno, cccity from ccontact cc where CC.customer_id = '724609' and CC.ccbill = 'X' AND cc.CCSEQ = (SELECT MAX(C1.CCSEQ) FROM ccontact c1                         WHERE CC.customer_id = C1.customer_id)   Best wishes
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.
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...)
55154
I'm sure its a simple question and I suppose it was asked many times, but I just can't figure it out from other answers, sorry.  I use recent versions of PostgreSQL and MySQL. I have 2 tables:  CREATE TABLE authors (     id INT,     name VARCHAR )  CREATE TABLE posts (     id INT,     author_id INT,     text VARCHAR,     date DATE )   I need to select one most recent post for each author. Thanks!  UPDATE  Thanks, both links provide answers to my question with some exception. All the following queries give the same result (which one is the most efficient btw?) The issue is when there is more than one post from the same author with the same date. Then the returned result set contains all such posts. How should I modify these queries to return exactly one post per author?  SELECT p1.* FROM posts p1 LEFT JOIN posts p2 ON p1.author_id = p2.author_id AND p1.date &lt; p2.date WHERE p2.author_id IS NULL ORDER BY p1.author_id;  SELECT p1.*  FROM posts p1 INNER JOIN (   SELECT author_id, MAX(date) AS max_date   FROM posts   GROUP BY author_id) p2   ON p1.author_id = p2.author_id AND p1.date = p2.max_date ORDER BY p1.author_id;  SELECT * FROM posts p1 WHERE date = (SELECT MAX(p2.date)               FROM posts p2               WHERE p1.author_id = p2.author_id) ORDER BY author_id;  SELECT * FROM (     SELECT author_id, MAX(date) date     FROM posts GROUP BY author_id ) p1 INNER JOIN posts p2 USING (author_id, date) ORDER BY author_id;
15587
I have a query to insert a row into a table, which has a field called ID, which is populated using an AUTO_INCREMENT on the column. I need to get this value for the next bit of functionality, but when I run the following, it always returns 0 even though the actual value is not 0:  MySqlCommand comm = connect.CreateCommand(); comm.CommandText = insertInvoice; comm.CommandText += "\'" + invoiceDate.ToString("yyyy:MM:dd hh:mm:ss") + "\', " + bookFee + ", " + adminFee + ", " + totalFee + ", " + customerID +  ")"; int id = Convert.ToInt32(comm.ExecuteScalar());   According to my understanding, this should return the ID column, but it just returns 0 every time. Any ideas?  EDIT:  When I run:  "INSERT INTO INVOICE (INVOICE_DATE, BOOK_FEE, ADMIN_FEE, TOTAL_FEE, CUSTOMER_ID) VALUES ('2009:01:01 10:21:12', 50, 7, 57, 2134);last_insert_id();"   I get:  {"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 'last_insert_id()' at line 1"}
43671
I have written below inner join query to get the data from my database tables. But  output of this data is coming up with almost 140+ lines. Output is below. I need data in only one row with the most recent value by "TimeSampled"    select DisplayName, ObjectName, CounterName, InstanceName, SampleValue, max(TimeSampled) as TimeSampled from PerformanceDataAllView pdv with (NOLOCK) inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId where ObjectName = 'Processor'  AND TimeSampled &gt;= DATEADD(MINUTE,-5,GETDATE()) AND DisplayName Like 'BLRW1ETST01.jcp.com' AND CounterName = '% Processor Time' GROUP BY DisplayName, ObjectName, CounterName, InstanceName, SampleValue ORDER BY  TimeSampled desc   How I can retrive results with only last Time Sampled row with respect to DisplayName. I have multiple servers (DisplayName) like this to retrieve the data.  Thanks, Ramu Chittiprolu
14862
I am trying to query two tables and get results like the following:  Section    Names shoes      AccountName1, AccountName2, AccountName3 books      AccountName1   The tables are:  CREATE TABLE dbo.TableA(ID INT, Section varchar(64), AccountId varchar(64));  INSERT dbo.TableA(ID, Section, AccountId) VALUES (1 ,'shoes','A1'), (2 ,'shoes','A2'), (3 ,'shoes','A3'), (4 ,'books','A1');  CREATE TABLE dbo.TableB(AccountId varchar(20), Name varchar(64));  INSERT dbo.TableB(AccountId, Name) VALUES ('A1','AccountName1'), ('A2','AccountName2'), ('A3','AccountNAme3');   I saw a few questions answered saying to use "XML PATH" and "STUFF" to query the data to get the results I am looking for, but I think there is something missing.  I have tried the below query and get the error message:     Column 'a.AccountId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.   I don't have it in the SELECT clause of either query, but I assume the error is because AccountId is not unique in TableA.  Here is the query I am currently trying to get working correctly.  SELECT section, names= STUFF((     SELECT ', ' + Name FROM TableB as b  WHERE AccountId = b.AccountId FOR XML PATH('')), 1, 1, '') FROM TableA AS a GROUP BY a.section
93357
My Trigger will send an email anytime one account has 7 cases created within 8 days. Towards the bottom in my Else block, I am trying to access fields in my first SOQL statement. I have tried many ways and read many help pages. Help with the syntax for the line: message.PlainTextBody = 'Account name: ' + cl.get('name') + ' has ' + (Integer)cl.get('co') + ' cases opened in the last 8 days.';`  is greatly appreciated.  List&lt;AggregateResult&gt; AggregateResultList = [SELECT AccountId, Account.Name name, COUNT(Id) co                                          FROM Case                                          WHERE CreatedDate = LAST_N_DAYS:8                                          GROUP BY AccountId, Account.Name                                          HAVING COUNT(Id)  &gt;= 7];                           Set&lt;Id&gt; AcctIds = new Set&lt;Id&gt;();                 for(AggregateResult aggr: AggregateResultList){                      Id accId = (Id)aggr.get('AccountId');                         AcctIds.add(accId);                 }                  List&lt;Case&gt; caseList = [SELECT Id, AccountId, Account.Name,Parent_Project_if_applicable__r.Implementation_status__c,                                                 Parent_Project_if_applicable__r.PM_Implementation_Status__c,                                                  Parent_Project_if_applicable__r.RCM_Implementation_Status__c                                            FROM Case                                        WHERE AccountId IN :AcctIds];             Messaging.SingleEmailMessage message = new Messaging.SingleEmailMessage();             for(Case cl:caseList){                   if(cl.Parent_Project_if_applicable__r.Implementation_status__c == 'Live - Closed Project'){                             //Private method *** getAddresses() *** retrieves email address from Customer_Success_Managers Public Group                              message.toAddresses  = getAddresses();                            }                         else{                                message.toAddresses = new String[] { Parent_Project_if_applicable__r.Client_Advisor_Email__c  };                          }                     message.Subject = 'Subject Test Message';                     message.PlainTextBody = 'Account name: ' + cl.get('name') + ' has ' + (Integer)cl.get('co') + ' cases opened in the last 8 days.';                     Messaging.SingleEmailMessage[] messages =   new List&lt;Messaging.SingleEmailMessage&gt; {message};                     Messaging.SendEmailResult[] results = Messaging.sendEmail(messages);             } private List&lt;String&gt; getAddresses(){ List&lt;User&gt; UserList =         [SELECT id, name, email         FROM User          WHERE id          IN (SELECT userorgroupid              FROM groupmember             WHERE group.name = 'Customer Success Managers')];  List&lt;String&gt; emailString = new List&lt;String&gt;();  for(User u: UserList){     emailstring.add(u.email); }    return (emailString); }
94031
I have a query in a before-update trigger that looks like following:  [SELECT Id,(SELECT Id,Active_opp__c              FROM Opportunities              ORDER BY Days_from_Last_Stage__c ASC)   FROM Account WHERE Id IN: conAccMap.values()]   I have checked this in query plan and it looks like :    Is there a chance that the inner query will cause selective query error since I dont have a WHERE Clause on an indexed field in the inner query?
110197
I have a child form and I want to get the total of the child forms to update the parent with the sum total. I am struggling with the CAML query to get the total back. The below returns zero records at present because (i suspect) the query isn't quite right.          var query = '&lt;Query&gt;&lt;Where&gt;&lt;Eq&gt;&lt;FieldRef Name="Engagement" /&gt;&lt;Value Type="Lookup"&gt;';             query += ParentEngagementID + ";#" + ParentEngagementText + "&lt;/Value&gt;&lt;/Eq&gt;&lt;/Where&gt;&lt;/Query&gt;";           console.log("Query is " + query);         //Checked in chrome this is: Query is &lt;Query&gt;&lt;Where&gt;&lt;Eq&gt;&lt;FieldRef Name="Engagement"&gt;&lt;/FieldRef&gt;&lt;Value Type="Lookup"&gt;106;#ABC Ltd - 31/03/2016 - Year End&lt;/Value&gt;&lt;/Eq&gt;&lt;/Where&gt;&lt;/Query&gt;          var viewFields = '&lt;ViewFields&gt;&lt;FieldRef Name="Budget_x0020_hours" /&gt;&lt;FieldRef Name="Actual_x0020_hours" /&gt;';             viewFields += '&lt;/ViewFields&gt;';          var NewBudgetTotal = 0.00;         var NewActualTotal = 0.00;         $().SPServices({             operation: "GetListItems",             async: false,             listName: "Work tracker - Products",             webURL: "/sites/UKIAsuDataAnalytics",             CAMLViewFields: viewFields,             CAMLQuery: query,             completefunc: function (xData, Status) {                 console.log("---------------------");                 console.log(status);                 console.log(xData.responseText);                 console.log("---------------------");                 $(xData.responseXML).SPFilterNode('z:row').each(function() {                     console.log('Processing list of returned CAML items');                     if($(this).attr("ows_Budget_x0020_hours")) {                         NewBudgetTotal += Number(parseFloat($(this).attr("ows_Budget_x0020_hours").split(";#")[0],2).toFixed(2));                     }                     if($(this).attr("ows_Actual_x0020_hours")) {                         NewActualTotal += Number(parseFloat($(this).attr("ows_Actual_x0020_hours").split(";#")[0],2).toFixed(2));                      }                 });             }         });   If I comment out the query the XML responses coming back are like the below, and you can see the Engagement field should match:      "&lt;z:row ows_Budget_x0020_hours='3.00000000000000' ows_Actual_x0020_hours='0' ows_MetaInfo='80;#' ows__ModerationStatus='0' ows__Level='1' ows_ID='80'      ows_UniqueId='80;#{E91295D7-EF93-4F11-B486-E00F6A6AB7DF}' ows_owshiddenversion='16' ows_FSObjType='80;#0' ows_Created='2016-07-13 10:17:15' ows_PermMask='0x7fffffffffffffff' ows_Modified='2016-08-26 18:58:30'      ows_Engagement='106;#ABC Ltd - 31/03/2016 - Year End' ows_FileRef='80;#sites/MySite/Lists/Work Tracker  Tools/80_.000' /&gt;"   I have tried all sorts of changes to the query for the content of "Engagement" but nothing helps. Any thoughts?  (Please don't blame me on design here - I am trying to patch up some Sharepoint "work" done by a colleague. I am certainly not the best person to do the patchup, as much as they weren't the person do to the work.)
52970
SELECT      Id,     CurrencyIsoCode,      ExpirationDate__c,     (SELECT           Id,          Regional_Starting_Price_2__c       FROM           SBQQ__QuoteLine__c)  FROM      SBQQ__Quote__c   WHERE      Id ='quote_Id'
20411
Question:     Given complex numbers $a,b,c$, we have that $|az^2 + bz +c| \leq 1$ holds true for any complex number $z, |z| \leq 1$. Find the maximum value of $|bc|$   It is said this is answer is $$|bc|\le \dfrac{3\sqrt{3}}{16}$$  My idea: let $z=1$,then $$|a+b+c|\le 1$$ let $z=-1$,then $$|a-b+c|\le 1$$ let $z=0$, then $$|c|\le 1$$  let $|\theta|=1$,then we have $$|a(z/\theta)^2+b(z/\theta)+c|\le 1\Longrightarrow |az^2+b\theta z+c\theta^2|\le 1$$ and only this can't solve this problem,Thank you
107537
Given the table:      Column    |            Type               id           | integer                       latitude     | numeric(9,6)                  longitude    | numeric(9,6)                  speed        | integer                       equipment_id | integer                       created_at   | timestamp without time zone Indexes:     "geoposition_records_pkey" PRIMARY KEY, btree (id)   The table has 20 million records that is not, relatively speaking, a large number. But it makes sequential scans slow.  How can I get the last record (max(created_at))  of each equipment_id?   I've tried both the following queries, with several variants that I've read through many answers of this topic:  select max(created_at),equipment_id from geoposition_records group by equipment_id;  select distinct on (equipment_id) equipment_id,created_at    from geoposition_records order by equipment_id, created_at desc;   I have also tried creating btree indexes for equipment_id,created_at but Postgres finds that using a seqscan is faster. Forcing enable_seqscan = off is of no use either since reading the index is as slow as the seq scan, probably worse.  The query must run periodically returning always the last.  Using Postgres 9.3.  Explain/analyze (with 1.7 million records):  set enable_seqscan=true; explain analyze select max(created_at),equipment_id from geoposition_records group by equipment_id; "HashAggregate  (cost=47803.77..47804.34 rows=57 width=12) (actual time=1935.536..1935.556 rows=58 loops=1)" "  -&gt;  Seq Scan on geoposition_records  (cost=0.00..39544.51 rows=1651851 width=12) (actual time=0.029..494.296 rows=1651851 loops=1)" "Total runtime: 1935.632 ms"  set enable_seqscan=false; explain analyze select max(created_at),equipment_id from geoposition_records group by equipment_id; "GroupAggregate  (cost=0.00..2995933.57 rows=57 width=12) (actual time=222.034..11305.073 rows=58 loops=1)" "  -&gt;  Index Scan using geoposition_records_equipment_id_created_at_idx on geoposition_records  (cost=0.00..2987673.75 rows=1651851 width=12) (actual time=0.062..10248.703 rows=1651851 loops=1)" "Total runtime: 11305.161 ms"
9337
Is there a way to get the (to-be-generated) SQL from a Hibernate Criteria?  Ideally, I would have something like:  Criteria criteria = session.createCriteria(Operator.class);  ... build up the criteria ... ... and then do something like ...  String sql = criteria.toSql()  (But this of course does not exist)   The idea would then be to use the SQL as part of a huge 'MINUS' query (I need to find the differences between 2 identical schemas - identical in structure, not in data - and the MINUS is not supported by Hibernate)  (BTW I know I can check the SQL from the log files)
42901
I'd like to run a query using the EntityFieldQuery object. I need value from both the node and node_access table therefore I'd need to use INNER JOIN. From the d.o documentation I can't figure out how this is possible.  Here's what I have --  $query = new EntityFieldQuery(); $result = $query-&gt;entityCondition('entity_type', 'node') -&gt;entityCondition('bundle', 'node_access') -&gt;propertyCondition('type', 'external_link') -&gt;propertyCondition('status', 1) -&gt;fieldCondition('gid', '3', '=') -&gt;fieldCondition('realm', 'domain_id', '=') -&gt;fieldCondition('grant_view', '1', '&gt;=') -&gt;range(0,1) -&gt;execute();
118112
When I execute SOQL in Developer Console:  Select Id, Amount From Opportunity Where Amount &gt; 10000000000   An parsing error occurs:  Unknown error parsing query   Is there length limit of number on SOQL Where condition?
82262
I'm just getting started with Postgres. Reading  I came across this query:  SELECT title, ts_rank_cd(textsearch, query) AS rank FROM apod, to_tsquery('neutrino|(dark &amp; matter)') query WHERE query @@ textsearch ORDER BY rank DESC LIMIT 10;   I can understand everything in this query, except for this: FROM apod, ....  What does this , mean? I'm used to joins but not to multiple FROM statements separated by a comma.  I searched the net for no avail. After looking at it and thinking, it seems to me that it's declaring a variable called query so it can use it multiple times. But if this is true, what does it have to do with FROM?
44649
What is the best way to get the Max value from a LINQ query that may return no rows? If I just do  Dim x = (From y In context.MyTable _          Where y.MyField = value _          Select y.MyCounter).Max   I get an error when the query returns no rows. I could do  Dim x = (From y In context.MyTable _          Where y.MyField = value _          Select y.MyCounter _          Order By MyCounter Descending).FirstOrDefault   but that feels a little obtuse for such a simple request. Am I missing a better way to do it?  UPDATE: Here's the back story: I'm trying to retrieve the next eligibility counter from a child table (legacy system, don't get me started...). The first eligibility row for each patient is always 1, the second is 2, etc. (obviously this is not the primary key of the child table). So, I'm selecting the max existing counter value for a patient, and then adding 1 to it to create a new row. When there are no existing child values, I need the query to return 0 (so adding 1 will give me a counter value of 1). Note that I don't want to rely on the raw count of child rows, in case the legacy app introduces gaps in the counter values (possible). My bad for trying to make the question too generic.