Whenever I have some records/objects that I want to be in a certain order, I usually create a field called Ordinal. I often wonder if it would be better to use an integer or a decimal value for the ordinal field. This is a consideration when moving an object to a different position in the order: If you use consecutive integers, you h...
I have SQL2008 installed on my local machine and need to view/alter/select rows and tables from a DB on a remote server running SQL 2005. Will I have any problems doing this. I have seen some people talk about setting compatibility for certain things, but this seems to only be when using new terms for SQL 2008 that don't exist in SQL 2...
I am trying select multiple conditional summaries into a single table result on a DB2 based database. Example: SELECT COUNT(FOO) FROM T1 WHERE T1.A=1 AS A_COUNT, SELECT COUNT(FOO) FROM T1 WHERE T1.B=2 AS B_COUNT Ext... Any help is appreciated. ...
What's the difference between using "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" and NOLOCK? Is one better than the other? ...
For simplicity, assume all relevant fields are NOT NULL. You can do: SELECT table1.this, table2.that, table2.somethingelse FROM table1, table2 WHERE table1.foreignkey = table2.primarykey AND (some other conditions) Or else: SELECT table1.this, table2.that, table2.somethingelse FROM table1 INNER JOIN table2 ...
Is there any difference (performance, best-practice, etc...) between putting a condition in the JOIN clause vs. the WHERE clause? For example... -- Condition in JOIN SELECT * FROM dbo.Customers AS CUS INNER JOIN dbo.Orders AS ORD ON CUS.CustomerID = ORD.CustomerID AND CUS.FirstName = 'John' -- Condition in WHERE SELECT * FROM dbo.Cus...
I have table A with close to 15000 entries. I have a second table B with 7900 entries with a common field with table A. I need to extract into a third temporary tableC all entries from table A except the ones that also appear in table B. Simple as it may sound, i havent found a way to do it. The closest i got was this: INSERT INTO tab...
I am SELECTing a group of records but one column yields distinct values in two or more results. I just want to SELECT the top value; not the Min one or the Max value, but the Top N value encountered by SQL Server 2008. Is there any aggregation mechanism that will perform this? E.g. Table has: Microsoft MSFT 12/21/05Microsoft MSFT 10/...
I have two tables: a schedule table that contains information about how an employee is scheduled and a numbers table in which each number corresponds to a date. The tables look like: [Employee Schedule] ID Employee ID Project ID Day ID ----------- ----------- ----------- ----------- 1 64 2 168 2 ...
I have a number of CSV files of data that I want to Union together into a single table in MS Excel. All the CSV files have the same names and number of columns. In a relational database like Access or SQL I could use a Union Query, but this has to be in Excel. How can I quickly merge all of these files into one in Excel? ...
Instead of executing: SELECT a FROM b WHERE a LIKE 'a%' OR a LIKE 'b%' OR a LIKE 'c%' is there a way to execute something functioning like this pseudocode? SELECT a FROM b WHERE a IN ('a%', 'b%', 'c%') ...
Say I have a database table like the following: FileID | FileName | FileSize | Group ------------------------------------- 1 test.txt 100 Group1 2 test2.txt 100 Group1 3 test3.txt 100 Group2 What would be the best way to display this data with an MVC view in the style of: Group 1 Table C...
Iv been trying to get my head around object orientation and I think iv started to get some of the concepts, but im not sure. Making a google search that answers if my train of thought is correct proved to be quite hard so I decided to ask here really quick, please tell me if the question is against any rules. Im I thinking correctly in ...
Ok, I have a table publication which has PublicationID, PublisherID, NameAbbrev... and this is joined to another table called AreaBuy which has an AreaBuyID field, NameAbbrev, DateCreated and CreatedBy fields. The relationship is basically that the company has signed up a group of papers (not necessarily owned by the same umbrella comp...
Part of why I love Rails is that I hate SQL - I think it's more like an assembly language that should be manipulated with higher level tools such as ActiveRecord. I seem to have hit the limits of this approach, however, and I'm out of my depth with the SQL. I have a complex model with lots of sub-records. I also have a set 30-40 named...
<?php $query = mysql_query("SELECT * FROM threads INNER JOIN users ON threads.poster = users.id WHERE threads.forum_id = 11"); while($row = mysql_fetch_array($query)) { <a href="thread.php?id=<?=$row['id']?>">Link</a> } ?> Theres a column named id in both threads and users. It prints the user i...
I'm getting started on a small internal web application at work, mostly a proof-of-concept, but we want to treat it like a "real" application. I don't have much experience as a DBA, nor does anyone in my group (it's not particularly important to have one, since it's a PoC). I was wondering though, if this web app was going public, sho...
Is it possible to create an indexed view with SQL Server 2008 which selects from another indexed view? create view V1 as (select 1 as abc) create view V2 as (select abc from V1 group by abc) ...
I have an Order class that has_many :shipments. How can I use Order.find to return all the order objects whose newest shipment was created after a certain time (say, in the last hour)? ...
How can I make an average between dates in MySQL? I am more interested in the time values, hours and minutes. On a table with: | date_one | datetime | | date_two | datetime | Doing a query like: SELECT AVG(date_one-date_two) FROM some_table WHERE some-restriction-applies; Edit: The AVG(date1-date2) works but I have no clu...