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.
46,983
questions
2773
votes
27
answers
2.2m
views
How can I prevent SQL injection in PHP?
If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example:
$unsafe_variable = $_POST['user_input'];
...
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
1652
votes
27
answers
2.1m
views
SQL select only rows with max value on a column [duplicate]
I have this table for documents (simplified version here):
id
rev
content
1
1
...
2
1
...
1
2
...
1
3
...
How do I select one row per id and only the greatest rev?
With the above data, the ...
- 29.8k
387
votes
10
answers
479k
views
How can I return pivot table output in MySQL?
If I have a MySQL table looking something like this:
company_name
action
pagecount
Company A
PRINT
3
Company A
PRINT
2
Company A
PRINT
3
Company B
EMAIL
Company B
PRINT
2
Company B
PRINT
2
...
- 5,053
2006
votes
21
answers
1.8m
views
Select first row in each GROUP BY group?
I'd like to select the first row of each set of rows grouped with a GROUP BY.
Specifically, if I've got a purchases table that looks like this:
SELECT * FROM purchases;
My Output:
id
customer
total
...
- 152k
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
1325
votes
34
answers
1.2m
views
Retrieving the last record in each group - MySQL
There is a table messages that contains data as shown below:
Id Name Other_Columns
-------------------------
1 A A_data_1
2 A A_data_2
3 A A_data_3
4 B ...
- 27.2k
375
votes
12
answers
194k
views
Simulating group_concat MySQL function in Microsoft SQL Server 2005?
I'm trying to migrate a MySQL-based app over to Microsoft SQL Server 2005 (not by choice, but that's life).
In the original app, we used almost entirely ANSI-SQL compliant statements, with one ...
- 7,117
259
votes
9
answers
344k
views
SQL Server dynamic PIVOT query?
I've been tasked with coming up with a means of translating the following data:
date category amount
1/1/2012 ABC 1000.00
2/1/2012 DEF 500.00
2/1/2012 ...
- 3,056
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
2418
votes
48
answers
3.3m
views
How to concatenate text from multiple rows into a single text string in SQL Server
Consider a database table holding names, with three rows:
Peter
Paul
Mary
Is there an easy way to turn this into a single string of Peter, Paul, Mary?
- 29.2k
106
votes
2
answers
8k
views
Cleansing User Passwords
How should I escape or cleanse user-provided passwords before I hash them and store them in my database?
When PHP developers consider hashing users' passwords for security purposes, they often tend ...
- 34.3k
5232
votes
28
answers
2.6m
views
What is the difference between "INNER JOIN" and "OUTER JOIN"?
Also, how do LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN fit in?
- 57.2k
1139
votes
41
answers
445k
views
Parameterize an SQL IN clause
How do I parameterize a query containing an IN clause with a variable number of arguments, like this one?
SELECT * FROM Tags
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count ...
- 63.7k
83
votes
5
answers
455k
views
How to include a PHP variable inside a MySQL statement
I'm trying to insert values in the contents table. It works fine if I do not have a PHP variable inside VALUES. When I put the variable $type inside VALUES then this doesn't work. What am I doing ...
- 10.2k
449
votes
17
answers
454k
views
How to create a MySQL hierarchical recursive query?
I have a MySQL table which is as follows:
id
name
parent_id
19
category1
0
20
category2
19
21
category3
20
22
category4
21
...
...
...
Now, I want to have a single MySQL query to which I simply ...
- 4,735
135
votes
5
answers
491k
views
How to use variables in SQL statement in Python?
I have the following Python code:
cursor.execute("INSERT INTO table VALUES var1, var2, var3,")
where var1 is an integer, var2 and var3 are strings.
How can I write the variable names ...
- 1,353
2180
votes
2
answers
2.2m
views
What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? [duplicate]
What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN
in MySQL?
- 33.4k
496
votes
5
answers
1.4m
views
Efficiently convert rows to columns in sql server
I'm looking for an efficient way to convert rows to columns in SQL server, I heard that PIVOT is not very fast, and I need to deal with lot of records.
This is my example:
Id
Value
ColumnName
1
John
...
- 5,003
108
votes
9
answers
45k
views
What is SQL injection? [duplicate]
Can someone explain SQL injection? How does it cause vulnerabilities? Where exactly is the point where SQL is injected?
subu
131
votes
7
answers
160k
views
Why do we always prefer using parameters in SQL statements?
I am very new to working with databases. Now I can write SELECT, UPDATE, DELETE, and INSERT commands. But I have seen many forums where we prefer to write:
SELECT empSalary from employee where salary ...
- 11.5k
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
533
votes
46
answers
954k
views
How do I split a delimited string so I can access individual items?
Using SQL Server, how do I split a string so I can access item x?
Take a string "Hello John Smith". How can I split the string by space and access the item at index 1 which should return "John"?
- 75k
96
votes
1
answer
117k
views
MySQL pivot row into dynamic number of columns
Lets say I have three different MySQL tables:
Table products:
id | name
1 Product A
2 Product B
Table partners:
id | name
1 Partner A
2 Partner B
Table sales:
partners_id | ...
- 1,204
332
votes
9
answers
1.0m
views
Convert Rows to columns using 'Pivot' in SQL Server
I have read the stuff on MS pivot tables and I am still having problems getting this correct.
I have a temp table that is being created, we will say that column 1 is a Store number, and column 2 is a ...
- 3,572
534
votes
14
answers
552k
views
Restrict results to top N rows per group
The following query:
SELECT
year, id, rate
FROM h
WHERE year BETWEEN 2000 AND 2009
ORDER BY id, rate DESC
yields:
year | id | rate
2006 | p01 | 8.0
2003 | p01 | 7.4
2008 | p01 | 6.8
2001 | ...
- 10.6k
270
votes
13
answers
355k
views
Reshape a Table to Convert Rows to Columns
I tried to search posts, but I only found solutions for SQL Server/Access. I need a solution in MySQL (5.X).
I have a table (called history) with 3 columns: hostid, itemname, itemvalue.
If I do a ...
- 5,361
281
votes
7
answers
370k
views
PostgreSQL Crosstab Query
How do I create crosstab queries in PostgreSQL? For example I have the following table:
Section Status Count
A Active 1
A Inactive 2
B Active 4
B ...
user151419
70
votes
4
answers
170k
views
What are good ways to prevent SQL injection? [duplicate]
I have to program an application management system for my OJT company. The front end will be done in C# and the back end in SQL.
Now I have never done a project of this scope before; in school we had ...
- 3,092
1598
votes
8
answers
338k
views
What are the options for storing hierarchical data in a relational database?
Good Overviews
Generally speaking, you're making a decision between fast read times (for example, nested set) or fast write times (adjacency list). Usually, you end up with a combination of the ...
1485
votes
16
answers
903k
views
Can I concatenate multiple MySQL rows into one field?
Using MySQL, I can do something like:
SELECT hobbies FROM peoples_hobbies WHERE person_id = 5;
My Output:
shopping
fishing
coding
but instead I just want 1 row, 1 col:
Expected Output:
shopping, ...
- 32k
192
votes
12
answers
234k
views
Get top n records for each group of grouped results [duplicate]
The following is the simplest possible example, though any solution should be able to scale to however many n top results are needed:
Given a table like that below, with person, group, and age ...
- 179k
334
votes
26
answers
742k
views
ROW_NUMBER() in MySQL
Is there a nice way in MySQL to replicate the SQL Server function ROW_NUMBER()?
For example:
SELECT
col1, col2,
ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS intRow
...
- 16.6k
1340
votes
16
answers
2.4m
views
How do I limit the number of rows returned by an Oracle query after ordering?
Is there a way to make an Oracle query behave like it contains a MySQL limit clause?
In MySQL, I can do this:
select *
from sometable
order by name
limit 20,10
to get the 21st to the 30th rows (skip ...
- 16.3k
495
votes
12
answers
231k
views
Explicit vs implicit SQL joins
Is there any efficiency difference in an explicit vs implicit inner join?
For example:
SELECT * FROM
table a INNER JOIN table b
ON a.id = b.id;
vs.
SELECT a.*, b.*
FROM table a, table b
WHERE a.id =...
- 51.9k
1119
votes
12
answers
719k
views
INNER JOIN ON vs WHERE clause
For simplicity, assume all relevant fields are NOT NULL.
You can do:
SELECT
table1.this, table2.that, table2.somethingelse
FROM
table1, table2
WHERE
table1.foreignkey = table2.primarykey
...
- 16.4k
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
191
votes
27
answers
669k
views
T-SQL split string
I have a SQL Server 2008 R2 column containing a string which I need to split by a comma. I have seen many answers on StackOverflow but none of them works in R2. I have made sure I have select ...
- 2,157
259
votes
5
answers
226k
views
Are PostgreSQL column names case-sensitive?
I have a db table say, persons in Postgres handed down by another team that has a column name say, "first_Name". Now am trying to use PG commander to query this table on this column-name.
select * ...
- 3,896
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
305
votes
16
answers
112k
views
Why is SELECT * considered harmful?
Why is SELECT * bad practice? Wouldn't it mean less code to change if you added a new column you wanted?
I understand that SELECT COUNT(*) is a performance problem on some DBs, but what if you ...
- 22.5k
4265
votes
40
answers
5.3m
views
How do I UPDATE from a SELECT in SQL Server?
In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement:
INSERT INTO Table (col1, col2, col3)
SELECT col1, col2, col3
FROM other_table
WHERE sql = 'cool'
Is it ...
- 44.9k
377
votes
16
answers
1.1m
views
Update statement with inner join on Oracle
I have a query which works fine in MySQL, but when I run it on Oracle I get the following error:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ...
- 4,187
8
votes
1
answer
8k
views
SQL to LINQ with multiple join, count and left join
I wrote this SQL request with multiple JOIN (including a LEFT JOIN).
It gives me the expected result.
SELECT DISTINCT c.Id,
c.Title,
COUNT(v.Id) AS 'Nb_V2',
COUNT(DISTINCT v....
- 447
1090
votes
12
answers
1.1m
views
How can I do 'insert if not exists' in MySQL?
I started by googling and found the article How to write INSERT if NOT EXISTS queries in standard SQL which talks about mutex tables.
I have a table with ~14 million records. If I want to add more ...
- 33.1k
194
votes
15
answers
322k
views
Calculate a Running Total in SQL Server
Imagine the following table (called TestTable):
id somedate somevalue
-- -------- ---------
45 01/Jan/09 3
23 08/Jan/09 5
12 02/Feb/09 0
77 14/Feb/09 7
39 20/...
- 22.9k
509
votes
18
answers
736k
views
Error related to only_full_group_by when executing a query in MySql
I have upgraded my system and have installed MySql 5.7.9 with php for a web application I am working on. I have a query that is dynamically created, and when run in older versions of MySQL it works ...
- 5,365
494
votes
23
answers
869k
views
How to use GROUP BY to concatenate strings in SQL Server?
How do I get:
id Name Value
1 A 4
1 B 8
2 C 9
to
id Column
1 A:4, B:8
2 C:9
- 15.6k
1841
votes
35
answers
2.6m
views
Insert results of a stored procedure into a temporary table
How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]?
Select all data from BusinessLine into tmpBusLine works fine.
select *
into ...
- 21.6k