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.

learn more… | top users | synonyms

3
votes
1answer
66 views

PostgresSQL: Get single attribute of UDT in SELECT statement

I created a user-defined type in a PostgreSQL 9.2 database and am trying in the SELECT statement to get only an attribute of the UDT. However, I don't seem to get my code to work. Creation of type ...
2
votes
1answer
491 views

Selecting minimum value using a subquery

I need to write a query to grab the lowest price from each merchant and output the price link (p_link) and some other information such as merchant name + rating. We have a table for prices ...
2
votes
1answer
108 views

Search within same ID accross several rows

My table has the following structure: +----+---------+------------+-------------+ | id | word_id | meaning_id | sentence_id | +----+---------+------------+-------------+ | 1 | 1 | 15333 | ...
1
vote
1answer
130 views

I would like to query a range of criteria on multiple columns in MySQL

I have 3 columns in a mysql table. I'm using Innodb engine. I want to be able to search for some values on those columns and also anything close to those values. For example : We have 3 columns as ...
0
votes
2answers
279 views

Need a way to query a table, and JOIN it with the TOP 1 related record from an other table

As a follow up to this question, I'm wondering if there is a better and/or more efficient way to gather the data in question. As stated, this query does return me 95% of the data I need -- SELECT ...
-1
votes
2answers
362 views

SELECT only values THAT are different

I have two instances user1 and user2. They have colors u_id | color<br> 1 | red<br> 1 | green<br> 2 | red<br> 2 | blue<br> I need a mysql request, that ...
0
votes
2answers
424 views

MySQL Reusing calculation in SELECT clause in a VIEW

I have an SQL query that has a simple date difference between NOW() and the creation date (is in the table). I need to reuse this again within the SELECT clause in a VIEW. Is this possible at all? ...
7
votes
2answers
3k views

SELECTing multiple columns through a subquery

I am trying to SELECT 2 columns from the subquery in the following query, but unable to do so. Tried creating alias table, but still couldn't get them. SELECT DISTINCT petid, userid, (SELECT ...
4
votes
1answer
186 views

Does the order of fields in SELECT query matter when using composite indexing?

I understand that the order of columns in the index itself matters immensely; however, what about the order of columns in the subsequent SELECT queries that make use of that index? For example, if I ...
3
votes
3answers
135 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 it's current form requires a regular truncation and recreation of allot of row (about 1 million). We're ...
1
vote
2answers
1k 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 ...
4
votes
1answer
84 views

Select columns from 4 tables

First thing, I am totally new to SQL. I saw some tutorials, videos, tried searching but didn't find what I needed. I know it has asked before, but I couldn't find it. What I need to find is basically ...
1
vote
1answer
159 views

MySQL SELECT query: i want show duplicated entries

Example My query is: SELECT code_CAT FROM categories WHERE id_code IN(14,14,14,14); output is: 53724 Only one row. Instead I want this output: 53724 53724 53724 53724 How can I achieve ...
2
votes
3answers
846 views

Count consecutive rows in mysql

I would like to get the count of consecutive rows in MySQL. It is a large database I try to avoiding the joins. Here gadget_id means vehicle and every 20/30 second the vehicle sends the current ...
3
votes
2answers
158 views

Inner Join or direct data save in SQL Server

I am developing a social network site and expecting very good response from people so expecting huge users (maybe more than million in years). My website is holding user information table and all its ...
2
votes
1answer
3k 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 ...
0
votes
2answers
494 views

How to mix pairs of rows without JOIN (in a temporary table)?

I have a TEMPORARY table containing pairs of key/value as id col1 col2 type pair 1 key1 key2 key 1 2 value1 value2 value 1 3 key3 ...
1
vote
1answer
249 views

How to re-arrange rows to best fit within groups?

I categorized a table with the method introduced here to categorize my items to folders with a limit size of 100. mixing_order id num_items cat order_in_cat cumulative_sum folder 1 ...
0
votes
1answer
327 views

Retrieving a fixed range of rows from Postgresql

I have a query: select qid,ansid,ans from table1 where askerid='something' This query can retrieve many rows. But I want to display only 50 rows at a time and then if the user clicks on more, the ...
2
votes
1answer
275 views

Help with SELECT queries

I need help with some queries. I struggled do find out how to do it, but I think I finally caught on or at least got the results I was looking for in the database. These are the tables (given by my ...
2
votes
1answer
1k views

MySQL Explain: Using index, using temporary, using filesort. Can this query be improved?

I'm working on a event tracking system which uses a handful of lookup tables as well as the primary logging table. In a report I'm writing, an object can be selected to view statistics against. The ...
1
vote
1answer
617 views

MySQL error in where clause : Unknown column

When I try to execute the query below, I receive the error message: Unknown column 'have_discount' in 'where clause' My query is: SELECT DISTINCT(MP.item_id), item_title, item_user_id, ...
3
votes
1answer
217 views

SELECT multiple sensor values in one query

Background I have a couple of devices, each with a couple of sensors. I log these every now and then and stores them in a table described below. When someone requests a web page, I fetch a couple of ...
2
votes
1answer
86 views

JOIN 2 tables, and query data from them 3 times

Basically I have 2 tables: post_meta and posts. post_meta has a post_id, key and value: post_id key value 6343 make Chevy 6343 model Volt 6794 make Chevy 6794 model ...
0
votes
1answer
603 views

Select fields from multiple tables

I want to make a query a little complicated, and I'm not able to do it alone. I have the following tables: Table color: id_uo P id_cor PF id_area F text_cor Table area: id_area P ...
2
votes
1answer
990 views

I have to update all the rows of a Column in a table. Here is my query

So My tables are People: People_name(VARCHAR 100), People_id (INT, PRIMARY KEY AUTO INCREMENT) Tracks: Track_id(INT PRIMARY KEY), People_id_references (int ,Foreign key references ...
0
votes
0answers
43 views

mysql select query optimization [duplicate]

Possible Duplicate: mysql select query optimization I have two tables, testa and testb. CREATE TABLE `testa` ( `id` INT(10) NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) DEFAULT NULL, ...
2
votes
2answers
144 views

Is it always bad to use SELECT * FROM?

It is widely recommended NOT to use SELECT *. I understand this, as it is not a good idea to bring the entire row into memory, when only a few cells are needed. Is it still bad to use SELECT * when ...
2
votes
2answers
110 views

HELP with SQL WHERE syntax

I have to pull out a range of entries of a database, all based around two pieces of data. In itself thats fine. An example of the data is: Record ID Tags 1 1423 Day, Sun, Warm 2 ...
3
votes
2answers
9k 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 ...
2
votes
5answers
264 views

How define select, if bridge pair may have 3 players?

Normaly a pair has 2 players, but if there is an odd number of players at the club, one 'pair' has 3 players. Table, members: pair, player_id 1 1 1 2 2 3 2 4 3 5 3 6 4 7 4 8 5 9 5 10 6 11 6 12 6 ...
9
votes
3answers
1k views

Why do I have to select from the dual table?

This works in the major Relation Database Management Systems most likely to appear on StackOverflow/dba.stackexchange, being SQL Server, MySQL, PostgreSQL and SQLite (WebSQL). select 'abc' abc, 1 ...
2
votes
2answers
2k views

GROUP BY two columns

I want to count two columns, but need to get the results of first column in one row. SELECT country, COUNT(*) FROM table1 GROUP BY country, type This query gives me country type COUNT(*) ...
2
votes
1answer
292 views

Select consecutive number of rows starting from some number

What I need is to select 100 consecutive rows from a table, starting at some defined point without ordering data. As far as I can observe, inserted data to a table is stored in no order but is ...
1
vote
1answer
659 views

How to COUNT number of rows with LIMIT?

I want to catch X rows, thus, I set LIMIT X; but how can I simultaneously count the total number of rows too? Currently, I use two separate queries to do so as SELECT COUNT(*) FROM col WHERE CLAUSE ...
3
votes
1answer
256 views

Tricky selection of grouped rows, selecting based on values of two distinct but related rows

Say I have a table with the following information (sorted here for display purposes) taskid | reference | centreid | date_out --------------------------------------------------- 10001 | ...
0
votes
2answers
158 views

MySQL: SELECT the n-th item from an ordered result

SELECTing the top n elements from a table is easy: SELECT id FROM pixels WHERE pixel_id='some_pixel_id' ORDER BY id DESC LIMIT 1000; Finding the n-th element with a subquery is quite ...
2
votes
1answer
1k views

Oracle error “too many values”, must be solved with nested SELECTs

I have hit upon a "too many values" error in Oracle, when working with some intricate nested SELECTs. A simplified description of my problem, on a toy problem describing the hierarchy in a company. ...
4
votes
1answer
378 views

Select one name per person where spelling variations exist

I have a source table that contains Employee IDs and names. The employee names may be listed multiple times with variations on the exact spelling (see 'source table' for example - note the middle ...
3
votes
2answers
783 views

Select query according to date

We have a table to store the Events. Table has got a schema like EventID INT Primary Key EventName NVARCHAR(100) StartDate Datetime EndDate Datetime So in the front end we want to show the events ...
2
votes
2answers
600 views

Select rows with repeat data within certain timeframe

As a follow-up question to my previous question on difficult row categorisation, I have another problem which I'm sure will be easily solvable. After this I plan on reading a good book or two on ...
3
votes
1answer
2k views

SELECT LIMIT 1 per column value?

Lets say I have the following table ----------------------------- | user_id | comment | ----------------------------- | 2 | thats cool | | 2 | awesome | | 3 | ...
1
vote
1answer
4k 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 ...
1
vote
1answer
323 views

Merge 2 tables and fetch data one by one

I have two tables: product & offers. products has (prod_id, category_id, prod_name, brand, price, status) offers has (offer_id, prod_id, offer_type, discount_amt, validity) I want to display ...
4
votes
2answers
11k views

How do I use the IF…ELSE condition in a WHERE clause?

I'm attempting to use the IF...ELSE construct in my WHERE clause to selectively apply conditions to my SELECT. Should this work? CREATE OR REPLACE package body If_Else_Pack IS PROCEDURE Moving ...
5
votes
1answer
668 views

Mysql sub query returns more than 1 row

SELECT * FROM wp_posts WHERE ID IN ( (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) ...
2
votes
1answer
1k views

MySQL Insert into two tables using new IDs

I'm trying to select data already in the database and insert it into two tables using the generated ID from table one. table1 contains configuration options, table2 contains pricing data. Table2 uses ...
4
votes
2answers
649 views

WHERE clause is not filtering on LESS THAN and GREATER THAN or BETWEEN

I am trying to select MTR values that are between the values of 0 and 4. What am I doing wrong? SELECT DATEDIFF(M, TheDate, TheOtherDate) AS MTR FROM MyTable WHERE MyID = 2074163 AND MTR >= 0 ...
3
votes
2answers
3k views

How do I get the current and next greater value in one select?

I have a InnoDB table 'idtimes' (MySQL 5.0.22-log) with columns `id` int(11) NOT NULL, `time` int(20) NOT NULL, [...] with a compound unique key UNIQUE KEY `id_time` (`id`,`time`) so there can ...
1
vote
1answer
568 views

How to change the display of an empty string in mysql command line tool?

I'm maintaining a database with InnoDB tables. These tables have some columns of type (from show create table): `val0` varchar(30) default NULL, `val1` varchar(30) default NULL, etc... From the ...