Does ORDER BY only work with columns?
Why does SELECT * FROM TABLE ORDER BY RAND() Work? I thought ORDER BY only works for columns. So what exactly does it mean to ORDER BY RAND() or ORDER BY SUM()? ...
sql
search HTML stored as binary image in SQl2000/2005 (without fulltext)
I am building a simple search tool to search through 'n' articles of html content. I have tried the fulltext search option and all was well until we went live and I have had a load of trouble with the webhost getting stuff sorted properly. So I might have to move to a host that does not have SQL fulltext support. All of the articles ar...
Group By and displaying entries under date
Here's my data: id date 1 2009-01-01 10:15:23 2 2009-01-01 13:21:29 3 2009-01-02 01:03:13 4 2009-01-03 12:20:19 5 2009-01-03 13:01:06 What I'd like to do is group by each date and then list the id numbers below each date. Should I be getting the distinct date values and then cycling through them to get the entries that fa...
SQL Query to find holes between min_numbers and max_number in a table
Quick question for the SQL gurus. I have a table which contains, amongst others, two columns - min_number and max_number I have been trying unsuccessfully to write a query which finds the first hole of n size between the min and max numbers Example min max 1. 100 200 2. 250 300 3. 330 400 If I want to find a h...
Creating table in mysql
Is it possible to create more than one table at a time using single create table statement. ...
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...
Method 'Boolean Contains(System.String)' has no supported translation to SQL.
"Method 'Boolean Contains(System.String)' has no supported translation to SQL." query is IsQueryable but this stopped working: foreach (string s in collection1) { if (s.Length > 0) { query = query.Where(m => m.collection2.Contains(s)); } } UPDATE: it works when i make query ...
outer query to list only if its rowcount equates to inner subquery
Need help on a query using sql server 2005 I am having two tables code chargecode chargeid orgid entry chargeid itemNo rate I need to list all the chargeids in entry table if it contains multiple entries having different chargeids which got listed in code table having the same charge code. data : code 100,1,100 100,2,100 100...
Custom writing OleDbCommand commands.
Hello! I have (another) question about writing OleDbCommand's. I have a complex select command (includes several left joins) and OleDbCommandBuilder is unable to generate update commands for me. I can't seem to grasp the magic behind writing update commands. I know the structure of the SQL update statement, however I am a bit puzzled a...
SSIS Analysis Services Connection Error In The Transport Layer of initializing provider
I'm trying to connect to an analysis services database in SSIS using a specific user name and password. I'm getting the message error in initializing provider. AN error was encountered in the transport layer. The peer prematurely closed the connection. Any ideas? ...
Correlated query vs inner join performance in SQL Server
let's say that you want to select all rows from one table that have a corresponding row in another one (the data in the other table is not important, only the presence of a corresponding row is important). From what I know about DB2, this kinda query is better performing when written as a correlated query with a EXISTS clause rather than...
LINQ and the Count extension method
I have a database table which could contain many records and I'd like to count the current total in the table. I was going to do a simple: DataContext.Table.Count(c => c.condition); Until I realized the return type for Count is int. What if the table is to hold more values than can be represented in 32 bits? How can I count them? Sh...
Guide to reading, comparing, and interpretting Execution Plans in SQL Server
I'm looking for a good, well-written and explained guide on reading and comparing execution plans in SQL Server. This is for more my own edification as well as for sharing with others. If you want to add your own summary here that would be welcome, though I expect the topic is large enough that links to pre-written articles will be req...
How do I create a table constraint to prevent duplicate values across two columns?
I have the following table: CREATE TABLE [dbo].[EntityAttributeRelship]( [IdNmb] [int] IDENTITY(1,1) NOT NULL, [EntityIdNmb] [int] NOT NULL, [AttributeIdNmb] [int] NOT NULL, [IsActive] [bit] NOT NULL CONSTRAINT [DF_EntityAttributeRelship_IsActive] DEFAULT ((0)), CONSTRAINT [PK_EntityAttributeRelship] PRIMARY KEY CLUSTER...
SQL HELP - Conditional where clause based on a BIT variable - SQL Server
I need help writing a conditional where clause. here is my situation: I have a bit value that determines what rows to return in a select statement. If the value is true, I need to return rows where the import_id column is not null, if false, then I want the rows where the import_id column is null. My attempt at such a query (below) doe...
Mixing PHP's mysqli data access functions?
I've seen a couple of questions over the last few days that have used mysqli but where the answers appear to have not recognised the difference between $stmt->execute() and $db->query(). As I understand it, there are two differing models for accessing results in mysqli. This one uses raw SQL and requires the programmer to escape the in...
Searching multiple tables with MySQL
I am writing a PHP/MySQL program and I would like to know how to search across multiple tables using MySQL. Basically, I have a search box as in the top right of most sites, and when user's search something in that box, it needs to search in users.username, users.profile_text, uploads.title, uploads.description, sets.description and com...
Is there a way to SELECT by a part of a date in SQL?
I have a datetime column in a MySQL table and I'd like to select records that have the same year, month, and day (but perhaps different hour and minutes). In other cases, I'd select ones with the same year and month. Is there a way to do that? ...
Date Range In PHP?
I have a DB created by a third party vendor that I'm now writing a new UI for. The DB stores event start times as unix timestamps (in GMT). What I need to do is query this for a one day range. So presumably I simply need to do a: SELECT * WHERE start > $last_night_at_midnight AND start < $tonight_at_midnight The problem I'm running i...
SQL Formatting Tool
So Visual Studio has a command like Format Document [Ctrl+K, Ctrl+D] is there something similar for SQL Management Studio? If not is there any other product out there, anywhere, that can help with this? We have hundreds of these we have to go through in a rather short time span, and as it we spend WAY to much time reformatting them due...