0 vote down star Hi, I am trying to create a view for a UNION of 2 select statements that I have created. The UNION is working fine when executed individually But the problem is only the 1st part of the UNION is getting executed when I am executing it as a view. The query I am using is as below SELECT DISTINCT products.pid AS id, ...
So I have a table as follows: ID_STUDENT ID_CLASS GRADE 1 1 90 1 2 80 2 1 99 3 1 80 4 1 70 5 2 78 6 2 90 6 3 50 7 3 90 I need to then group, sort and order them to give: ID_STUDENT ID_CLASS GRADE RANK 2 1 99 1 1 1 90 2 3 1 80 3 4 1 70 4 6 2 90 1 1 2 80 2 5 2 78 3 7 3 90 1 6 3 50 2 Now I know that you can use a temp variable to rank...
I have a table with a row that looks like this: (2009123148498429, '...', '...') The first part, id, is a timestamp followed by a random number. (needed to work with other parts in the system) The data already exists in the table. I want to create a column, timestamp, and extract just the date (20091231) and update all the rows with ...
I have a number of rows from a database which I want to insert into another database. Since there's quite a few rows I want to use the INSERT .. SELECT ... construct in mysql. The statement I try to run is as follows: set @rank := 1; set @currpoints := 0; set @currgroupcount := 0; SELECT id, @rank := if( @currpoints = points, @rank ,...
Is there any way to update a table within the select_expr part of a mysql select query. Here is an example of what I am trying to achieve: SELECT id, name, (UPDATE tbl2 SET currname = tbl.name WHERE tbl2.id = tbl.id) FROM tbl; This gives me an error in mysql, but I dont see why this shouldn't be possible as long as I am not changing ...
I want grouped ranking on a very large table, I've found a couple of solutions for this problem e.g. in this post and other places on the web. I am, however, unable to figure out the worst case complexity of these solutions. The specific problem consists of a table where each row has a number of points and a name associated. I want to be...
I would like to know what the complexity of the following two operations are. The first case is a count where I order by a column that I have an index on and ask for the count of all values below or above a certain number like this: SELECT count(*) FROM tbl WHERE col1 > 10 ORDER BY col1; The other case is concerning a median operation...
I'm in over my head with a big mysql query (mysql 5.0), and i'm hoping somebody here can help. Earlier I asked how to get distinct values from a joined query http://stackoverflow.com/questions/508707/mysql-count-only-for-distinct-values-in-joined-query The response I got worked (using a subquery with join as) select * from media m i...
Say you have a large table with thousands of rows and 3 columns, looking something like this: Name City Birthyear Egon Spengler New York 1957 Mac Taylor New York 1955 Sarah Connor Los Angeles 1959 Jean-Luc Picard La Barre 2305 Ellen Ripl...
I ran into a problem last week moving from dev-testing where one of my quieries which had run perfectly in dev, was crawling on my testing server. It was fixed by adding FORCE INDEX on one of the indexes in the query. Now i've loaded the same database into the production server (and it's running with the FORCE INDEX command, and it has...
Hello, I am not sure if this is possible in mySQL. Here are my tables:- Categories table - id - name - parent_id (which points to Categories.id) I use the above table to map all the categories and sub-categories. Products table - id - name - category_id The category_id in the Products table points to the sub-category id in which it ...
Hi all, I'm trying to count multiple entries in a MySQL database, I know how to use COUNT(), but the exact syntax I want to get the results I need eludes me. The problem: Table structure: ID, CODE, AUTHOR, COUNTRY, TIMESTAMP. Code, Author and Country overlap many times in the table. I am trying to discover if there is one simple query...
Hi All, I have a table tilted "tbltree" Structure: `id` int(10) unsigned NOT NULL auto_increment, `title` longtext NOT NULL, `parent` int(10) unsigned default '0' Data: id Title parent 1 abc 0 2 xyz 1 3 pqr 1 4 uuu 2 Now I want the results like this id title no of childs 1 abc 3 2 xyz 1 3 pqr...
I'm creating a MySQL Stored Procedure with a few cursors. I want to make sure the Insert/Update statements it will ultimately generate are correct before making it live. I searched and could not find a PRINT or similar method to have it just send output back to the client, in my case SQLyog Enterprise. I tried declaring a variable as T...
I have a MySQL table with multiple fields, and I'd like to find any entries that duplicate a tuple. I was initially thinking I could do this select f1, f2, f3 from mytable t1 left join mytable t2 where t1.f1 = t2.f1 and t1.f2 = t2.f2 and t1.f3 = t2.f3; But then I realized that will just return all the rows in the table (I think). I...
Hi, I am trying to search employees based on their code and department. I triggered a query using IN operator which has 1200 employee codes. When i executed the query I got an exception ("Maximum 1000 records allowed in IN "). Can anyone help me out with this issue. ...
In my rails app, I am running a sql query using find_by_sql() since I need subqueries. This works if I do either the first or second query but when I add them together with the AND, it starts complaining about more than 1 row in subquery. I want all rows (records) returned that match the criteria. What needs to be fixed/changes here? W...
I got a single table and it contain 4 fields Id|Hospital| Doctor|patient 1 A D1 P11 2 B D6 P61 3 A D2 P21 4 A D1 P12 5 B D7 P71 6 B D6 P62 7 B D6 P63 Doctors are unique to the Hospital. They don't work in other hosp...
I have two queries. One of them makes sense to me, the other don't. First one: SELECT gender AS 'Gender', count(*) AS '#' FROM registrations GROUP BY gender WITH ROLLUP That gives me this: Gender # Female 20 Male 19 NULL 39 So, I get the count, and the total count. What I expected. Next one: SELEC...
I am trying to take one step towards optimizing a 90GB+ table: Old Table Every day the table grabs approx. 750,000 records from an external source and adds them to the table with the new date. This has been going on for three years from what I understand. 97% of the records don't change from one day to the next. New Table I am tryi...