sql

Limiting amount of rows in OleDbDataAdapter query

I am using C# and .NET 2.0. I created an OleDbDataAdapter to import a tab delimited file and in the SQL for one of my commands, I am adding 'LIMIT 1' to the end of the query. string query = string.Format("SELECT DISTINCT * FROM {0} ORDER BY ZipCode ...

How do I properly join tables and use a group by to summarize data in MySQL?

I am working in MySQL and having a bit of trouble with building a query that will summarize columns from two tables. I want to compare the quantity of requests per day, for a table containing hourly records and a table containing daily aggregation, per day. Ideally the sums of each would be identical. Here is the schema: Hourly Table:...

SQL correlated subquery in a case clause

Hello all, is it possible to write a subquery within a case clause for the when statement ie. SELECT colA, colB, CASE WHEN (SELECT colA FROM tab2 WHERE tab2.colA = tab1.colA) THEN '1' CASE WHEN (SELECT colA FROM tab3 WHERE tab3.colA = tab3.colA) THEN '2' ELSE '0' END AS colC, ... FROM tab1 Extended question: Is ...

How to handle Gmail addresses?

Background Gmail allows '.'s and +filters allowing for an infinite number of email addresses all pointing to the same gmail account. i.e. the following all point to the same gmail account: [email protected] [email protected] [email protected] [email protected] Problem We have a table on our production environment that hold all re...

Algorithm improvement on a simple looking postgresql query.

High-level: Can I do this order by, group by based on sum any faster? (PG 8.4, fwiw., on a non-tiny table .... think O(millions of rows) ) Suppose I had a table like this: Table "public.summary" Column | Type | Modifiers -------------+-------------------+-----...

Compojure + clojure.contrib.sql: SELECT query is being cached. Why?

I'm writing a Compojure TODO app and with MySQL as the primary data store. I'm using clojure.contrib.sql to interface with MySQL as follows: (def db {:classname "com.mysql.jdbc.Driver" :subprotocol "mysql" :subname "//localhost:3306/todo" :user "<user>" :password ""}) The queries I'm using seem to w...

Nested Queries? -> Set Value = (Select Value From OtherTable WHERE) ...

I'm looking to do a single query to update a database. Here is some pseudocode: UPDATE Table1 SET Table1.Value = (SELECT Value FROM Table2 WHERE Table2.Id==2) WHERE Table1.Id == 4 ...

Is there a SQL technique for ordering by matching multiple criteria?

I have several tables that get JOINed together to form a table with columns designID garmentID colorID sizeID imageID I have a function that looks like this [variables in square brackets are optional]: getProductImages($designID, [$garmentID], [$colorID], [$sizeID]); I want it to return all imageIDs that match $designID in the foll...

sql primary key integer/varchar

hi, i had an argue at the office with the team i'm working with. they decided to create a table with a varchar primary key. This table is referenced by another table on this pk. When i noticed that, i was a bit disappointed because i've the habit to create an integer primary key (following what some teacher told me at the university). I...

Why do SQL FullText queries slow down when you OR?

In SQL Server (2008), I have a FullText index on two columns, call them Table1.FirstNames and Table2.LastNames. After profiling some queries, I came up with the following results: SELECT * FROM (Table1 LEFT JOIN Table2 ON Table1.SomeKey=Table2.SomeKey) WHERE CONTAINS(FirstNames, 'Bob') OR CONTAINS(LastNames, 'Bob') => 31 197ms SELEC...

Multiple Ranks in one table

Hi, I need the following, Can anyone please help me do it. Rank Cust_Type Cust_Name Revenue 1 Top A 10000 2 Top B 9000 3 Top C 8000 1 Bottom X 5000 2 Bottom Y 6000 3 Bottom Z 7000 I need separate rank...

SQL Group By - Select Both Columns

I have a table of users containing the following columns: | User_ID (int) | Name (varchar) | Age (int) | Experience_Level (int) | I would like to create an sql query to output all of the IDs of people who are not unique in the combination of age and experience. My code so far: SELECT Count(*), User_ID FROM Users GROUP BY Age,E...

How do I get a list of unique values and the number of occurences from SQL Server 2008?

I have a SQL Server 2008 database with millions of records. One field has values ranging from 0 to 250 and may, or may not, include all numbers witin the range. How do I query the database to get a list of distinct values and the number of records contaiing that value? I used a Select Count(Distinct) query but that only gives me the num...

Regarding sql query

I have the below sql query to insert the records from a table. The problem is when there is no records for select query i still want an record with the sequence number,null,'3' value inserted. Nothing get's inserted when there is no record found for select query. how can i do it? insert into test_details(seqnbr,trepid,type) select '&se...

ANDROID: Database connection, check data, login page

Hi, I'm doing Major Project on my final year and I'm very new to Android plus I;m not good at codings. I need help with my login page. I've created something like a database connection java file which is this: package one.two; import java.util.List; import android.app.ListActivity; import android.content.Context; import android.datab...

SQL query works when run as query on server, but will not work from PHP code

I'm running this via PHP and well the first query gets run perfectly then right after it I run the second query and it does nothing. Its rows never get updated for some reason, but when I type out the query manually on the sql server, it works perfectly. Anyone have any idea why its not working in the php code? $qry = "UPDATE Matches S...

SQL data in DataGridView

Hey guys, I have an application that I want to display some data from a sql db in a DataGridView... I have the data displayed in the DataGridView now but here are my questions... How can I use custom header titles because I don't want the SQL column titles to be used for the DataGridView column titles. Also, I want it so that when a user...

SQLite: How to count references to each ROWID in a table, then insert the count

I need some help writing a SQL statement. I have two tables in a SQLite database: CREATE TABLE users ( user_id INTEGER PRIMARY KEY, item_id INTEGER ); CREATE TABLE items ( item_id INTEGER PRIMARY KEY, ref_count INTEGER ); I'm seeking a SQLite statement that will be like the following pseudocode: for each row in items ite...

MySQL: Efficiently finding rows where the contents of a column is the beginning of a string

I have a MySQL table in which a column contains string prefixes. For instance these prefixes could be top-level directories on an Unix file system: my_table: +---------+ | prefix | +---------+ | /usr/ | | /bin/ | | /var/ | | /lib/ | +---------+ How can I write a query that efficiently finds all rows in this table where th...

Sorting union queries in MySQL

I am making a search feature for a Job listing website. For that I need to first show the listings with matching titles and then the job listings with matching description. Here is the query, I am using right now: Example: (SELECT * FROM `jobs` WHERE title LIKE '%java%developer%') UNION DISTINCT (SELECT * FROM `jobs` WHERE descriptio...