I have the following table: UID | Limit |Type 8 | 4 |A 8 | 5 |B 8 | 6 |C I want a query, that will return all of these values, but in one row, like this: UID | A | B | C 8 | 4 | 5 | 6 Anybody know how to do that with SQL? I use MySQL. ...
A table I have no control of the schema for, contains a column defined as varchar(50) which stores uniqueidentifiers in the format 'a89b1acd95016ae6b9c8aabb07da2010' (no hyphens) I want to convert these to uniqueidentifiers in SQL for passing to a .Net Guid. However, the following query lines don't work for me: select cast('a89b1acd950...
anyone know how I would write that query with AR? select *, (m.user_id=1) as member from band b join memberships m on m.band_id = g.id; Thanks in advance. ...
Anyone know how to extract the date from a datetime stamp as part of the where clause? eg. select * from tableA where date between '01/08/2009' and '31/08/2009' (Date is a timestamp!) Many thanks, Fiona ...
Hi, I'm having to return ~70,000 rows of 4 columns of INTs in a specific order and can only use very shallow caching as the data involved is highly volatile and has to be up to date. One property of the data is that it is often highly repetitive when it is in order. I've started to look at various methods of reducing the row count in o...
I have a table that looks like this: nid vid tid 1 2 3 2 2 4 3 2 4 4 2 3 I'd like to run some SQL that will take each one of those rows and create another based on it like so: foreach row where vid=2, create a duplicate row where vid = 3 So I'll end up with this: nid vid tid 1 2 3 2 2 ...
I am seeing some odd behavior in SQL Server AVG calcuation. On manual calculation, you get 49.277588 but SQL Server is reporting that the average is 50.9914 as shown below. Question: Can someone explain the difference and why this is happening? You can try out the query on AdventureWorks2008 Database with following query select C.P...
Consider the following query: select FEE_NUMBER from CARRIER_FEE CF left outer join CONTYPE_FEE_LIST cfl on CF.CAR_FEE_ID=cfl.CAR_FEE_ID and cfl.CONT_TYPE_ID=3 where CF.SEQ_NO = ( select max(CF2.SEQ_NO) from CARRIER_FEE CF2 where CF2.FEE_NUMBER=CF.FEE_NUMBER and CF2.COMPANY_ID=CF.COMPANY_ID group by CF2.FEE_NUMBER) g...
I use SQL Server 2000. Suppose I have two tables like the following: Area ---------------------------------- ID| Name | HierarchyLevel ---------------------------------- 1 | World | 1 2 | America| 2 3 | Europe | 2 4 | Africa | 2 5 | USA | 3 and AreaHierarchy ------------------------ ID | ParentID | ChildID...
I want to convert my float field into a decimal field; I want a precision of 11,2 in my decimal field, but when I tried to change the type of my field(example: Amount) I get an error: "Arithmetic overflow error converting float to data type numeric. The statement has been terminated." My field is decimal(11,2) at the table, and my max a...
Suppose I have n types of users in my application. I am using an UserType enumeration to distinguish them. Do I need to keep a table in my database named UserType? So that I can find the user type any time by querying the tables rather than searching the application source code. Doing so my source code may become somewhat complicated...
I have the following query: SELECT a.topicID, d.catalogFileID, d.catalogFileExtension, a.sortorder FROM catalog_topics a LEFT JOIN catalog_files_join b ON a.catalogID = b.foreignKey LEFT JOIN catalog_files_join c ON c.foreignKey = b.catalogFileID LEFT JOIN catalog_files d ON d.catalogFileID = b.catalogFileID WHERE b.fileTypeID = 'gvl401...
According to BOL, "SNAPSHOT Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction" However, that is not exactly what I am observing. Consider this: In the first tab, run this: CREATE TABLE dbo.TestTable(i INT); GO ...
I'd like to be able to insert some values into a table using a single INSERT statement, but I'm having trouble determining the best way. The data I'm inserting is bootstrap (or hard-coded) data, ie. not available in another table. Here's what I've tried so far. create table #t (id int) insert into #t values (1) --- (a) insert into #t ...
I am trying to think data in terms of sets but have some questions about aggregate functions. here is the definition from wiki an aggregate function is a function that returns a single value from a collection of input values such as a set so for example, select c.id, c.user_id, c.name, c.created_at, count(c.id) from colle...
Hey there.. i would like to ask the experts.. i've already created a query to mysql that will give 20 results from mysql table etc. "cat" heres the calling: if(isset($_GET['cat'])) { $query = "SELECT game_title,game_desc,.... FROM games WHERE cat_id='".validate_input($_GET['cat'])."' LIMIT 20"; } ... by this i manage to ge...
Lets say I have around 1,000,000 users. I want to find out what position any given user is in, and which users are around him. A user can get a new achievement at any time, and if he could see his standing update, that would be wonderful. Honestly, every way I think of doing this would be horrendously expensive in time and/or memory. Id...
table 1(ob): name,address table 2(address): dname,addr I need to update ob.address with address.addr when ob.name=address.dname. Can anyone help to get better results because I'm using following command which leads system halt. UPDATE ob LEFT JOIN address ON ob.name =address.dname SET ob.address=address.addr; ...
I am trying to prevent any SQL injection in all my queries and would like to know how to put double quotes in this query. Thanks string.Format("SELECT TOP 10 article_guid, article_title FROM article WHERE article.article_isdeleted = 0 AND FREETEXT(article_title, @val)"); ...
I have a folder C:\Scripts. In that folder I have 2 sub folders, Procedures and another Views. In the Procedures folder I have 2 files proc1.sql proc2.sql in the Views table I have 2 files view1.sql view2.sql I am trying to combine these files into one .sql file with the following batch file Copy Procedures\*.sql proc.sql Copy Vie...