Tag Info

Hot answers tagged

25

Strictly, yes, the FROM clause of a SELECT statement is not optional. The syntax for SQL-99 details the basic SELECT statment, and the FROM clause doesn't have any square brackets around it. That indicates the standard considers it non-optional: SELECT [ DISTINCT | ALL ] {Column expression [ AS name ]} [ ,... ] | * FROM <Table reference> [ ...


15

They are called quoted identifiers and they tell the parser to handle the text between them as a literal string. They are useful for when you have a column or table that contains a keyword or space. For instance the following would not work: create table my table (id int); But the following would: create table `my table` (id int); Also, the following ...


13

mysqldump has the --where option to execute a WHERE clause for a given table. Although it is not possible to mysqldump a join query, you can export specific rows from each table so that every row fetched from each table will be involved in the join later on. For your given query, you would need to mysqldump three times: First, mysqldump all table3 rows ...


12

SELECT coalesce(MAX(post_id),0) AS max_id FROM my_table WHERE org_id = 3 or SELECT case count(*) when 0 then 0 else MAX(post_id) end AS max_id FROM my_table WHERE org_id = 3; if you want max(post_id) to be null when there is 1 row but post_id is null


12

If you want to use something around object identifiers, use at least the standard double quotes: " This works in MySQL, PostgreSQL, SQL Server, Oracle, etc. etc. For MySQL you might need the SQL mode ansi_quotes, depending on the default configuration: SET sql_mode = 'ANSI_QUOTES'; Backticks ` are only used in MySQL, you learn a type of SQL that won't ...


11

There are actually two problems with the query. The first is Max('Row') will return the string 'Row'. The Second is your subquery needs an alias. Try like this: SELECT MAX(Row) FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) Row FROM USERS) UserQuery UPDATE: I guess there are actually 3 problems with this query :). The 3rd being, count() is a ...


11

If you want to get the exact count of rows in an efficient manner, then COUNT(*) is it. The ANSI standard (look for "Scalar expressions 125") states that COUNT(*) give the row count of a table: it is intended to be optimised from the start. If COUNT(*) is specified, then the result is the cardinality of T. A ROW_NUMBER() function isn't a practical ...


11

Derived table SELECT EMail, hashbytes('SHA1', EMail) AS HashedEmail FROM ( SELECT LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com' as EMail FROM sometable ) foo or CTE: ;WITH cEMail AS ( SELECT LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com' as EMail FROM sometable ) SELECT EMail, hashbytes('SHA1', EMail) ...


11

In Relational algebra, projection means collecting a subset of columns for use in operations, i.e. a projection is the list of columns selected. In a query optimiser step, the projection will manifest itself as a buffer or spool area of some description containing a subset of the columns from the underlying table or operator, or a logical view based on ...


9

Both posts are wrong syntax. I've -1 in the first from SO and left a comment on the second. Create a table SELECT * INTO PlayerBackups FROM NhlPlayer Inserts to an existing table INSERT PlayerBackups SELECT * FROM PlayerBackups


9

It is not quite true that NOLOCK means placing no locks at all. Queries under this hint will still take Sch-S locks and (possibly HOBT locks). Under read committed isolation level SQL Server will (usually) take row level S locks and release them as soon as the data is read. These are incompatible with the X locks held on uncommited updates and thus prevent ...


9

Reposting my answer to a similar question regarding SQL Server: In the SQL world, order is not an inherent property of a set of data. Thus, you get no guarantees from your RDBMS that your data will come back in a certain order -- or even in a consistent order -- unless you query your data with an ORDER BY clause. So, to answer your question, ...


9

No, you can specify the 'params' (the parts of the where clause) in any order and the query optimizer will handle it. The optimizer will do the filtering in the order that it estimates is most efficient, but note that this is more complex than just choosing which order to filter: filtering might be done before or after joining for example. You can't exactly ...


9

My first thought would be to use the INFORMATION_SCHEMA first, so you get to know (in one query for all tables in the MySQL instance) which tables have an active column and then use that info to construct your queries. And this is probably the most sane approach. There is one other, tricky way though that works no matter if the table has or not such a ...


8

List all users who have been assigned a particular role -- Change 'DBA' to the required role select * from dba_role_privs where granted_role = 'DBA' List all roles given to a user -- Change 'PHIL@ to the required user select * from dba_role_privs where grantee = 'PHIL'; List all privileges given to a user select lpad(' ', 2*level) || granted_role ...


8

by default, MySQL does not consider the case of the strings This is not quite true. Whenever you create database in MySQL, the database/schema has a character set and a collation. Each character set has a default collation; see here for more information. The default collation for character set latin1, which is latin1_swedish_ci, happens to be ...


8

"it depends" Using a table variable or temp table with requires overhead of creating populating this object However, if the you require multiple processing steps then this is small compared to querying the same data over and over, especially as the query gets more complex. Also, for multiple steps, using a table variable or temp table means working on the ...


7

The order of the rows in the absence of ORDER BY clause may be: different between any two storage engines; if you use the same storage engine, it might be different between any two versions of the same storage engine; Example here, scroll down to "Ordering of Rows". if the storage engine version is the same, but MySQL version is different, it might be ...


7

DML can be considered to exclude SELECT statements. The Wikepidia.org entry for “Data Manipulation Launguage” describes it as follows: The purely read-only SELECT query statement is classed with the 'SQL-data' statements2 and so is considered by the standard to be outside of DML. The SELECT ... INTO form is considered to be DML because it ...


7

Without any more specifics, this is all I can offer: Do not use the visual designers. You may be tempted to use "Edit Top 200 Rows" (previously "Open Table") in Management Studio to "edit" data like a spreadsheet. Resist the temptation. These designers are full of bugs, hold unnecessary locks on the table, pick an arbitrary 200 rows until you massage the ...


7

Similar to @ypercubes but to get one row without 3 separate queries select count(CASE WHEN pub_id = '1389' THEN title_id END) as algodata, count(CASE WHEN pub_id = '0877' THEN title_id END) as binnet, count(CASE WHEN pub_id = '0736' THEN title_id END) as newmoon from titles where pub_id IN ('1389', '0877', '0736') Also, decide if your ...


7

First things first, I notice that your 'what I do now' query: SELECT TOP (1) ca.SensorValue, ca.Date FROM sys.partitions AS p CROSS APPLY ( SELECT TOP (1) v.Date, v.SensorValue FROM SensorValues AS v WHERE $PARTITION.SensorValues_Date_PF(v.Date) = p.[partition_number] AND v.DeviceId = @fDeviceId ...


7

CREATE TABLE `user_mv` (id INT AUTO_INCREMENT PRIMARY KEY) SELECT `user`.`firstname` as `firstname`, `user`.`lastname` as `lastname`, `user`.`lang` as `lang`, `user`.`name` as `user_name`, `group`.`name` as `group_name` from `user` inner join `user_groups` on (`user`.`user_id`=`user_groups`.`user_id`) left join `group` on ...


6

You can only have one statement after the CTE. You can, however, define subsequent CTEs based on a previous one: WITH t1 AS ( SELECT a, b, c FROM table1 ) , t2 AS ( SELECT b FROM t1 WHERE a = 5 ) SELECT * FROM t2; Given that you are trying to count the rows and populate a ref cursor from the same result set, it may be more appropriate to do ...


6

Erwin: I would concur with the idea that USING causing rigid ordering could well create many edge cases where optimal plans would be ruled out. I recently helped someone out who had something like this in his query: LEFT JOIN ( a JOIN b ON a.id = b.a_id JOIN c ON b.c_id = c.id ) ON a.id = something.a_id LEFT JOIN ( table1 t1 ...


6

This is what Joins are for. You don't have to get a list of patients ids and then send them back to the SQL engine. You can combine the two queries into one, with JOIN: SELECT x.* FROM xrays AS x JOIN appointments AS a ON a.patID = x.patID WHERE a.docID IN ('docid1', 'docid2', ..... , 'docidn') ORDER BY x.patID --- optional so ...


6

That's because in SQLite, the AND operator has a higher precedence than OR (see the Operators section on this SQLite documentation page) This means that SQLite first evaluates the category_id=6 AND user_id = 1 expression and then ORs its result with all the other category_id conditions. Thus, your query returns all records where category_id is 1,2,3,4, or ...


6

Here is the query SELECT id,name FROM (SELECT name,MAX(id) id FROM `mytable` GROUP BY name) A ORDER BY id DESC LIMIT 2; Here is some sample data mysql> use test Database changed mysql> drop table if exists mytable; Query OK, 0 rows affected (0.03 sec) mysql> create table mytable -> (id int not null auto_increment, -> name ...


6

SQL_BUFFER_RESULT creates a temporary table on the server for every result set. This is not a temporary table like CREATE TEMPORARY TABLE - it is an implicit temporary table as would be created when using a GROUP BY clause or with a subquery. As such, all the same rules apply. First, let's talk the problem that SQL_BUFFER_RESULT is designed to solve: ...


6

You can also use Pivot assuming you are on at least SQL Server 2005 SELECT [1389] AS algodata, [0877] AS binnet, [0736] AS newmoon FROM titles PIVOT (COUNT(title_id) FOR pub_id IN ([1389], [0877], [0736])) P



Only top voted, non community-wiki answers of a minimum length are eligible