math functions in active record sql queries? (pow, sqrt etc)
Active Record is not recognizing common SQL functions like POW and SQRT in the "find" method or "find_by_sql." How do I work around this? There seems to be no literature out there :-( ...
query
SELECT item types not provided by an entity
So I've got some data. There are entities. Entities have an arbitrary number of items. Items can be one of a defined set of types. An entity can have more than one item of a given type. I can get a list of items that an entity has. What I want is to get a list of types that an entity doesn't have an item for. Here's my schema: entitie...
Linq query
Hi, I'm a big noob with Linq and trying to learn, but I'm hitting a blocking point here. I have a structure of type: Dictionary<MyType, List<MyObj>> And I would like to query with Linq that structure to extract all the MyObj instances that appear in more than one list within the dictionary. What would such a query look like? Th...
SQL GROUP BY week (Monday 07:00:00 to Monday 06:59:59)
Hi folks, I've created a query that groups production data on ISO week by using this query SELECT to_char(wid_date,'IYYY'), to_char(wid_date,'IW'), tonnes FROM production WHERE to_char(wid_date,'IYYY') = '2009' GROUP BY to_char(wid_date,'IYYY'), to_char(wid_date,'IW') The problem is that our "production weeks" don't follow the ISO ...
sql sub query
Hi I need a small help regarding writing a simple query. I want to display results based on max number of employees in a dept. Here is my table empid dept sal emp001 d001 10000 emp002 d001 10000 emp003 d002 20000 emp004 d001 10000 emp005 ...
select many through many... kind of
Hi all! This is my first post on Stack, so please bear with me if I breach any protocol. I'm working on a project in Rails (2.1.2), and I have a relations scenario that looks like this: event [has_many] days People (in different categories) can sign up for event days, giving the following binding results: category [has_many] custom...
Left and right joining in a query
A friend asked me for help on building a query that would show how many pieces of each model were sold on each day of the month, showing zeros when no pieces were sold for a particular model on a particular day, even if no items of any model are sold on that day. I came up with the query below, but it isn't working as expected. I'm only ...
Insert a part of a table into another table
I have table A and table B, same schemas. I want to insert certain rows from table A into table B. For example, insert into table B all rows from table A with column 'abc' > 10. Couldn't figure out how to do it ...
How do I match an exact number using SharePoint search
In SharePoint (MOSS 2007) search, I need to match an exact number such as 2009482842 and nothing else. How do I get search to only return hits on the exact number? Edit: I originally thought this was only with the SQL FullTextQuery from code, but it is also a problem when searching from the portal itself. Edit: This was fixed by upgra...
Fastest way to loop thru a SQL Query.
What is the fastest way to loop thru a Query in T-SQL . 1) Cursors or 2) Temp tables with Key added or any thing else. ...
Oracle-sqlplus query
Hi All, I am giving a select statement in sqlplus,It is retreving the data but the column name is displayed every time after certain number of rows.I want column name to be displayed only once.For example- select emp_name from employee. output: emp_name ======== raman sunil rajesh dilip emp_name ======== rahul pramod ankita I...
mySQL - Using data from another table.field to determine results in a query.
Hello Hello, Buried in mySQL syntax and query structure problems once more. I'm currently doing this query fine:- SELECT DATE_FORMAT(`when`, '%e/%c/%Y')date , COUNT(`ip`) AddressCount FROM `metrics` WHERE `projID` = '$projID' GROUP BY DATE(`when`) This works fine to find the specific number of ip address's hit. However, what...
Sequence database field and query for updating sort order
I would like to enable users of my application to define a number of (say) email addresses. Then I would like to give them the ability to rearrange these addresses, so the primary address is on top, secondary next, etc. Let's suppose I have a UserEmailAddresses table in a database that links a user (UserId) and an email address (Email...
Solr Query on Unique Integer Field
I have a field defined in schema.xml as: <field name="id" type="integer" indexed="true" stored="true" required="true" /> It is also the uniqueKey for the schema. I cannot perform a query on this field with the query url: /select?q=4525&qf=id&fl=id,name%2Cscore This returns no results, however, if I search on a different field(such...
Problem with JMX query of Coherence node MBeans visible in JConsole
I'm using JMX to build a custom tool for monitoring remote Coherence clusters at work. I'm able to connect just fine and query MBeans directly, and I've acquired nearly all the information I need. However, I've run into a snag when trying to query MBeans for specific caches within a cluster, which is where I can find stats about total nu...
Mysql join syntax
If I want to perform joins on 3 or more tables, what is the best syntax? This is my attempt: Select * from table1 inner join table2 using id1, table2 inner join table3 using id2, table3 inner join table4 using id4 where table2.column1="something" and table3.column4="something_else"; does that look right? The things I'm not sure ...
How to determine position of row in sql result-set?
i have a sql query: select id, name from table order by name result looks like this: 52 arnold 33 berta 34 chris 47 doris 52 emil for a given id=47 how can i determine the position in the result set? the result should be 4 because: 52 arnold 33 berta 34 chris are before (47, doris) and id=41 is on the 4th position in the resu...
Mysql: Select all data between two dates
I have a mysql table with data connected to dates. Each row has data and a date, like this: 2009-06-25 75 2009-07-01 100 2009-07-02 120 I have a mysql query that select all data between two dates. This is the query: "SELECT data FROM tbl WHERE date BETWEEN date1 AND date2" My problem is that I also need to get the rows betwee...
How to retrieve non-matching results in mysql
I'm sure this is straight-forward, but how do I write a query in mysql that joins two tables and then returns only those records from the first table that don't match. I want it to be something like: Select tid from table1 inner join table2 on table2.tid = table1.tid where table1.tid != table2.tid; but this doesn't seem to make alot ...
Efficient algorithm for Next button on a MySQL result set
I have a website that lets people view rows in a table (each row is a picture). There are more than 100,000 rows. You can view different subsets of the rows, and you can view them with different sort orders. While you are viewing one of the rows, you can click the "Next" or "Previous" buttons to go the next/previous row in the list. ...