This is what I'd like to do, but it doesn't seem possible: (edit: changed single to double quotes) function get_archives($limit, $offset) { $query = $this->db->query(" SELECT archivalie.id, archivalie.signature, type_of_source.description AS type_of_source_description, med...
Is there any way in MS SQL Server to get the results starting at a given offset? For example, in another SQL server, it's possible to do: SELECT * FROM MyTable OFFSET 50 LIMIT 25 to get results 50-74. This construct does not appear to exist in MS SQL. How can I accomplish this without loading all the rows I don't care about? Thank...
I am trying to run a query that will give time averages but when I do... some duplicate records are in the calculation. how can I remove duplicates? ex. Column 1 / 07-5794 / 07-5794 / 07-5766 / 07-8423 / 07-4259 Column 2 / 00:59:59 / 00:48:22 / 00:42:48/ 00:51:47 / 00:52:12 I can get the average of the ...
Is there any way in the SQL language or in MySQL (or other DBMA) to transfer a value from one cell to another? For example, say there is a table called user_cars with the following structure: |id| |user_name| |num_cars| Bob has 5 cars, and John has 3 cars. Is there any way to in one query subtract 2 cars from Bob and add 2 to John? I ...
I've just learned ( yesterday ) to use "exists" instead of "in". BAD select * from table where nameid in ( select nameid from othertable where otherdesc = 'SomeDesc' ) GOOD select * from table t where exists ( select nameid from othertable o where t.nameid = o.nameid and otherdesc = 'SomeDesc' ) And I...
Using the following query and results, I'm looking for the most recent entry where the ChargeId and ChargeType are unique. select chargeId, chargeType, serviceMonth from invoice CHARGEID CHARGETYPE SERVICEMONTH 1 101 R 8/1/2008 2 161 N 2/1/2008 3 101 R 2/1/2008 4 101 R 3/1/2008 5 101 R 4/1/2008 6 101 R 5/1/2...
I need to do a join across two different database servers (IPs 10.0.0.50 and 10.0.0.51). What's the best way? ...
Hi Guys, We're running a java/hibernate app going against ORACLE 10g in TESTING. Once in a while, we're seeing this error(ORA-00942: table or view does not exist). Is there a way to find out which table/view(s) ORACLE is talking about ? I know that I can add extra levels of logging in hibernate which will show all the SQL that it execu...
Hi, i am looking for opinions if the following problem maybe has a better/different/common solution: I have a database for products which contains the names of the products in english (the default language of this application) and i need translations of the names if available. Currently i have this setup: A product table CREATE TA...
How would you go about retrieving the @@IDENTITY value for each row when the SQLDataAdapater.Update is executed on a table? eg. Is it possible to modify/intercept the InsertCommand, generated by the SQLCommandBuilder, to say add an output parameter, and then retrieve its value in the da.RowUpdated event??? ...
Given a table structure like this: CREATE TABLE `user` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(32) NOT NULL, `username` varchar(16) NOT NULL, `password` char(32) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`) ); Is there any use in using the LIMIT keyword when searching by usernam...
Ok, so I have an nHibernate ICriteria that returns an object. I want to order by a single property but NOT asc or desc I want certain values of the property to come to the top of the repeater based on the selection. Like if I choose "video" I want all records with videos to come to the top. Now, can I do this in the query? or the repea...
What automated tools are there? ~~ Mark Harrison ~~ ...
Given this data set: ID Name City Birthyear 1 Egon Spengler New York 1957 2 Mac Taylor New York 1955 3 Sarah Connor Los Angeles 1959 4 Jean-Luc Picard La Barre 2305 5 Ellen Ripley Nostromo 2092 6 James T. Kirk Riverside 2233 7 Henry Jones Chica...
I am using SQL Server 2005. I want to constrain the values in a column to be unique, while allowing NULLS. My current solution involves a unique index on a view like so: CREATE VIEW vw_unq WITH SCHEMABINDING AS SELECT Column1 FROM MyTable WHERE Column1 IS NOT NULL CREATE UNIQUE CLUSTERED INDEX unq_idx ON vw_unq (Column...
I wish to search a database table on a nullable column. Sometimes the value I'm search for is itself NULL. Since Null is equal to nothing, even NULL, saying where MYCOLUMN=SEARCHVALUE will fail. Right now I have to resort to where ((MYCOLUMN=SEARCHVALUE) OR (MYCOLUMN is NULL and SEARCHVALUE is NULL)) Is there a simpler way of sa...
I'm architecting a new app at the moment, with a high read:write ratio. At my current employer we have lots of denormalised data on our tables for performance reasons. Is it better practice to have totally 3NF tables and then use indexed views to do all the denormalisation? Should I run queries against the tables or views? An example of...
I've googled around a bit, but maybe I didn't put the correct magik incantation into the search box. Does anyone know how to get output parameters from a stored procedure in Python? I'm using pymssql to call a stored procedure, and I'm not sure of the correct syntax to get the output parameter back. I don't think I can use any other db...
I want to find a sql command or something that can do this where I have a table named tblFoo and I want to name it tblFooBar. However, I want the primary key to also be change, for example, currently it is: CONSTRAINT [PK_tblFoo] PRIMARY KEY CLUSTERED And I want a name change to change it to: CONSTRAINT [PK_tblFooBar] PRIMARY KEY CLU...
In my enviroment i have a database on ms sql 2000 that is being hit by hundreads of users at any time. Also the are lots of intense reports using reporting services 2005 hitting the same database. The problem we are getting is when there are lots of reports runnig at the same time and people using the database concurrent with the reports...