Hot answers tagged select
24
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> [ ...
13
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 ...
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
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 ...
10
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 ...
10
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
10
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 ...
10
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 ...
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
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
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 ...
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
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 ...
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
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
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, ...
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 ...
6
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 ...
6
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 ...
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
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
6
Several problems with your trigger:
You are assuming the trigger fires per row, whereas triggers fire per statement. This means you can't just assign a variable because this won't work if the update affects more than one row.
Your query to assign a variable doesn't pull from anywhere - you need to reference the inserted and/or deleted pseudo-tables.
...
6
Prior to MySQL 5.6, the inner query is executed once per entry in the outer row and is easy to prove:
mysql> SELECT COUNT(*) FROM sample_data;
+----------+
| COUNT(*) |
+----------+
| 8 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM sample_data WHERE id = (SELECT SLEEP(1) );
+----------+
| COUNT(*) |
+----------+
| 0 ...
6
(SELECT courses FROM wp_category WHERE CatID =401)
OR
(SELECT meta_value FROM wp_postmeta WHERE post_id IN (SELECT courses FROM wp_category WHERE CatID =401) AND meta_key ='post_id' )
This is a condition, but you need n values.
This should work:
(
ID IN
(
(SELECT courses FROM wp_category WHERE CatID ...
6
Are there other processes operating on the same tables? Indexes being rebuilt?
If so, then you could be hitting the situation that Jonathan Lewis describes here: http://jonathanlewis.wordpress.com/2007/09/16/index-rebuild/
6
You simply need to think about the date range slightly differently. An event falls in your given range if the start date is prior to the end of your reporting period, and the end date is after the beginning of your reporting period:
Select * from tbl_Events
where StartDate <= @end
AND EndDate >= @start;
For example:
CREATE TABLE MyTestDates
(
...
6
The advantage to dual is the optimizer understands dual is a special one row, one
column table (with varchar2 datatype) -- when you use it in queries, it uses this knowledge when developing the
plan.
Why do we need to select from DUAL in Oracle?
you can select from dual or from your own tables too, you can if you want.
for me, i'll stick with dual. ...
Only top voted, non community-wiki answers of a minimum length are eligible
