When I try this in MySQL:     select make,count(*) as num from TABLE where num > 100  group by   make   I got:   Error Code: 1054, SQL State: 42S22]  Unknown column 'num' in 'where clause'   Is there a way to limit count(*)?
13630
I have a simple query:  SELECT u_name AS user_name FROM users WHERE user_name = "john";   I get Unknown Column 'user_name' in where clause.  Can I not refer to 'user_name' in other parts of the statement even after select 'u_name as user_name'?
21375
I want to write a select statement that with some fixed columns and dynamic columns. here Dynamic Columns names are the rows in another table.  I have tried this but getting error. Please tell me where my error is  set @sql=(Select subject from tbl_subjects where C_Id=22); SELECT      ta.rollno,     ta.StdNm,     tc.C_Name,     @sql FROM     tbl_cmarks tm,     tbl_admission ta,     tbl_classes tc WHERE     tm.Cid = tc.C_Id         AND ta.rollno = tm.rollno         AND tm.Cid = 22 and tm.Examid=9    Mysql showing:     Error Code: 1242. Subquery returns more than 1 row   Could any one tell me how to do this in mysql?
39271
I have a table that has a very high usage (insert/update/select) - one of the operations is to obtain a count of the number of rows. So far I've been using the classic select count(pk) from table but I'm thinking to read it from information_schema instead of doing the count.  select table_rows from tables where table_name = 'my_table'  Is this reliable enough ?  If there is a different method please let me know.  thanks!
94051
I want to do something like this:    create table app_users (     app_user_id smallint(6) not null auto_increment primary key,     api_key     char(36) not null default uuid() );    However this results in a error, is there a way to call a function for a default value in mysql?  thanks.
31371
Suppose you have the following table and data:  create table t (     k int,     v int,     index k(k)     ) engine=memory;  insert into t (k, v) values (10, 1),        (10, 2),        (10, 3);   When issuing select * from t where k = 10 with no order by clause, how does MySQL sort the records by default?
49893
Found the following in an Oracle-based application that we're migrating (generalized):  SELECT     Table1.Category1,     Table1.Category2,     count(*) as Total,     count(Tab2.Stat) AS Stat FROM Table1, Table2 WHERE (Table1.PrimaryKey = Table2.ForeignKey(+)) GROUP BY Table1.Category1, Table1.Category2   What does (+) do in a WHERE clause? I've never seen it used like that before.
58206
How can I have a dynamic variable setting the amount of rows to return in SQL Server? Below is not valid syntax in SQL Server 2005+:  DECLARE @count int SET @count = 20  SELECT TOP @count * FROM SomeTable
74456
I want to write a query like this:  SELECT o.OrderId, MAX(o.NegotiatedPrice, o.SuggestedPrice) FROM Order o   But this isn't how the MAX function works, right?  It is an aggregate function so it expects a single parameter and then returns the MAX of all rows.     Does anyone know how to do it my way?
58214
I'm trying to create a query activity in order to show, from a Data Extension, only the last 5 articles ids. I tried with the formula ORDER BY xxx DESC LIMIT 0,5 as in SQL but it is not working and it is returning this exception:     The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified   How can I use ORDER BY in a Data Extension?
13466
I am looking for a way to concatenate the strings of a field within a group by query. So for example, I have a table:  ID   COMPANY_ID   EMPLOYEE 1    1            Anna 2    1            Bill 3    2            Carol 4    2            Dave   and I wanted to group by company_id to get something like:  COMPANY_ID   EMPLOYEE 1            Anna, Bill 2            Carol, Dave   There is a built-in function in mySQL to do this
119372
I am very, very new to MYSQL.I tried to create a table named "option". My SQL Query is :  create table option(   id int not null primary key auto_increment,  choice varchar(30)  )  While executing this query it shows the following error  Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'option( id int not null primary key auto_increment, choice varchar(30) )' at line 1 (0 ms taken)  If I try with the table name as "choice" it is working.  can we have the table name as "option" in mysql?   thanks
62223
In MySQL we can create queries with or without the backtick (`) symbol. Example:   SELECT * FROM TEST; SELECT * FROM `TEST`;   Both works fine in mysql-console.  Is there any technical difference between them?  Is there any benefit using (`) over over simple queries?
115371
Is it possible to build a single mysql query (without variables) to remove all records from the table, except latest N (sorted by id desc)?  Something like this, only it doesn't work :)  delete from table order by id ASC limit ((select count(*) from table ) - N)   Thanks.
31406
I am planning to replace repeatedly executed Statement objects with PreparedStatement objects to improve performance. I am using arguments like the MySQL function now(), and string variables.   Most of the PreparedStatement queries I have seen contained constant values (like 10, and strings like "New York") as arguments used for the ? in the queries. How would I go about using functions like now(), and variables as arguments? Is it necessary to use the ?s in the queries instead of actual values? I am quite confounded.
53567
I have a query which is meant to show me any rows in table A which have not been updated recently enough. (Each row should be updated within 2 months after "month_no".):  SELECT A.identifier      , A.name      , TO_NUMBER(DECODE( A.month_no              , 1, 200803               , 2, 200804               , 3, 200805               , 4, 200806               , 5, 200807               , 6, 200808               , 7, 200809               , 8, 200810               , 9, 200811               , 10, 200812               , 11, 200701               , 12, 200702              , NULL)) as MONTH_NO      , TO_NUMBER(TO_CHAR(B.last_update_date, 'YYYYMM')) as UPD_DATE   FROM table_a A      , table_b B  WHERE A.identifier = B.identifier    AND MONTH_NO &gt; UPD_DATE   The last line in the WHERE clause causes an "ORA-00904 Invalid Identifier" error. Needless to say, I don't want to repeat the entire DECODE function in my WHERE clause. Any thoughts? (Both fixes and workarounds accepted...)
45432
I've asked the same question on , but got no answer, so that's why I'm asking it here (I've read that simple sql questions should be asked at SO, but I have no choice).  Suppose I have 3 tables: posts, post_categories and categories. I'm implementing some kind of page with filters where I can filter posts by categories. User can select multiple categories. When he selects more than one, they should be summed. I can't get it working. What I have now is simple IN() SQL clause:  SELECT posts.id, post_categories.category_id FROM posts  JOIN post_categories ON posts.id = post_categories.post_id  WHERE post_categories.category_id IN (1,2,3) LIMIT 10;   But it is not matching all ids, it is OR and I need AND. What I need here is to find all posts that have categories with id=1 AND id=2 AND id=3, but instead this query returns posts that have at least one category from the IN() list.
24654
Is there any performance difference between these two queries?  select name from Account where id = :myId  select name from Account where id = :myId limit 1   Or, I guess more general: does salesforce stop searching after first hit when there's a unique value as a condition?