Questions tagged [select]

A core SQL statement, SELECT retrieves data from one or more tables or other sources of row set data such as views or table-valued functions.

Filter by
Sorted by
Tagged with
111 votes
3 answers
168k views

What is the default order of records for a SELECT statement in MySQL?

Suppose you have the following table and data: create table t ( k int, v int, index k(k) ) engine=memory; insert into t (k, v) values (10, 1), (10, 2), (10, 3); When ...
daisy's user avatar
  • 1,328
49 votes
8 answers
97k views

Is it possible to mysqldump a subset of a database required to reproduce a query?

Background I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in reproducible research). Question ...
David LeBauer's user avatar
24 votes
3 answers
24k views

How to select specific rows if a column exists or all rows if a column doesn't

I'm writing a script that gets a count of rows for a few tables, however for some tables I want to only get a count of rows where a flag is set (in this case active=1). Is there a way I can do this ...
Matt S.'s user avatar
  • 343
10 votes
1 answer
70k views

Insert into table select * from table vs bulk insert

I just wanted to know that SQL Statement INSERT INTO TABLE1 SELECT * FROM TABLE2, will work same like bulk insert ? If no, Is there any way exclude index while inserting records. Process is inserting ...
Virul Patel's user avatar
5 votes
2 answers
661 views

SQL Server: Order of Rows

We all know that a simple statement such as: SELECT * FROM stuff; should not produce an ordered result. However, when I try to demonstrate this, it always comes out in primary key order. Moreover a ...
Manngo's user avatar
  • 2,819
45 votes
5 answers
48k views

Benefits of using backtick (`) in MySQL queries?

In MySQL we can create queries with or without the backtick (`) symbol. Example: SELECT * FROM TEST; SELECT * FROM `TEST`; Both works fine in mysql-console. Is there any technical difference ...
Satish Pandey's user avatar
3 votes
1 answer
828 views

select for update gives error on indexed column

here is my code: mysql_query("start transaction"); $q="select max(id) from test for update"; $r=mysql_query($q); $row=mysql_fetch_array($r); for ($k=0;$k<100000;$k++) { $q="insert into test values ...
user3731's user avatar
  • 205
16 votes
4 answers
59k views

Recursive self joins

I have a comments table, which can be simplified down to this: comments ======= id user_id text parent_id where parent_id is nullable, but might be a key for its parent comment. Now, how can I ...
Josh's user avatar
  • 163
8 votes
5 answers
9k views

Get a truly RANDOM row from a PostgreSQL table quickly

I always used to do: SELECT column FROM table ORDER BY random() LIMIT 1; For large tables, this was unbearably, impossibly slow, to the point of being useless in practice. That's why I started ...
user avatar
7 votes
1 answer
92k views

Select Multiple Values from Same Column; one sql statment

I am looking to return a list of users from a database. There are hundreds of users in the db. Each user has it's own unique user_id. All of the values that I am trying to return reside in the same ...
brandon-estrella-dev's user avatar
6 votes
1 answer
2k views

How to Choose Between VALUES and SELECT for INSERT?

This answer raised the question for me how to choose between VALUES and SELECT in such a function . Using PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit: ...
Léo Léopold Hertz 준영's user avatar
5 votes
3 answers
9k views

slow query with fulltext and left join

I have 3 tables about 250,000 records. table1 is MyISAM, add fullindex with title and content, add index with pid. table2 and table3 are InnoDB, add index with pid. Only query table3, just cost 0.04 ...
cj333's user avatar
  • 189
0 votes
3 answers
474 views

Selecting multiple rows by given amount of volume

i have a table like this +--------+------------+-----------+ | id | volume | price | +--------+------------+-----------+ | 1 | 1.0000 | 14250.00 | | 2 | 0.2500 | 14251....
memo's user avatar
  • 11
-1 votes
2 answers
23k views

Return hierarchical data from a self-referencing table (parents, children)

I have the following SQL Server 2008 table and data: CREATE TABLE dbo.MyTable (Sno int, refid int); INSERT INTO dbo.MyTable VALUES (17, 8), (16, 8), (15, 7), (14, 7), (13, 6), (12, 6), ...
Krishna Thota's user avatar
43 votes
7 answers
172k views

How to cast an integer to a boolean in a MySQL SELECT clause?

I'm new here so be kind to me. I have the following scenario: I have many tables which, for the sake of simplicity, are represented in a View in my MySQL database. My problem is that I need a value ...
Bruno's user avatar
  • 533
35 votes
1 answer
138k views

MySQL Count rows from another table for each record in table

SELECT student.StudentID, student.`Name`, COUNT(attendance.AttendanceID) AS Total FROM student LEFT JOIN attendance ON student.StudentID = attendance.StudentID I am trying to count the last ...
Ali Shaikh's user avatar
6 votes
1 answer
98k views

How do I select items from a table where a single column must contain two (or more) values?

I have a MySQL database table that references different words and their locations in documents. I want to return the IDs of documents that contain all of the words. Here is an example table. docid ...
user658182's user avatar
5 votes
1 answer
9k views

How can I make a select statement get blocked?

Hello I am trying to intentionally make a SQL select statement get blocked by another simple SQL delete or update statement, for the purpose of learning. I prefer only InnoDB tables. To prepare the ...
davidjhp's user avatar
  • 429
3 votes
1 answer
34k views

Delete from table where multiple fields match select subquery from other table

I want to delete an entry in a table where multiple fields match the results of another select subquery which takes data from another table. This is what I have so far, though it doesn't work: ...
Oskar Persson's user avatar
2 votes
1 answer
4k views

Select with if statement return 'column does not exist'

Database, application. PostgreSQL 11.4 (Ubuntu 11.4-1.pgdg19.04+1), Dbeaver 6.1.1. What I need? Return right values. What I do. Here is part of code. select if(available_count < 5, "neni skladem"...
Stanislav Hosek's user avatar
2 votes
1 answer
162 views

Issue with one query - can some Index help to make it faster?

I am working on some IBM web application and it has one query hard coded in some part of the application which I cannot change or optimize - so modification of the SELECT statement is not possible. ...
Dejan's user avatar
  • 685
2 votes
2 answers
559 views

Select arbitrary element in postgres

I have a table that has path1, path2, and sha1 value. For any values of path2 and sha1, there can be multiple values of path1. I just want one of those paths. I don't really care which one. I'm ...
pedz's user avatar
  • 123
2 votes
1 answer
19k views

ora-01410 invalid RowID sporadically in a select statement

I have a select that looks up some data by a join. The system runs Oracle 10.02g and the select looks like this SELECT distinct t1.crit FROM table_name1 t1 INNER JOIN table_name2 t2 ON t1.crit ...
mruether's user avatar
  • 180
2 votes
3 answers
4k views

How to speed up a multiple select query

EDIT - Putting my whole stored procedure: I have the below stored procedure: CREATE PROCEDURE [dbo].[data_Update] @Owner NVARCHAR(255), @ID VARCHAR(255), @Division VARCHAR(255), @Manager ...
entryton's user avatar
  • 135
1 vote
1 answer
3k views

Error 1052 Field is Ambiguous using Inner Join Function

Error '1052' Field is ambiguous. I understand the error that is happening, and I've looked at several places for the fix and then applied the inner join function in attempt to fix it. It's still not ...
Konfu Chicken's user avatar
0 votes
3 answers
14k views

Is there a default SELECT order? [duplicate]

I need to clear up what I mean by order here. I am simply asking about what order the data is fetched in. If I simply SELECT * FROM table; in SQL Server without an ORDER BY clause, I find that the ...
Manngo's user avatar
  • 2,819
-1 votes
2 answers
1k views

Select multiple rows with different criteria for each [closed]

I am trying to put together a query will return sales information across multiple date ranges. I was planning to do something like this: SELECT count(*) AS thirty_day_sales FROM "MySchema"."MyTable" ...
Joe M's user avatar
  • 121
63 votes
2 answers
74k views

Reference column alias in same SELECT list

I'm converting an old MS-Access-based system to PostgreSQL. In Access, fields that were made up in SELECTs could be used as parts of equations for later fields, like this: SELECT samples.id, ...
wizpig64's user avatar
  • 733
40 votes
1 answer
18k views

USING construct in JOIN clause can introduce optimization barriers in certain cases?

It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases. I mean this key word: SELECT * ...
Erwin Brandstetter's user avatar
35 votes
3 answers
5k views

Why does this query work?

I have two tables, table_a (id, name) and table_b (id), let's say on Oracle 12c. Why does this query not return an exception? select * from table_a where name in (select name from table_b); From ...
eagerMoose's user avatar
34 votes
2 answers
317k views

oracle - list users with access to certain tables

I'm sure this has been asked before but I can't seem to find the relevant details for the following. Is there some sort of pre-built table that can do the following (I have used dba_tab_privs but it ...
dgf's user avatar
  • 359
21 votes
1 answer
28k views

Why is "select *" faster than "select top 500 *" in SQL Server? [duplicate]

I have a view, complicated_view -- there are a few joins and where clauses. Now, select * from complicated_view (9000 records) is faster, much faster, than select top 500 * from complicated_view ...
user45867's user avatar
  • 1,683
16 votes
2 answers
30k views

Multiple operations using WITH

Is there a way to execute multiple operations using the WITH statement? Something like WITH T AS ( SELECT * FROM Tbl ) BEGIN OPEN P_OUTCURSOR FOR SELECT * FROM T; SELECT COUNT(*) INTO ...
BrunoLM's user avatar
  • 3,463
12 votes
2 answers
13k views

MySQL - UUID/created_at cursor based pagination?

For a large dataset, paginating with an OFFSET is known to be slow and not the best way to paginate. A much better way to paginate is with a cursor, which is just a unique identifier on the row so we ...
Wonka's user avatar
  • 145
10 votes
4 answers
16k views

show table name + number of records for each table in a mysql innodb database

How to list all tables in the current database, together the number of rows of the table. In other words, can you think of a query to come up with something like this in mysql? +---------------------...
sjdh's user avatar
  • 747
10 votes
2 answers
55k views

Select query takes more time than it should

I have a MySQL database table with nearly 23 million records. This table has no primary key, because nothing is unique. It has 2 columns, both are indexed. Below is its structure: Below is some of ...
PeakGen's user avatar
  • 223
9 votes
2 answers
21k views

Why can I select all fields when grouping by primary key but not when grouping by another column

How is this a valid statement (where id is the primary key of the table): select * from table group by id ; and this is not: select * from table group by name ; ERROR: column "pgluser.id" must ...
Sachin Verma's user avatar
8 votes
1 answer
49k views

Multiple "GROUP_CONCAT"s with WHERE clause

In a table as id name type info 1 BMW car yes 2 Reno car no 3 IBM electronics no 4 Sony electronics yes 5 Mazda ...
Googlebot's user avatar
  • 4,521
8 votes
2 answers
48k views

How to use variables inside a select (SQL Server)?

If I want to compute a column and use the result in more than 1 column, how do I do this without making the computing twice? Example: SELECT LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com' as ...
yellowblood's user avatar
7 votes
2 answers
85k views

Select column names whose entries are not null

I would like to have a list of those columns of a table that have at least one not-NULL data entries in them. In other words, I would like to get the column names for which the following returns at ...
Baz's user avatar
  • 165
5 votes
7 answers
52k views

Retrieving row count without using Count function

I wrote a query below which shoots me a syntax error why would it do so, SELECT MAX('Row') FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) 'Row' FROM USERS) Error Desc: Incorrect syntax near ')'....
Deeptechtons's user avatar
5 votes
3 answers
41k views

Influxdb and Grafana combine multiple SELECT

I have some network counters in Influx that I graph with Grafana. I'm trying to combine stats from a couple ethernet interfaces and combine them to show aggregate bandwidth. These two separate ...
Mike's user avatar
  • 71
5 votes
1 answer
4k views

PostgreSQL converting undefined JSONB fields into a row dynamically

I have a table with a field of type jsonb, I would like to split the column of field jsonb for all the json keys. This column lacks a schema. For instance: From CREATE TABLE v(id,jsonb) AS VALUES ...
Cyberguille's user avatar
5 votes
1 answer
4k views

Fast alternative for "NOT IN"

I have a table A that got a field called id that is the primary key of this table. I also have a table called B which also have a field called id as primary key. Now I want to get all rows from ...
Johnny000's user avatar
  • 207
4 votes
2 answers
3k views

Has SELECT an INSERT clause?

I know the form Select * into new_table from old_table statement. Bur recently I found 2 posts here and here which use it like SELECT * FROM NhlPlayer INSERT INTO PlayerBackups I find nothing in ...
bernd_k's user avatar
  • 12.2k
4 votes
2 answers
3k views

select specified number of unique IDs where second column is unique

Look at the following example starting from the top row (id=9) and work your way down, selecting a limit of 4 rows that have sec's that we have not yet seen. We "select" id=9 because we don't yet have ...
user664833's user avatar
  • 1,859
3 votes
1 answer
4k views

SELECT Parent-Child with SORT

I have a simple, one level parent child relation table, with following columns: ID_Asset| Parent_ID_Asset | ProductTitle I need output grouped by Parent followed by children, and also sorted by ...
Petr Šimůnek's user avatar
3 votes
3 answers
266 views

Tuning reads (or perhaps the truncate!) for unusual database structure requiring regular truncates

We have an unusual database structure (I leave the reasoning for another question), that in its current form requires a regular truncation and recreation of a lot of rows (about 1 million). We're ...
Alex KeySmith's user avatar
3 votes
2 answers
15k views

Getting repeated value in a group_concat only if ids are different

I have two tables, first the table Product: id|category_id --+----------- 1 | 12345 2 | 12345 3 | 12465 And then a table activity: id|prod_id|activity_type |description --+-------+----------------...
Eldros's user avatar
  • 133
3 votes
1 answer
669 views

Search within same ID across several rows

My table has the following structure: +----+---------+------------+-------------+ | id | word_id | meaning_id | sentence_id | +----+---------+------------+-------------+ | 1 | 1 | 15333 | ...
Sophia's user avatar
  • 33