This question originates from a discussion on whether to use SQL ranking functionality or not in a particular case. Any common RDBMS includes some ranking functionality, i.e. it's query language has elements like TOP n ... ORDER BY key, ROW_NUMBER() OVER (ORDER BY key), or ORDER BY key LIMIT n (overview). They do a great job in increas...
I am implementing a roulette wheel selection, and I would like to keep as much code as possible in SQL. My attempt has yielded the query below. $1 is a random variable of the same range as weight I send to the SQL code (it was not clear how to make random() be called only once). Weight is the size of the row's slot on the wheel. rand...
I have a VARCHAR column that contains 5 informations (2 CHAR(3) and 3 TIMESTAMP) separated with '$'. CREATE TABLE MYTABLE ( COL VARCHAR(256) NOT NULL ); INSERT INTO MYTABLE VALUES ( 'AAA$000$2009-10-10 10:50:00$null$null$null' ), ( 'AAB$020$2007-04-10 10:50:00$null$null$null' ), ( 'AAC$780$null$2007-04-10 10:50:00$2009-...
How to update multiple rows using id as primary key in a table with a single query ?? i have collection of id's say 23,25,26 . i have to update todo_deleted column as checked for all the three rows with ids 23,25,26 I need a query which should be very efficient.. Post ans if u know.. Thanx in advance ...
I have a loop on the rows returned by an SQL SELECT statement, and, after some processing on a row's data, I sometimes want to UPDATE the row's value. The processing in the loop's body is non-trivial, and I can't write it in SQL. When I try to execute the UPDATE for the selected row I get an error (under Perl's DBD::SQLite::st execute ...
Hi, I have some SQL code that needs to be executed if a certain View exists in a database. How would I go about checking if the View exists? EDIT: The DBMS being used is Microsoft SQL Server ...
I have a SELECT statement similar to the one below which returns several counts in one query. SELECT invalidCount = (SELECT COUNT(*) FROM <...a...> WHERE <...b...>), unknownCount = (SELECT COUNT(*) FROM <...c...> WHERE <...d...>), totalCount = (SELECT COUNT(*) FROM <...e...> WHERE <...f...>) This works fine but I wanted to...
UPDATE Finally managed to work it out! Thanks for all the help from everyone. If you spot any potential errors or scope for improvement in my query please let me know. SELECT * FROM TBL_CAMPAIGNS C INNER JOIN TBL_MEMBERS M ON C.campaign_MemberId = M.members_Id INNER JOIN TBL_CAMPAIGNS_CHARITIES CC ON C.campaign_Key = CC.camcha...
Hi, We have a database with a very simple schema: CREATE TABLE IF NOT EXISTS tblIndex( frame_type INT, pts VARCHAR(5), ts_start INT primary key, ts_end INT) And the application scenario is : Every second user will insert 2 ~ 50 records ,and the ts_start fields of those records is always increasing. After ...
Hi all... we have a view in our database which has an ORDER BY in it. Now, I realize views generally don't order, because different people may use it for different things, and want it differently ordered. This view however is used for a VERY SPECIFIC use-case which demands a certain order. (It is team standings for a soccer league.) Th...
If there are 2 columns in database, eg. code varchar(3) name nvarchar(50) how to tell hibernate to pass varchar for searching by code? In the hibernate mappings string is mapped to nvarchar and it produces queries like: Select code, name From table where code=N'AAA' (instead of code='AAA') This is very bad as it causes index scan i...
Hi, I'm making one MySql script that break one string field in multiple words. I need something like the explode function in PHP! I try the mysql substring_index function but I have to expecify the number of occurences of the one substring. And that is not predictable in one 10.000 row table. Any sugestion? this is my actual Stored ...
How to implement the scalar MAX in Sql server (like Math.Max). (In essense I want to implement something like Max(expression, 0), to make negative values replaced by 0.) I've seen in other threads solutions with creating a scalar function (how's that with performance?) case when expression > 0 THEN expression ELSE 0) (then 'express...
I have this table ID Date 1 08/10/2009 11:20:00 2 08/10/2009 12:06:06 3 08/11/2009 13:03:10 4 08/11/2009 01:20:00 5 08/11/2009 15:41:23 6 08/12/2009 14:22:20 I want to return: Date Count 08/10/2009 2 08/11/2009 3 08/12/2009 1 in PL/SQL. Any ideas? ...
I have 3 columns of data in SQL Server 2005 : LASTNAME FIRSTNAME CITY I want to randomly re-order these 3 columns (and munge the data) so that the data is no longer meaningful. Is there an easy way to do this? I don't want to change any data, I just want to re-order the index randomly. ...
I am loading tables in my warehouse using SSIS. Since my SSIS is slow, it seemed like a great idea to build indexes on the tables. There are no primary keys (and therefore, foreign keys), indexes (clustered or otherwise), constraints, on this warehouse. In other words, it is 100% efficiency free. We are going to put indexes based on ...
I'm looking for what I think is called a data modeling program to map out all of our SQL Server tables (200+) into a large, poster-size image. We've put all of our legacy application tables into SQL Server 2005 and my boss is looking for a snazzy way of viewing the data... basically something to say "this is what we maintain". I've see...
I have to round down all incoming data with type DECIMAL(14,3) which has 3 decimal digit to the last one. I.e.: 100; 100.0; 100.00; 100.000 -> 100 100.2; 100.02; 100.20; 100.22 -> 100.xx but 100.221 -> 100.22 100.229 -> 100.22 Using which SQL operator can I check that residue of division in decimal digit is greater then zero? ...
how do you create a one to many relationship using SQL Server? thank you ...
EDIT 9-3-10: I found this blog entry recently that was very enlightening. http://optimizermagic.blogspot.com/2007/12/outerjoins-in-oracle.html There are times when one or the other join syntax may in fact perform better. I have also found times when a have noticed a slight performance increase (only noticeable in VLDBs) when choosing ...