All Questions
13,993
questions
1210
votes
29
answers
2.6m
views
SQL Update from One Table to Another Based on a ID Match
I have a database with account numbers and card numbers. I match these to a file to update any card numbers to the account number so that I am only working with account numbers.
I created a view ...
Boerseun
1011
votes
15
answers
4.4m
views
SQL SELECT WHERE field contains words
I need a select which would return results like this:
SELECT * FROM MyTable WHERE Column1 CONTAINS 'word1 word2 word3'
And I need all results, i.e. this includes strings with 'word2 word3 word1' or '...
- 14.5k
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
489
votes
19
answers
1.1m
views
MySQL: Select DISTINCT / UNIQUE, but return all columns?
SELECT DISTINCT field1, field2, field3, ......
FROM table;
I am trying to accomplish the following SQL statement, but I want it to return all columns.
Is this possible?
Something like this:
SELECT ...
- 77.1k
478
votes
13
answers
410k
views
SQL join: selecting the last records in a one-to-many relationship
Suppose I have a table of customers and a table of purchases. Each purchase belongs to one customer. I want to get a list of all customers along with their last purchase in one SELECT statement. What ...
- 7,817
466
votes
6
answers
924k
views
SQL query return data from multiple tables
I would like to know the following:
how to get data from multiple tables in my database?
what types of methods are there to do this?
what are joins and unions and how are they different from one ...
403
votes
6
answers
1.6m
views
Convert INT to VARCHAR SQL
I am using Sybase and I am doing a select which returns me a column called "iftype", but its type is int and I need to convert into varchar. When I try to do the select without the convert function I ...
- 4,513
321
votes
12
answers
799k
views
MySQL SELECT only not null values
Is it possible to do a select statement that takes only NOT NULL values?
Right now I am using this:
SELECT * FROM table
And then I have to filter out the null values with a php loop.
Is there a ...
- 7,281
279
votes
4
answers
159k
views
COUNT(*) vs. COUNT(1) vs. COUNT(pk): which is better? [duplicate]
I often find these three variants:
SELECT COUNT(*) FROM Foo;
SELECT COUNT(1) FROM Foo;
SELECT COUNT(PrimaryKey) FROM Foo;
As far as I can see, they all do the same thing, and I find myself using the ...
- 137k
277
votes
10
answers
675k
views
SQL WHERE ID IN (id1, id2, ..., idn)
I need to write a query to retrieve a big list of ids.
We do support many backends (MySQL, Firebird, SQLServer, Oracle, PostgreSQL ...) so I need to write a standard SQL.
The size of the id set could ...
- 31.2k
275
votes
5
answers
961k
views
JOIN two SELECT statement results
Is it possible to join the results of 2 sql SELECT statements in one statement?
I have a database of tasks where each record is a separate task, with deadlines (and a PALT, which is just an INT of ...
- 3,109
246
votes
17
answers
249k
views
How to select multiple rows filled with constants?
Selecting constants without referring to a table is perfectly legal in an SQL statement:
SELECT 1, 2, 3
The result set that the latter returns is a single row containing the values. I was wondering ...
- 43.1k
235
votes
10
answers
1.0m
views
Best way to do nested case statement logic in SQL Server
I'm writing an SQL Query, where a few of the columns returned need to be calculated depending on quite a lot of conditions.
I'm currently using nested case statements, but its getting messy. Is there ...
- 5,733
218
votes
49
answers
114k
views
Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc
I've heard that SELECT * is generally bad practice to use when writing SQL commands because it is more efficient to SELECT columns you specifically need.
If I need to SELECT every column in a table, ...
215
votes
16
answers
657k
views
Fastest way to determine if record exists
As the title suggests... I'm trying to figure out the fastest way with the least overhead to determine if a record exists in a table or not.
Sample query:
SELECT COUNT(*) FROM products WHERE ...
- 14k
213
votes
9
answers
490k
views
Return Boolean Value on SQL Select Statement
How to return a boolean value on SQL Select Statement?
I tried this code:
SELECT CAST(1 AS BIT) AS Expr1
FROM [User]
WHERE (UserID = 20070022)
And it only returns TRUE if the UserID exists on the ...
- 3,502
171
votes
3
answers
324k
views
MySQL Select Query - Get only first 10 characters of a value
Ok, so here is the issue.
I have a table with some columns and 'subject' is one of the columns.
I need to get the first 10 letters from the 'subject' field no matter the 'subject' field contains a ...
- 2,662
165
votes
12
answers
318k
views
MySQL INNER JOIN select only one row from second table
I have a users table and a payments table, for each user, those of which have payments, may have multiple associated payments in the payments table. I would like to select all users who have payments, ...
- 5,033
165
votes
7
answers
421k
views
How to select only the first rows for each unique value of a column?
Let's say I have a table of customer addresses:
+-----------------------+------------------------+
| CName | AddressLine |
+-----------------------+------------------------+
...
- 1,743
154
votes
8
answers
191k
views
Ordering by specific field value first
I have a table with 3 columns:
id
name
priority
1
core
10
2
core
9
3
other
8
4
board
7
5
board
6
6
core
4
I want to order the result set using priority but first those rows that have name=core ...
- 4,625
146
votes
4
answers
171k
views
SQLite select where empty?
In SQLite, how can I select records where some_column is empty?
Empty counts as both NULL and "".
- 54.5k
130
votes
10
answers
29k
views
SELECT * FROM tablename WHERE 1
I've been curious. What are the differences between these respective queries:
SELECT * FROM `tablename`
SELECT * FROM `tablename` WHERE 1
SELECT * FROM `tablename` WHERE 1=1
- 1,283
129
votes
10
answers
114k
views
SQL SELECT speed int vs varchar
I'm in the process of creating a table and it made me wonder.
If I store, say cars that has a make (fx BMW, Audi ect.), will it make any difference on the query speed if I store the make as an int or ...
- 33.2k
125
votes
1
answer
273k
views
Include in SELECT a column that isn't actually in the database
I'm trying to execute a SELECT statement that includes a column of a static string value. I've done this in Access, but never with raw SQL. Is this possible?
Example:
Name | Status
------+------...
- 2,637
122
votes
16
answers
398k
views
How to select bottom most rows?
I can do SELECT TOP (200) ... but why not BOTTOM (200)?
Well not to get into philosophy what I mean is, how can I do the equivalent of TOP (200) but in reverse (from the bottom, like you'd expect ...
- 43.3k
119
votes
6
answers
639k
views
How to write a SQL DELETE statement with a SELECT statement in the WHERE clause?
Database: Sybase Advantage 11
On my quest to normalize data, I am trying to delete the results I get from this SELECT statement:
SELECT tableA.entitynum
FROM tableA q
INNER JOIN tableB u on (u....
- 1,331
109
votes
4
answers
246k
views
How to delete from select in MySQL?
This code doesn't work for MySQL 5.0, how to re-write it to make it work
DELETE FROM posts where id=(SELECT id FROM posts GROUP BY id HAVING ( COUNT(id) > 1 ))
I want to delete columns that dont ...
- 63.4k
107
votes
4
answers
120k
views
How do I select all the columns from a table, plus additional columns like ROWNUM?
In Oracle, it's possible to do a SELECT statement that returns the row number as a column in your result set.
For example,
SELECT rownum, column1, column2 FROM table
returns:
rownum column1 ...
- 2,345
104
votes
3
answers
164k
views
How can I do SELECT UNIQUE with LINQ?
I have a list like this:
Red
Red
Brown
Yellow
Green
Green
Brown
Red
Orange
I am trying to do a SELECT UNIQUE with LINQ, i.e. I want
Red
Brown
Yellow
Green
Orange
var uniqueColors = from dbo in ...
- 11.1k
103
votes
3
answers
120k
views
How to order by column A and then by column B?
How to write SQL so that the result can be ordered first by column A then by column B. Something like below:
SELECT * FROM tbl WHERE predictor ORDER by col_A and ORDER by col_B
- 40.3k
100
votes
6
answers
279k
views
SQL column reference "id" is ambiguous
I tried the following select:
SELECT (id,name) FROM v_groups vg
INNER JOIN people2v_groups p2vg ON vg.id = p2vg.v_group_id
WHERE p2vg.people_id =0;
And, I get the following error column reference id ...
- 3,458
98
votes
2
answers
89k
views
How to remove duplicates, which are generated with array_agg postgres function
Does anyone an idea how to rewrite following SQL query to generate results, that would contains only one occurrence of name? (results grouped by user).
The query
SELECT array_to_string(array_agg(...
- 2,766
89
votes
11
answers
96k
views
How do I find records that are not joined?
I have two tables that are joined together.
A has many B
Normally you would do:
select * from a,b where b.a_id = a.id
To get all of the records from a that has a record in b.
How do I get just ...
- 13.1k
87
votes
3
answers
187k
views
Add row to query result using select
Is it possible to extend query results with literals like this?
select name from users
union
select name from ('JASON');
or
select age, name from users
union
select age, name from (25,'Betty');
so ...
jdsushi
82
votes
35
answers
401k
views
SQL query to check if a name begins and ends with a vowel
I want to query the list of CITY names from the table STATION(id, city, longitude, latitude) which have vowels as both their first and last characters. The result cannot contain duplicates.
For this ...
- 2,233
80
votes
8
answers
601k
views
How to select records without duplicate on just one field in SQL?
I have a table with 3 columns like this:
+------------+---------------+-------+
| Country_id | country_title | State |
+------------+---------------+-------+
There are many records in this ...
- 13k
76
votes
3
answers
231k
views
MySQL error 1241: Operand should contain 1 column(s)
I am trying to Insert data from a table1 into table2
insert into table2(Name,Subject,student_id,result)
select (Name,Subject,student_id,result)
from table1;
Key for table2 is student_id.
Assume ...
- 1,253
75
votes
7
answers
549k
views
SQL SELECT from multiple tables
How can I get all products from customers1 and customers2 include their customer names?
customer1 table
cid name1
1 john
2 joe
customer2 table
cid name2
p1 sandy
p2 linda
product table
pid ...
- 763
74
votes
3
answers
422k
views
SQL User Defined Function Within Select
I have a user defined function in SQL called getBuisnessDays it takes @startdate and @enddate and returns the number of business days between the two dates. How can I call that function within my ...
- 8,164
73
votes
10
answers
191k
views
MySQL INSERT INTO ... VALUES and SELECT
Is there a way to insert pre-set values and values I get from a select-query?
For example:
INSERT INTO table1 VALUES ("A string", 5, [int]).
I have the value of "A string" and the number 5, but I've ...
- 956
72
votes
10
answers
570k
views
SQL: Two select statements in one query
I want to select information from two SQL tables within one query, the information is unrelated though, so no potential joints exist.
An example could be the following setup.
tblMadrid
id | name ...
- 1,013
71
votes
9
answers
280k
views
Find all stored procedures that reference a specific column in some table
I have a value in a table that was changed unexpectedly. The column in question is CreatedDate, which is set when my item is created, but it's being changed by a stored procedure.
Could I write some ...
- 14.9k
69
votes
2
answers
148k
views
mysql SELECT IF statement with OR
The following works - returns Y when chargeback equal to 1 else it defaults to N
IF(fd.charge_back = 1, 'Y', 'N') AS charge_back
however I cannot seem to get this one working? Is the syntax valid
...
- 11.8k
69
votes
3
answers
110k
views
Select columnValue if the column exists otherwise null
I'm wondering if I can select the value of a column if the column exists and just select null otherwise. In other words I'd like to "lift" the select statement to handle the case when the column ...
- 16.9k
67
votes
8
answers
270k
views
SQL How to replace values of select return?
In my database table (MySQL), there is a column with 1 and 0 to represent true and false respectively.
But in SELECT, I need to replace it for true or false in order to print in a GridView.
How do I ...
- 8,348
66
votes
7
answers
219k
views
How can I select from list of values in Oracle
I am referring to this stackoverflow answer:
How can I select from list of values in SQL Server
How could something similar be done in Oracle?
I've seen the other answers on this page that use UNION ...
- 11.9k
66
votes
6
answers
135k
views
Do all columns in a SELECT list have to appear in a GROUP BY clause
My lecturer stated:
All column names in SELECT list must appear in GROUP BY clause unless name is used only in an aggregate function.
I'm just wanting some confirmation of this as I cannot think of ...
- 12.4k
64
votes
20
answers
7k
views
Can select * usage ever be justified?
I've always preached to my developers that SELECT * is evil and should be avoided like the plague.
Are there any cases where it can be justified?
I'm not talking about COUNT(*) - which most ...
- 21.6k
63
votes
4
answers
561k
views
If statement in select (ORACLE)
Hi I have simply select and works great:
select 'CARAT Issue Open' issue_comment, i.issue_id, i.issue_status, i.issue_title, i.ISSUE_summary ,i.issue_description, i.severity,
gcrs.Area_name, gcrs....
- 3,098
62
votes
3
answers
217k
views
SQL select everything in an array
My homework has a problem for example there is a category array $cat=array('1','4','5','7'); now i need to select products from db based on the category that is
SELECT * FROM products WHERE catid='1'
...
- 1,029