query

SQL to delete the oldest records in a table

I'm looking for a single SQL query to run on an oracle table that will retain n number of records in a table and delete the rest I tried the following delete from myTable where pk not in (SELECT pk FROM myTable where rownum <5 order by created DESC) But it appears that I cannot have order by in the nested select. Any help appreci...

Help with a SQL Query

Hey Guys, I'm looking for a SQLite query which will return values that are great or less than 0.014763 for the lng and 0.008830 for the lat. I'm using the following code... $latupp = $_REQUEST['currlat'] + 0.008830; $latlow = $_REQUEST['currlat'] - 0.008830; $lngupp = $_REQUEST['currlng'] + 0.014763; $lnglow = $_REQUEST['currlng']...

query for what customers have bought together with the listed product

hi guys, i'm trying to get optimize an very old query that i can't wrap my head around. the result that i want to archive is that i want to recommend the visitor on a web shop what other customers have shown interest in, i.e. what else they have bought together with the product that the visitor is looking at. i have a subquery but it's ...

Oracle select query performance

I am working on a application. It is in its initial stage so the number of records in table is not large, but later on it will have around 1 million records in the same table. I want to know what points I should consider while writing select query which will fetch a huge amount of data from table so it does not slow down performance. ...

Multipart identifer could not be bound, correlated subquery

This is a contrived example using SQL Server 2008. I'm essentially storing a list of ids in an xml column in a table: temp (bigint id, xml ids) I want to join the table itself to the xml nodes. So far I have: select * from temp x join ( select x.id , ids.id.value('@value', 'bigint') zid from temp t cross apply ids....

Export SQL Server 2005 query result to SQL INSERT statement?

Is there an easy way in SQL Server 2005 to export query results to a SQL INSERT statement? I'm thinking something along the lines of how you can use Database Explorer to script an existing stored procedure to a new query window. We're looking to move some data from a dev DB to a production one; I know about linked servers but there's no...

Question about JPQL, @NamedQuery

If I have something like this @Entity public class Facility { ... @ManyToOne @JoinColumn(name="CUSTOMER_FK") private Customer customer; ... } does my @NameQuery like this @NamedQuery(name="Facility.findByCustomerId", query="select c from Facility c where c.customer=:customer_fk") or like this @NamedQuery(name="...

MongoDB - upsert involving lists

Hi, I'm a MongoDB newbie and wanted to ask how to write an update command involving upsert and list. Basically I want to accomplish something like this: {"_id" : ObjectId("4c28f62cbf8544c60506f11d"), "some_other_data":"goes here", "trips": [ {"name": "2010-05-10", "loc": [{"lat":21.321231, "lng": 16.8783234, "updated_at": ...

Inventory SQL Query without UNION ?

I'm designing an inventory system, it has users, products, buy_leads, orders (authorized buy_leads), inputs and outputs. class Product < ActiveRecord::Base has_many :buy_leads end class BuyLead < ActiveRecord::Base belongs_to :product has_one :order end class Order < ActiveRecord::Base belongs_to :buy_lead belongs_to :user, ...

sql query help - how to properly join ??

Hi guys, Let's say if I have a table "PersonData" in which 4 of its columns are FromPersonId, FromEmployeeId, ToPersonId, ToEmployeeId Any of the records only ever contains Only one From** and Only one To** and the other two columns are null. Also FromPersonId and ToPersonId belong to "Person" table and FromEmployeeId and ToEmployeeId...

How to find words in a memo field with microsoft access

Hi This is more complex than I thought as I need to do it with VBA. So here is my problem:- I have two tables in an MS Access DB, content and adjectives. The content table has over 5,000 text snippet rows in a memo field (content) and a blank text field (adjective). The adjectives table has 120 adjectives with the field (adjective)....

MySql Query: Select top 3 rows from table for each category

I have an table with records and this table has an row called category. I have inserted to many articles and i want to select only to articles from each category. I tried to do something like this: i created an view: create view limitrows as select * from tbl_artikujt order by articleid desc limit 2 and than i created this query:S...

Inner join with count() on three tables

Simple and fast question, i have those tables: //table people | pe_id | pe_name | | 1 | Foo | | 2 | Bar | //orders table | ord_id | pe_id | ord_title | | 1 | 1 | First order | | 2 | 2 | Order two | | 3 | 2 | Third order | //items table | item_id | ord_id | pe_id | title | | 1 | 1 | 1 | ...

SQL: CASE and selecting multiple COLUMS

Hi Guys, How can I do this: Based on the select Case below I want to show TWO separate columns within one WHEN? The two lines below which are commented is what something I would like to have and I am sure it is something very simple I am missing....however i get syntax error select person.FirstName, person.LastName, CASE ...

How to get 0 value when count(*) returns null on multiply values

I have a table that contains error codes and entry times (among other things). What I need is a way to count the amount of lines with the same error code (that I choose) for the last hour, and to string the result the error code. SELECT COUNT(*) || ',' || error_code as amount_and_code FROM my_table WHERE error_code in (5001, 5002, 5003,...

sql multiple insert on one table, while looping/iterating over another table?

Hi Guys, I have two table "TempStore" and "Store" with the same column called "Items". There is data in "TempStore" table which I need to move over to "Store" table which requires few modifications. I need to iterate over "TempStore" data (i.e. items) and insert into Store... More specifically: How can I iterate over TempStore (in sq...

SQL Script Help, combine two queries into one

I was wondering if these two queries could be combined into a single query? Query 1: to get the @guidID to be plugged into Query 2: DECLARE @guidID uniqueIdentifier SET @guidID = (SELECT guidID FROM dbo.table1 WHERE IntID = 1) Query 2: retrieves a combined table from a function and table1 SELECT o.guidID, IntID, Title, func.Nam...

How to call API's from a URL in Android

I have a URL to a cfm file that contains the API for a application that I am making a mobile version of. On my end of the application, I need to supply that file with all of the variables it needs (all of which are URLs). How would I take take these variables created in my application - all of them currently Strings - and give them to ...

Mysql Many to Many with Count and math

I have a many to many table setup in my mysql database. Teams can be in many games and each game has 2 teams. There is a table in between them called teams_games. What I am looking to do is create stats for each team. An ideal printout would be: team_id, team_name, wins, losses, draws, error My problem is linking the math of which tea...

Most efficient way to query joined tables in Filemaker Pro 10

I am tasked with extracting data from 2 tables: Master and Charges. The common key is two alpha fields. I need to filter the results by fields in each of the two tables-- Year and Office in the Master, and another alpha field in the Charges. There are over a million master records, and about 4 charges for each on average. The tables ...