I have to store some intervals in mssql db. I'm aware that the datetime's accuracy is approx. 3.3ms (can only end 0, 3 and 7). But when I calculate intervals between datetimes I see that the result can only end with 0, 3 and 6. So the more intervals I sum up the more precision I loose. Is it possible to get an accurate DATEDIFF in millis...
Hi I plan to create a tale to store the race result like this, Place RaceNumber Gender Name Result 12 0112 Male Mike Ｌｅｅ 1:32:40 16 0117 Female Rose Ｍａｒｒｙ 2:20:40 Ｉ ｃｏｎｆｕｓｅｄ ａｔ ｔｈｅ ｉｔｅｍｓ ｔｙｐｅ ｄｅｆｉｎｉａｔｉｏｎ． Ｑ１．I am not sure the result can ...
Suppose you have the following schema (id, user_id , score). How can I take per each user the row with max score and then order all row for score. In other word I want a ranking where each user have his best result. ...
Hi, I have a script which captures tweets and puts them into a database. I will be running the script on a cronjob and then displaying the tweets on my site from the database to prevent hitting the limit on the twitter API. So I don't want to have duplicate tweets in my database, I understand I can use 'INSERT...ON DUPLICATE KEY UPDATE...
Whenever I do a: $this->Job->find('all', array( 'group' => array('Job.some_field'), 'recursive' => -1 )); I get a : SQL Error: Column 'jobs.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. With MySQL it works fine but with SQL Server 2008 it se...
I have the following script which I use to give me a simple "diff" between tables on two different databases. (Note: In reality my comparison is on a lot more than just an ID) SELECT MyTableA.MyId, MyTableB.MyId FROM MyDataBaseA..MyTable MyTableA FULL OUTER JOIN MyDataBaseB..MyTable MyTableB ON MyTableA.MyId = MyTabl...
I heard opinion that it make no sense to create indexes when field has less changeability. Eg if it stores only A,B,C,D values then there wouldn't be benefit of having such index, and further more SQL server will not use it at all when executing query. I wonder your opinion on that matter? EDIT Sample usage Select * FROM Table WHERE ...
I have a table records(ID, ParentID) containg this data ID ParentID 1 null 2 1 3 2 4 2 5 3 6 null 7 6 If you draw this table in hierarchy as a family 1,2,3,4,5 will be related to each other. I want to find a way, where I can pass an ID like 3 it give me the others family members using SQL or us...
We're seeing strange behavior when running two versions of a query on SQL Server 2005: version A: SELECT otherattributes.* FROM listcontacts JOIN otherattributes ON listcontacts.contactId = otherattributes.contactId WHERE listcontacts.listid = 1234 ORDER BY name ASC version B: DECLARE @Id AS INT; SET @Id = 1234; SELECT otherattribut...
How can I generate some insert sql sentences using a specific select query. Are there any tool from Ms Sql Server 2008? or else? ...
Hi, I'm trying to get the AVERAGE from the results of two separate sql queries built in MS Access. The first sql query pulls the largest record: SELECT DISTINCTROW Sheet1.Tx_Date, Sheet1.LName, Sheet1.Patient_Name, Sheet1.MRN, Max(Sheet1.) AS [Max Of FEV1_ACT], Max(Sheet1.FEF_25_75_ACT) AS [Max Of FEF_25_75_ACT] FROM Sheet1 GROUP ...
I have the following sql table which contains menu (website menu) data. Table Name: MenuItems Columns: Id, MenuId, ParentMenuItemId, Text. My goal is to bind a DDL according to the following hierarchy (example): Id: 1, MenuId: 1, ParentMenuItemId: -1, Text: 'One' Id: 2, MenuId: 1, ParentMenuItemId: 1, Text: 'Two' Id: 3, MenuId: 1,...
This query fails when I add the line shown... Select Companyid, count(*) as cnt from mytable where State is not null and cnt = 1 <------------------------- FAIL group by CompanyID Any way to do this? Here's a long winded background if it'll help.... I have a single table query. Here's a sample of the table: CompanyID, State 1,OH ...
Hi, In my shops database I need to have the opening hours. Do you have an idea how i can implement this in my dB? The opening hours are from Monday to Sunday, each day can have 2 opening windows (ex 09:00-12:00, 16:00-19:00) ...
Hey i got this Table: CREATE TABLE Test_Table ( old_val VARCHAR2(3), new_val VARCHAR2(3), Updflag NUMBER, WorkNo NUMBER ); and this is in my Table: INSERT INTO Test_Table (old_val, new_val, Updflag , WorkNo) VALUES('1',' 20',0,0); INSERT INTO Test_Table (old_val, new_val, Updflag , WorkNo) VALUES('2',' 20',0,0); I...
Can we join a table with the result of a subquery, such as: select name from gifts LEFT OUTER JOIN (select giftID from gifts) ... If not, can it be done by some methods, such as creating a temporary table? P.S. Can a subquery only appear using IN or NOT IN, or EXISTS or NOT EXISTS? ...
I have to create a C# application which displays unicode data in a textbox. But the SQL server column storage has varchar data which contains unicode data. But since varchar is non-unicode based it displays character strings like "????????". Well the problem i am facing is i cant change the column type as there are other dependent app...
Regarding to SQL performance. I have a scalar valued function for checking some specific condition in base, it returns BIT value for True or False. I now do not know how I should fill @BIT parameter If I write. set @bit = convert(bit,1) or set @bit = 1 or set @bit='true' function will work anyway but I do not know which met...
I would like to try using Outer Join to find out duplicates in a table: If a table has Primary Index ID, then the following outer join can find out the duplicate names: mysql> select * from gifts; +--------+------------+-----------------+---------------------+ | giftID | name | filename | effectiveTime | +--------+--...
I need to get the most recent record for each device from an upgrade request log table. A device is unique based on a combination of its hardware ID and its MAC address. I have been attempting to do this with GROUP BY but I am not convinced this is safe since it looks like it may be simply returning the "top record" (whatever SQLite or M...