I'm using MySQL. I have a table which looks like that: id: primary key content: varchar weight: int What I want to do is randomly select one row from this table, but taking into account the weight. For example, if I have 3 rows: id, content, weight 1, "some content", 60 2, "other content", 40 3, "something", 100 The first row has 3...
That table contain: chanelID,chanelclassify,createdate,movedate and report has form: index,date,chanelclassify,number of created chanels, number of moved chanels, avgTime how should i do to count the number of created and moved chanel on one by one date and group by chanelclassify? ...
I have trouble accessing filestream via SqlFileStream. Sql server and IIS7 are on different servers, remote access to Filestream is enabled. Workgroup for both servers is the same. I've tried everything, including opening user with identical username/passwords on both servers. Didn't work. Dim sqlFileStream As New SqlTypes.SqlFileStre...
ATM I am trying to learn how to efficiently use database inidices and would appreciate to get some expert input. I do not have any performance issues currently. I would just like to know, how you would handle your indices with this query: SELECT B.event, COALESCE(B.system, C.surname || ' ' || C.forename) AS name, C.label...
After going thru some tutorials on SQL SERVER 2008's new feature SPARSE COLUMN, I have found that it doesn't take any space if the column value is 0 or null but when there is a value, it takes 4 times the space a regular(non sparse) column holds. If my understanding is correct, then why I will go for that at the time of database design?...
I've got a simple bit of code that uses BeginTransaction(). The resulting transaction is assigned to the connection that I'm using for some sql commands. When I profile the resulting sql, I don't see a BEGIN TRANSACTION at any point. What might be happening that would prevent the transaction from being used? ...
Normally LIKE statement used to check the pattern like data. ex: select * from table1 where name like 'ar%' My problem is to use one column of table with LIKE statement. ex: select * from table1, table2 where table1.x is like table2.y% Query above creates error creates . how to use one column data in like query? ...
I have a query that returns a result set similar to the one below A | B | C | D -----|----|----|----- 1 abc | d0 | e0 | true 2 def | d0 | e1 | true 3 ghi | d0 | e2 | true 4 jkl | d1 | e1 | true 5 mno | d2 | e2 | false In Column A each value is unique. but column B,C,D have some duplicate values. ...
i am using this function in a trigger: CREATE OR REPLACE FUNCTION xx() RETURNS trigger AS $xx$ BEGIN INSERT INTO my_log (x, y, z) VALUES (NEW.x, NEW.y, current_setting('myvar.user')); RETURN NULL; END; $xx$ LANGUAGE plpgsql; now i would like to check, if 'myvar.user' is a valid integer, and if not, do anothe...
Hi, I have a table of PricePlan that looks like Table PricePlan ID MerchantID NAME VALIDFROM VALIDUPTO 1. M1 Plan A 1-sep-09 30-sep-09 2. M1 Plan B 7-sep-09 21-sep-09 3. M2 Plan Da 1-sep-09 30-Sep-09 Given a @FromDate and @ToDate I need to find the matching id and Unique MerchantID....
If it's an int, I know it will be faster, just cannot understand the string type. notes: most Asian language don't have the space between words. and mysql cannot split the sentence to words. and also, I mean random search, that is, words can appears in any where in a sentence . ...
SQLException.getSQLState retrieves the SQLState for the SQLException object. What are all the possible values that can be returned by this method? Can I use the value to identify specific errors that ocured in the database (i.e. can this value tell me if it was a PK violation, or a unique constraint, or column value to large etc)? Also,...
I was curious since i read it in a doc. Does writing select * from CONTACTS where id = ‘098’ and name like ‘Tom%’; speed up the query as oppose to select * from CONTACTS where name like ‘Tom%’ and id = ‘098’; The first has an indexed column on the left side. Does it actually speed things up or is it superstition? Using php and my...
Hi all! How can I compile Propel Criteria to clear SQL? I've tried $criteria->toString(); but this is not I expected. Also I've tried ModelPeer::doSelectStmt($criteria) but it returned raw sql (required parameters substitution) ...
I ran into something a little odd this morning and thought I'd submit it for commentary. Can someone explain why the following SQL query prints 'equal' when run against SQL 2008. The db compatibility level is set to 100. if '' = ' ' print 'equal' else print 'not equal' And this returns 0: select (LEN(' ')) It appears to b...
I've got a T-SQL stored procedure running on a Sybase ASE database server that is sometimes failing to commit all of its operations, even though it completes without exception. Here's a rough example of what it does. BEGIN TRANSACTION UPDATE TABLE1 SET FIELD1 = @NEW_VALUE1 WHERE KEY1 = @KEY_VALUE1 IF @@error <> 0 OR @@rowcount <> 1 BE...
Hello, I have the same "problem" as described in (http://stackoverflow.com/questions/1078280/last-record-of-join-table): I need to join a "Master Table" with a "History Table" whereas I only want to join the latest (by date) Record of the the history table. So whenever I query a record for the mastertable I also geht the "latest" data o...
Ffor testing purposes I need to write a SQL query which contains the actual record number as a column in the result set. If my SELECT gets back to me with 10 records as the result, I need to have one column which contains the values 1-10. Is there a way to achieve this without a stored procedure cursoring through my data? EDIT: I nee...
Is it possible to specify a condition in Count()? I would like to count only the rows that have for example "Manager" value in Position column. Edit: please read carefully, I want to do it IN count statement, not using WHERE; I'm asking about it because I need to count both Managers and Other in the same select (something like Count(Pos...
I have a VB app that accesses a sql database. I think it’s running slow, and I thought maybe I didn’t have the tables propery indexed. I was wondering how you would create the indexes? Here’s the situation. My main loop is Select * from Docrec Order by YearFiled,DocNumb Inside this loop I have two others databases hits. Select *...