I am wondering if there is away (possibly a better way) to order by the order of the values in an IN() clause. The problem is that I have 2 queries, one that gets all of the IDs and the second that retrieves all the information. The first creates the order of the IDs which I want the second to order by. The IDs are put in an IN() clause...
How do we combine http://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql and http://stackoverflow.com/questions/183124/multiple-random-values-in-sql-server-2005 to select N random rows using a single pure-SQL query? Ideally, I'd like to avoid the use of stored procedures if possible. Is this even possible? CLARIFICA...
I have run into this problem a couple of times in my career, and have never been very happy with the solution, and am presented with it again on a project I am doing in ASP.Net MVC, C#, SQL Server 2008: Imagine I have a Person type (class). I further have types Mother and Father that extend Person. Father and Mother are very similar: ...
From Joel Spolsky's article on leaky abstractions: [C]ertain SQL queries are thousands of times slower than other logically equivalent queries. A famous example of this is that some SQL servers are dramatically faster if you specify "where a=b and b=c and a=c" than if you only specify "where a=b and b=c" even though the result set i...
EDIT: I'm told that making you guys read means I get less attention. My apologies. Here's a simpler version: Bill got $100 dollars worth of items from a store. He wants to return enough of the items to get exactly $30 dollars back. The store has a Point of Return system that will help him do this. Here is the data after he scans his...
Hello. I hope someone could help me on this. I want to add a month to a database date, but I want to prevent two jumping over month on those days at the end. For instance I may have: Jan 31 2009 And I want to get Feb 28 2009 and not March 2 2009 Next date would be March 28 2009 Jun 28 2009 etc. Is there a function that...
It's quite possible a question like this has been asked before, but I can't think of the terms to search for. I'm working on a photo gallery application, and want to display 9 thumbnails showing the context of the current photo being shown (in a 3x3 grid with the current photo in the centre, unless the current photo is in the first 4 ph...
Hi, Lets imagine I have the same database schema as here: http://www.databaseanswers.org/data_models/driving_school/index.htm If a customer makes a booking and it is in the same day and time as another booking, how can I gracefully handle this business problem? Furthermore, what if two bookings are made at the same time? This is a conc...
I've got a sql query (using Firebird as the RDBMS) in which I need to order the results by a field, EDITION. I need to order by the contents of the field, however. i.e. "NE" goes first, "OE" goes second, "OP" goes third, and blanks go last. Unfortunately, I don't have a clue how this could be accomplished. All I've ever done is ORDER BY ...
I've seen a few similar questions to this one, but none that were quite the same. Anyhow, I have a stored procedure that ends with the lines, select SCOPE_IDENTITY() return I then get the value by calling executeScalar(). This works perfectly. However, I feel like it would be more correct to just end with return select SCOPE_IDENT...
What are the advantages of replacing (int)reader[0] with reader.GetInt32(0)? I'm sure such casting functions are there for a reason, but other than it feeling more aesthetically pleasing to avoid the cast myself, I'm not sure what those reasons are. ...
I'm using MS SQL Server. When I define the database schema I define a (non-materialized) view, which includes many fields, for example as follows (where "Topic" is the name of a table, and the view is a self-join on the Topic table): CREATE VIEW View_Topic_Ancestor AS SELECT Subordinate.Id AS Subordinate_Id, Subordinate.Folder_...
Hi, I have a Class see below, when i am trying to query it from another page it not linking e.g. if i put "var Notes = HandhedNotes.GetNotesByLinkIDJoin();" when i look at Notes theres not links there. I think its due to the IQuerable maybe I should be using something elese?? namespace WebSys { public partial class HandheldNote { ...
I want to make a table that simply has two integer columns to serve as a mapping table between two different data sets and I wish to put the correct constraints on it. I initially set the two columns as a compound primary key, but then realized that represents a many to many, only keeping duplicate many to many mappings from occurring. ...
Is there a general consensus out there for when working with library's that call stored procedures? Return datasets or use sqldatareader to populate custom objects? Is the cost of serialization your Data Transport Object less then a DataSet? ...
Hi people, Does anyone know a situation where a PostgreSQL HASH should be used instead of a B-TREE for it seems to me that these things are a trap. They are take way more time to CREATE or maintain than a B-TREE (at least 10 times more), they also take more space (for one of my table.columns, a B-TREE takes up 240 MB, while a HASH would...
So, I'm using jdbc to talk to a MySQL DB. For many a table and for many queries/views, I have created one class which encapsulates one row of the table or the query/table result. Accesses to the DB return one object of such a class (when I know exactly that there's only one matching row) or a Vector of such objects. Each class featu...
I seem to have a problem with this SQL query: SELECT * FROM appts WHERE timeStart >='$timeStart' AND timeEnd <='$timeEnd' AND dayappt='$boatdate' The time is formatted as military time. The logistics is that a boat rental can be reserved at 7am til 1pm or 9am til 1pm or 9am til 5pm. If there is an appt within that range, it should ...
So, I'm trying to use ADO.NET to stream a file data stored in an image column in a SQL Compact database. To do this, I wrote a DataReaderStream class that takes a data reader, opened for sequential access, and represents it as a stream, redirecting calls to Read(...) on the stream to IDataReader.GetBytes(...). One "weird" aspect of IDa...
I'm trying to write a circuit schematic drawing tool in Python. I'm creating a simple database based on dictionaries which holds all the components and their properties. I'm also trying to create a simple query language where you can select, for example, all resistors with value>100ohms or footprint='0402' So far, I can select thing...