query

Subquery to return the latest entry for each parent ID

I have a parent table with entries for documents and I have a history table which logs an audit entry every time a user accesses one of the documents. I'm writing a search query to return a list of documents (filtered by various criteria) with the latest user id to access each document returned in the result set. Thus for DOCUME...

Left Joins are what I want but they are very slow?

Overview: I have three tables 1) subscribers, bios, and shirtsizes and i need to find the subscribers without a bio or shirtsizes the tables are laid out such as subscribers | season_id | user_id | bio | bio_id | user_id | shirt sizes | bio_id | shirtsize | And I need to find all users who do not have a bio or shirtsize, ...

improving sql server query response time.

I have a table which contains around 4 lakh records and which gets called on the homepage of the intranet website. At peak times we can have 300-400 concurrent users. The SQL Profiler tool gives the following output. CPU: 406, Reads: 32446, Duration: 397. Q1. I have indexed the fields involved in the 'where' clause. Is there a way to ...

SQL query - Join that returns the first two records of joining table

I have two tables: Patient pkPatientId FirstName Surname PatientStatus pkPatientStatusId fkPatientId StatusCode StartDate EndDate Patient -> PatientStatus is a one to many relationship. I am wondering if its possible in SQL to do a join which returns only the first two PatientStatus records for each Patient. If only one Patien...

How to query as GROUP BY in django??

I query a model, Members.objects.all() and it returns say Eric, Salesman, X-Shop Freddie, Manager, X2-Shop Teddy, Salesman, X2-Shop Sean, Manager, X2-Shop What i want is, to know the best DJango way to fire a group_by query to my db, as like, Members.objects.all().group_by('designation') Which doesn't work of course...

MSSQL - Split a field into 3 fields

I have a resultset consisting of 1 column and in this case 2 rows the single column [ProductDescription] is a varchar field that hold 3 pieces of information (I didn't design it) i need to get those three pieces of information out into 3 additional fields using a query before /------------------------------\ |ProductDescription ...

How best to filter entities exposed by ado.net data services on top of entity framework?

I want to expose a simple set of blog posts, tags and categories through an API provided by ADO.NET data services. It looks easy in the demos: create your entity data model using the entity framework designer, add in the data service, point it to the entities, done. So far so good. But some posts, tags and categories are unpublished (p...

using OR and NOT in solr query

I'm working on a solr query similar to the following: ((myField:superneat AND myOtherField:somethingElse) OR NOT myField:superneat) When running this, no results are returned. Using criteria on either side of the OR NOT returns results that I'd expect - they are just not working well together. In the case that myField matches supern...

How do I specify unique constraint for multiple columns in MySQL?

I have a table: table votes ( id, user, email, address, primary key(id), ); Now I want to make the columns user, email, address unique (together). How do I do this in MySql? Thanks in advance. Of course the example is just... an example. So please don't worry about the semantics. ...

LINQ query operator for log table

I am trying to find the corerct LINQ to SQL query operator and predicate combo that can operate on an audit table. Imagine a table called Setting that has three columns : rowID, DefID, and Value. I want to be able to check that every DefID ( in this case all definition 1 through 3 ) has at least one row which has a value set to true. ...

Is there any Java equivalent of PHP's http_build_query function?

I have a Map with my data and want to build a query string with it, just like I would with http_build_query on PHP. I'm not sure if this code is the best implementation of it or if I'm forgetting something? public String toQueryString(Map<?, ?> data) throws UnsupportedEncodingException { StringBuffer queryString = new StringBuffer()...

Caching query results in ORMs - satisfying arbitrary subset queries

I know N/Hibernate uses a cache to satisfy queries that it has seen before, and that is called a query cache. However, can it satisfy a subset of that query? I'd imagine not since I'd guess that the general problem of figuring that out is undecidable. Are there any strategies for doing this, though? Say I have a query for all widgets wh...

What is the best way to create a simple revision system using MySQL?

I am currently working on a simple revision system that enables me to store multiple versions of a single file, which works fine so far. Table structure is as follows (obsolete columns removed for the sake of brevity): file_id file_revision file_parent file_name -------------------------------------------------------- 1 ...

SQL query to calculate correct leadtime in table (MS Access)

Hi, I have the following table: log(productId, status, statusDate, department...), where productId, status and statusDate is the primary key. Example: id1 01 01/01/2009 id1 02 02/01/2009 id1 03 03/01/2009 id1 01 06/01/2009 id1 02 07/01/2009 id1 03 09/01/2009 id2 01 02/01/2009 id2 02 03/01/2009 id2 01 04/01/2009 id2 03 06/01/2009 id3 01 0...

MySQL - Search in all fields from every table from a database

I know that probably it's not possible to do this, but just want to check it out with you, guys... Is it possible to do something like this, using MySQL? I want to search in all fields from all tables a given string: SELECT * FROM * WHERE * LIKE '%stuff%' ...

help with T-SQL on SQL Server 2005

Hi there, I have a T-SQL that works below: SELECT WP_VTDID AS UTIL_VTDID, (SELECT COUNT(WP_ENGINE) FROM WAYPOINTS WHERE (WP_ENGINE = 1) AND (WP_SPEED > 0) AND WP_VTDID='L083') AS UTIL_RUN, (SELECT COUNT(WP_ENGINE) FROM WAYPOINTS WHERE (WP_ENGINE = 1) AND (WP_SPEED = 0) AND WP_VTDID='L083') AS UTIL_IDLE, (SELECT COUNT(WP_ENGINE) FROM ...

Mysql query with wildcard on number strings

I am trying to query a mysql table which contains strings of numbers (i.e. '1,2,3,4,5'). How do I search to see if it has '1' but not '11' bearing in mind if it is '9,10' '9%' doesnt work?? Fixed! (field like '10' OR field like '%,10,%' OR field like '%,10' OR field like '10,%') ...

Delimiter to use within a query string value

I need to accept a list of file names in a query string. ie: http://someSite/someApp/myUtil.ashx?files=file1.txt|file2.bmp|file3.doc Do you have any recommendations on what delimiter to use? ...

can linq update and query atomically?

Hi, I need to get 1000 rows from a database, and at the same time tag them as 'in process'. This way, another thread can not take the same 1000 rows and process them as well. With linq i do something like this: msgs = (from m in database.messages where (m.status == MESSAGESTATUSINIT) select m).Take(1000).ToList(); ide...

MySQL query (mixing an insert with select)

I have a bunch of rows in a table with columns a, b, c. I'd like to be able to SELECT all rows where say a = 1, and reinsert them with a = 2. Essentially keeping all the rows where column a exist as is, and having a new batch of rows having as a = 2. What's the best query to establish such a multi-INSERT query? This is all happening in t...