Questions tagged [sql]
Structured Query Language (SQL) is a language for querying databases. Questions should include code examples, table structure, sample data, and a tag for the DBMS implementation (e.g. MySQL, PostgreSQL, Oracle, MS SQL Server, IBM DB2, etc.) being used. If your question relates solely to a specific DBMS (uses specific extensions/features), use that DBMS's tag instead. Answers to questions tagged with SQL should use ISO/IEC standard SQL.
671,329
questions
911
votes
22
answers
1.9m
views
How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?
I have a table of player performance:
CREATE TABLE TopTen (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
home INT UNSIGNED NOT NULL,
`datetime`DATETIME NOT NULL,
player VARCHAR(6) NOT NULL,
...
- 9,811
896
votes
14
answers
1.2m
views
Should I use != or <> for not equal in T-SQL?
I have seen SQL that uses both != and <> for not equal. What is the preferred syntax and why?
I like !=, because <> reminds me of Visual Basic.
- 20.5k
891
votes
17
answers
663k
views
DateTime2 vs DateTime in SQL Server
Which one:
datetime
datetime2
is the recommended way to store date and time in SQL Server 2008+?
I'm aware of differences in precision (and storage space probably), but ignoring those for now, is ...
- 10.6k
880
votes
15
answers
1.1m
views
How can I do a FULL OUTER JOIN in MySQL?
I want to do a full outer join in MySQL. Is this possible? Is a full outer join supported by MySQL?
- 21.9k
879
votes
13
answers
526k
views
Count(*) vs Count(1) - SQL Server
Just wondering if any of you people use Count(1) over Count(*) and if there is a noticeable difference in performance or if this is just a legacy habit that has been brought forward from days gone ...
- 29.7k
847
votes
38
answers
1.3m
views
How do I get the query builder to output its raw SQL query as a string?
Given the following code:
DB::table('users')->get();
I want to get the raw SQL query string that the database query builder above will generate. In this example, it would be SELECT * FROM users.
...
- 11.4k
826
votes
9
answers
899k
views
How can I temporarily disable a foreign key constraint in MySQL?
Is it possible to temporarily disable constraints in MySQL?
I have two Django models, each with a foreign key to the other one. Deleting instances of a model returns an error because of the foreign ...
- 36.9k
813
votes
13
answers
1.1m
views
SQL update query using joins
I have to update a field with a value which is returned by a join of 3 tables.
Example:
select
im.itemid
,im.sku as iSku
,gm.SKU as GSKU
,mm.ManufacturerId as ManuId
,mm....
- 217k
808
votes
32
answers
580k
views
Fastest Way of Inserting in Entity Framework
I'm looking for the fastest way of inserting in Entity Framework.
I'm asking this because of the scenario where you have an active TransactionScope and the insertion is huge (4000+). It can ...
- 9,530
803
votes
11
answers
1.5m
views
Rename column SQL Server 2008
I am using SQL Server 2008 and Navicat. I need to rename a column in a table using SQL.
ALTER TABLE table_name RENAME COLUMN old_name to new_name;
This statement doesn't work.
- 9,512
798
votes
19
answers
896k
views
MySQL Query GROUP BY day / month / year
Is it possible to make a simple query to count how many records I have in a determined period of time like a year, month, or day, having a TIMESTAMP field, like:
SELECT COUNT(id)
FROM stats
WHERE ...
- 12.8k
797
votes
17
answers
843k
views
MySQL: Get character-set of database or table or column?
What is the (default) charset for:
MySQL database
MySQL table
MySQL column
- 59.5k
788
votes
13
answers
314k
views
When to use single quotes, double quotes, and backticks in MySQL
I am trying to learn the best way to write queries. I also understand the importance of being consistent. Until now, I have randomly used single quotes, double quotes, and backticks without any real ...
- 27k
786
votes
4
answers
313k
views
SQL injection that gets around mysql_real_escape_string()
Is there an SQL injection possibility even when using mysql_real_escape_string() function?
Consider this sample situation. SQL is constructed in PHP like this:
$login = mysql_real_escape_string(...
- 82.4k
778
votes
19
answers
998k
views
Get top 1 row of each group
I have a table which I want to get the latest entry for each group. Here's the table:
DocumentStatusLogs Table
ID
DocumentID
Status
DateCreated
2
1
S1
7/29/2011
3
1
S2
7/30/2011
6
1
S1
8/02/2011
...
- 29.7k
763
votes
8
answers
1.6m
views
'IF' in 'SELECT' statement - choose output value based on column values
SELECT id, amount FROM report
I need amount to be amount if report.type='P' and -amount if report.type='N'. How do I add this to the above query?
- 14k
757
votes
8
answers
724k
views
Insert text with single quotes in PostgreSQL
I have a table test(id,name).
I need to insert values like: user's log, 'my user', customer's.
insert into test values (1,'user's log');
insert into test values (2,''my users'');
insert into ...
- 9,753
752
votes
15
answers
1.5m
views
How to select all records from one table that do not exist in another table?
table1 (id, name)
table2 (id, name)
Query:
SELECT name
FROM table2
-- that are not in table1 already
- 17.4k
746
votes
10
answers
920k
views
Create new user in MySQL and give it full access to one database
I want to create a new user in MySQL and give it full access only to one database, say dbTest that I create with a command like create database dbTest;. What would be the MySQL commands to do that?
- 7,477
738
votes
12
answers
426k
views
Postgres: upgrade a user to be a superuser?
In postgres, how do I change an existing user to be a superuser? I don't want to delete the existing user, for various reasons.
# alter user myuser ...?
- 10.7k
738
votes
22
answers
930k
views
Solutions for INSERT OR UPDATE on SQL Server
Assume a table structure of MyTable(KEY, datafield1, datafield2...).
Often I want to either update an existing record, or insert a new record if it doesn't exist.
Essentially:
IF (key exists)
run ...
- 30k
737
votes
35
answers
448k
views
How to reset Postgres' primary key sequence when it falls out of sync?
I ran into the problem that my primary key sequence is not in sync with my table rows.
That is, when I insert a new row I get a duplicate key error because the sequence implied in the serial ...
- 32.8k
698
votes
11
answers
1.5m
views
How to insert a line break in a SQL Server VARCHAR/NVARCHAR string
I didn't see any similar questions asked on this topic, and I had to research this for something I'm working on right now. Thought I would post the answer for it in case anyone else had the same ...
- 33.3k
678
votes
12
answers
459k
views
How to skip certain database tables with mysqldump?
Is there a way to restrict certain tables from the mysqldump command?
For example, I'd use the following syntax to dump only table1 and table2:
mysqldump -u username -p database table1 table2 > ...
674
votes
35
answers
693k
views
Fetch the rows which have the Max value for a column for each distinct value of another column
Table:
UserId, Value, Date.
I want to get the UserId, Value for the max(Date) for each UserId. That is, the Value for each UserId that has the latest date.
How do I do this in SQL? (Preferably Oracle....
- 6,775
670
votes
16
answers
553k
views
Export schema without data
I'm using a MySql database with a Java program, now I want to give the program to somebody else.
How to export the MySQL database structure without the data in it, just the structure?
- 9,615
669
votes
19
answers
1.1m
views
How do I restore a dump file from mysqldump?
I was given a MySQL database file that I need to restore as a database on my Windows Server 2008 machine.
I tried using MySQL Administrator, but I got the following error:
The selected file was ...
- 56.6k
667
votes
26
answers
514k
views
How to insert multiple rows in SQLite?
In MySQL you can insert multiple rows like this:
INSERT INTO 'tablename' ('column1', 'column2') VALUES
('data1', 'data2'),
('data1', 'data2'),
('data1', 'data2'),
('data1', 'data2');
...
- 233k
639
votes
31
answers
671k
views
Is there a Max function in SQL Server that takes two values like Math.Max in .NET?
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 ...
- 30.6k
639
votes
11
answers
1.9m
views
NOT IN vs NOT EXISTS
Which of these queries is the faster?
NOT EXISTS:
SELECT ProductID, ProductName
FROM Northwind..Products p
WHERE NOT EXISTS (
SELECT 1
FROM Northwind..[Order Details] od
WHERE p....
- 16.3k
625
votes
19
answers
387k
views
UPSERT *not* INSERT or REPLACE
http://en.wikipedia.org/wiki/Upsert
Insert Update stored proc on SQL Server
Is there some clever way to do this in SQLite that I have not thought of?
Basically I want to update three out of four ...
- 8,644
625
votes
7
answers
1.6m
views
How to turn IDENTITY_INSERT on and off using SQL Server 2008?
Why am I getting an error doing an insert when IDENTITY_INSERT is set to OFF?
How do I turn it on properly in SQL Server 2008? Is it by using SQL Server Management Studio?
I have run this query:
...
- 29k
613
votes
9
answers
1.3m
views
UPDATE and REPLACE part of a string
I've got a table with two columns, ID and Value. I want to change a part of some strings in the second column.
Example of Table:
ID Value
---------------------------------
1 c:...
- 6,693
611
votes
32
answers
589k
views
How to request a random row in SQL?
How can I request a random row (or as close to truly random as possible) in pure SQL?
- 16.7k
609
votes
23
answers
998k
views
Best approach to remove time part of datetime in SQL Server
Which method provides the best performance when removing the time portion from a datetime field in SQL Server?
a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
or
b) select cast(convert(char(11)...
- 6,753
607
votes
11
answers
3.1m
views
OR is not supported with CASE Statement in SQL Server
The OR operator in the WHEN clause of a CASE statement is not supported. How can I do this?
CASE ebv.db_no
WHEN 22978 OR 23218 OR 23219 THEN 'WECS 9500'
ELSE 'WECS 9520'
END as wecs_system
- 6,185
605
votes
19
answers
682k
views
What is the best way to paginate results in SQL Server
What is the best way (performance wise) to paginate results in SQL Server 2000, 2005, 2008, 2012 if you also want to get the total number of results (before paginating)?
602
votes
25
answers
806k
views
How to get a list of column names on Sqlite3 database?
I want to migrate my iPhone app to a new database version. Since I don't have some version saved, I need to check if certain column names exist.
This Stackoverflow entry suggests doing the select
...
- 6,561
598
votes
7
answers
1.6m
views
Selecting COUNT(*) with DISTINCT
In SQL Server 2005 I have a table cm_production that lists all the code that's been put into production. The table has a ticket_number, program_type, program_name and push_number along with some other ...
- 6,504
589
votes
13
answers
849k
views
Is it possible to specify condition in Count()?
Is it possible to specify a condition in Count()? I would like to count only the rows that have, for example, "Manager" in the Position column.
I want to do it in the count statement, not using WHERE;...
- 15.2k
585
votes
29
answers
1.4m
views
Delete duplicate rows keeping the first row
How can I delete duplicate rows where no unique row id exists?
My table is
col1 col2 col3 col4 col5 col6 col7
john 1 1 1 1 1 1
john 1 1 1 1 1 1
sally 2 2 2 ...
- 10.9k
583
votes
5
answers
1.3m
views
How do I (or can I) SELECT DISTINCT on multiple columns?
I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The ...
- 33.9k
582
votes
9
answers
1.1m
views
How to declare a variable in MySQL?
How to declare a variable in mysql, so that my second query can use it?
I would like to write something like:
SET start = 1;
SET finish = 10;
SELECT * FROM places WHERE place BETWEEN start AND ...
- 25.2k
577
votes
15
answers
141k
views
What is the most efficient/elegant way to parse a flat table into a tree?
Assume you have a flat table that stores an ordered tree hierarchy:
Id Name ParentId Order
1 'Node 1' 0 10
2 'Node 1.1' 1 10
3 'Node 2' 0 ...
- 335k
567
votes
21
answers
1.4m
views
Adding an identity to an existing column
I need to change the primary key of a table to an identity column, and there's already a number of rows in table.
I've got a script to clean up the IDs to ensure they're sequential starting at 1, ...
- 14.7k
560
votes
7
answers
310k
views
554
votes
29
answers
582k
views
MySQL select 10 random rows from 600K rows fast
How can I best write a query that selects 10 rows randomly from a total of 600k?
- 79k
553
votes
6
answers
451k
views
MySQL: @variable vs. variable. What's the difference?
In another question I posted someone told me that there is a difference between:
@variable
and:
variable
in MySQL. He also mentioned how MSSQL has batch scope and MySQL has session scope. Can ...
- 36.7k
552
votes
29
answers
2.0m
views
MySQL Error: : 'Access denied for user 'root'@'localhost'
Consider:
./mysqladmin -u root -p** '_redacted_'
Output (including typing the password):
Enter password:
mysqladmin: connect to server at 'localhost' failed error:
'Access denied for user 'root'@'...
- 5,761
548
votes
8
answers
705k
views
updating table rows in postgres using subquery
I have this table in a postgres 8.4 database:
CREATE TABLE public.dummy
(
address_id SERIAL,
addr1 character(40),
addr2 character(40),
city character(25),
state character(2),
zip character(...
- 6,655