sql

Which mysql select is better/faster?

Is there a preferred way? There are currently 1640 rows in the game table to go through and will be adding about 1200 every year. SELECT `date` FROM `game` WHERE `date`<'2009-11-09' ORDER BY `date` DESC LIMIT 1; 0.0004 seconds SELECT MAX(`date`) AS `date` FROM `game` WHERE `date`<'2009-11-09' LIMIT 1; 0.0006 seconds The speeds we...

Table Variables: Is There a Cleaner Way?

I have a table that stores various clients I have done work for, separated by Government and Commercial columns. The problem is that there could be an uneven number of clients in either column. When I do a SELECT, I end up with NULL values in irregular places because I can't think of a clean way to order the result set. For example, the ...

date problems in mysql 5.1.40

I upgraded from 4.1.8 to 5.1.40 and am having a problem with queries where the dates contains single digit day or month. The first query below worked fine before on 4.1.8 - also if I create a new table in 5.1.40 it works fine, but not on the upgraded data. Dumping and re-creating tables isn't an option. Any ideas how to correct? mysql [...

Proper way to handle 'optional' where clause filters in SQL?

Let's say you have a stored procedure, and it takes an optional parameter. You want to use this optional parameter in the SQL query. Typically this is how I've seen it done: SELECT * FROM dbo.MyTableName t1 WHERE t1.ThisField = 'test' AND (@MyOptionalParam IS NULL OR t1.MyField = @MyOptionalParam) This seems to work well, however it c...

SQL Server select primary key from table where the key contains multiple columns

I am working on a legacy database. I am not able to change the schema :( in a couple of tables the primary key uses multiple columns. In the app I read the data in each row into a table the user then updates the data and I write the data back into the table. Currently I concatenate the various PK columns and store them as a unique id f...

Help with a complex self referency query accross multiple colums

I am having difficulties with a complicated (for me any way) query. The table I'm querying has 3 colums, ClientID (int Not Null), ProductID (int Not Null) and ExpiryDate (smalldatetime nullable) Given two client ID's Master and Consolidated I need to perform the following business logic to return a single data set: Select the Clien...

Help with SQL/LINQ Debugging

I'm having trouble with the following statement, which is returning the error "Sequence contains no elements": var vUser = (from u in this.dcLAUNCHOnline.aspnet_Users where u.UserName.Equals(this.wCreateUser.UserName) select u).Single(); The SQL being generated: SELECT [t0].[A...

Is there any library for T-SQL to turn it object oriented?

Hi all, Microsoft Ajax Library has added full object orientation to JavaScript. Is there any library, framework, component, package, etc equivalent for T-SQL? It would be very nice to write object oriented SQL scripts in MS SQL Server. Cheers, afsharm ...

How can I randomize DataMapper collection and convert it to JSON?

Hi, I'm pulling my hair out trying to build a little random photo JSON feed using DataMapper/Sinatra. Here's what I have so far.. Photo.favorites.to_json(:methods => [:foo, :bar]) So that works fine. The to_json method is provided in the dm-serializer library. All I want to do is randomize that feed so the photos don't show up in the...

SQL Join and Count

I want to join two tables and get data against an ID from the first table and count a column record from the second table against the same ID. I want a single query which gives me that output. ...

Error converting data type varchar to datetime when running raw SQL but not when called by LINQ to SQL

A client is getting an error when running my code. They sent me the SQL from profilder. When I paste it into SQL Server Management Studio it fails with: Error converting data type varchar to datetime However it doesn't fail when I run it on my local dev box or another clients production sever. To test I created a simple app with a...

Splitting data in one column and updating new column with split data

Hello all, I'm working on Oracle 10g.One of the column's of my table stores data, as sampled below. 1722999340KK000200000 1444210829AB1001EX003 1444300000CD0148EX003 1722999340KL000200000 I want to split the data in the ratio of digits ( 4 ; 6; 6; 5) as shown below and store it in different columns ( A1 || A2 || A 3 |...

sql server questions

i have one question. I have a accttype (varchar) field in t_data table. I have different length acct numbers in that field. like few are 15 digit and few are 13 digit. I just want to know how many are there 13 digit acct no and how many are there 15 digit acct number and list them separately. can any one write sql query for that.Please....

Using "cached" LINQ entities

Hi, i've got the problem that i implemented a WCF-service which is using LINQ entities... To generate the content of the response to a service call, requires a high amount of LINQ/SQL selects... Whatever, the service is not doing any updates or inserts to the DB, so i'd like to implement a kind of "caching" of the LINQ entities ( also ...

Optimal code - One large query but more complicated data retrieval or lots of small queries

Hi, I have a C# program that retrieves multiple rows from a DB but currently does them one at a time on the same connection. Would it be better to write it so that instead of repeatedly running (where blah changes each time): select data from table where name = 'blah' To something like: select name, data from table where name in (...

When Does Django Perform the Database Lookup?

From the following code: dvdList = Dvd.objects.filter(title = someDvdTitle)[:10] for dvd in dvdList: result = "Title: "+dvd.title+" @ "+dvd.price+"." When does Django do the lookup? Maybe it's just paranoia, but it seems if I comment out the for loop, it returns a lot quicker. Is the first line setting up a filter and then the fo...

Edit all views and stored precedures, find and replace?

Is there an easy way to do a find and replace on a string in every view and stored procedure in my SQL Server database. I need to replace something like 'X United Kingdom' with 'X(UK)'. ...

Oracle PL/SQL Denomalised Results

Hi, Given three tables: a car table, an extras table and a link table, something like: table_car --------- int car_id string make string model table_extras ------------ int extra_id string extra table_car_extras_link --------------------- int car_id int extra_id I'd like to write a PL/SQL stored proc that returns data in the follow...

Define variable to use with IN operator (T-SQL)

I have a Transact-SQL query that uses the IN operator. Something like this: select * from myTable where myColumn in (1,2,3,4) Is there a way to define a variable to hold the entire list "(1,2,3,4)"? How should I define it? declare @myList {data type} set @myList = (1,2,3,4) select * from myTable where myColumn in @myList ...

Manipulating data in sql / asp.net / c# - how?

Not sure how to word the question... Basically, so far all my SQL stuff has been stored procedures and dumped into a gridview. The odd case where I had to perform an action based on a value (such as highlighting a row green if a certain value was true) were done as the gridview was rendering in one of the overrides. Now however I have ...