In SQL how to compare date values?
Using MySQL syntax and having a table with a row like: mydate DATETIME NULL, Is there a way to do something like: ... WHERE mydate<='2008-11-25'; I'm trying but not really getting it to work. ...
sql
Strategies for using NHibernate to generate a schema
I'm building a new app that is using NHibernate to generate the database schema but i can see a possible problem in the future. Obviously you use all the data from your database is cleared when you update the schema but what stratagies do people use to restore any data to the new database. I am aware that massive changes to the schema w...
How do I get the MIN() of two fields in Postgres?
Let's say I have a table like this: name | score_a | score_b -----+---------+-------- Joe | 100 | 24 Sam | 96 | 438 Bob | 76 | 101 ... | ... | ... I'd like to select the minimum of score_a and score_b. In other words, something like: SELECT name, MIN(score_a, score_b) FROM table The results, of course, wo...
Building a Query From a List of Strings
How would you take an arbitrary list of strings (of the form "%[text]%") and a database column, and turn them into a SQL query that does a LIKE comparison for each string in the list? An example: I have three strings in my list, "%bc%", "%def%" and "%ab%". This builds the query: ([ColumnName] LIKE "%bc" AND [ColumnName] LIKE "%def%") A...
Can you recommend a good source for Teradata Best Practices?
Looks like my data warehouse project is moving to Teradata next year (from SQL Server 2005). I'm looking for resources about best practices on Teradata - from limitations of its SQL dialect to idioms and conventions for getting queries to perform well - particularly if they highlight things which are significantly different from SQL Ser...
Can SQL calculate aggregate functions across multiple tables?
Let's say I have two existing tables, "dogs" and "cats": dog_name | owner ---------+------ Sparky | Bob Rover | Bob Snoopy | Chuck Odie | Jon cat_name | owner ---------+------ Garfield | Jon Muffy | Sam Stupid | Bob How do I write a query with this output? owner | num_dogs | num_cats ------+----------+--...
Postgresql Concurrency
Hi, In a project that I'm working, there's a table with a "on update" trigger, that monitors if a boolean column has changed (ex.: false -> true = do some action). But this action can only be done once for a row. There will be multiple clients accessing the database, so I can suppose that eventually, multiple clients will try to update...
What is the equivalent of 'describe table' in MSSQL?
I have a Microsoft SQL database and I want to know what columns and types it has. I'd prefer to do this through a query rather than using a GUI like Enterprise Manager. Is there a way to do this? ...
Database consistency with SQL Order By and nulls
I have a column in my database (a flag) with type varchar(1) that is populated either Y or NULL (this is how it is, not in my control). In SQL Server, doing an ascending order by query, NULL is ordered at the top. Should this behaviour be consistent for Oracle and DB2? If, instead I have a COALESCE on the column to ensure it is not nu...
How can I determine whether or not a stored procedure is recompiling every time?
I've got the a SQL Server stored procedure with the following T-SQL code contained within: insert into #results ([ID], [Action], [Success], [StartTime], [EndTime], [Process]) select 'ID' = aa.[ActionID], 'Action' = cast(aa.[Action] as int), 'Success' = aa.[Success], 'StartTime' = aa.[StartTime], 'EndTime' = aa.[EndTime], 'Process'...
use SUM() or caching
I don't have much experience with databases, so I don't know which is better for long-term performance, better practice, etc. Here is my (hypothetical) case: imagine you have a database of customer information and history of purchase orders for each. You want to keep track of how much each customer is buying. I can think of two ways of ...
Novice SQL query question for a movie ratings database
I have a database with one table, like so: UserID (int), MovieID (int), Rating (real) The userIDs and movieIDs are large numbers, but my database only has a sample of the many possible values (4000 unique users, and 3000 unique movies) I am going to do a matrix SVD (singular value decomposition) on it, so I want to return this databa...
how to translate the SQL code "having" condition into LinqToSQL or LinqToEntites?
Could you tell me how to translate the following SQL code to Linq To SQL or Linq To Entites? The correst SQL code is: select CollectId,url,userid,pubtime from Collect group by url,collectid,userid,pubtime having pubtime >= (select max(pubtime) from collect d where d.url = collect.url ) order by Collect.pubtime desc Th...
SQL: How to append IDs to the rows with duplicate values
I have a table with some duplicate rows. I want to modify only the duplicate rows as follows. Before: id col1 ------------ 1 vvvv 2 vvvv 3 vvvv After: id col1 ------------ 1 vvvv 2 vvvv-2 3 vvvv-3 Col1 is appended with a hyphen and the value of id column. ...
Column names for a table formed by a UNION
Given a couple of simple tables like so: create table R(foo text); create table S(bar text); If I were to union them together in a query, what do I call the column? select T.???? from ( select foo from R union select bar from S) as T; Now, in mysql, I can apparently refer to the column of T as 'foo' -- the name ...
sql - Using aggregate functions (min/max) as part of select statement
Hello everyone, I am trying to return the minimum and maximum prices for a villa booking system. I have a look up table that stores the price for each week for each villa. I am using the min and max functions to do this within the select but I'm having lots of problems. Can anyone explain where i'm going wrong? Heres the sp ALTER PRO...
datatype supplied by user for SQL field
I have a SQL table which has a number of fields ID | Value | Type A typical record may be :- 1000,10,[int] a second row may be:- 1001,foo,[string] a third row may be:- 1002,10/12/2008,[DateTime] I have been asked to look at this as at the moment, each time we wish to select from this table we have to cast the value to the type spe...
Hibernate transform SQL to Criteria
Hello, I 'd like to use Criteria for my SQL query. I have 3 tables "home", "person" and a third table "liveIn" for correspondance between home and person. My sql query is "select home.id from home, person, liveIn where home.country = 'Japan' and person.id = '15' and liveIn.Homeid = home.id and liveIn.PersonId = person.id" A little hel...
SQL Server 2005 charindex performance
I'm trying to run an update query that updates one table based on rows it finds in another table where a specific value exists anywhere in one of the remote table's column values. The query I'm using is: update c set LastStep = @StepNumber, LastDate = pv.Created from @Conversions c inner join PageViews pv on c.S...
Anyone know what an <Optional SQL Explain flag> is?
Does anyone know what an Optional SQL Explain flag is? ...