4
votes
2answers
76 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 ...
0
votes
1answer
39 views

Low-level relational operators from SQL

I have been asked to outline the sequence of commands using low-level operators (join/project/select) that are used in the following SQL statement; SELECT d.name, k.city FROM DOG d, KENNEL k WHERE ...
1
vote
0answers
31 views

SELECT, PROJECT, CARTESIAN PRODUCT and JOIN

I have the following tables; DOG +----------------------------------------------+ | Name Type Age Kennel_name Owner_name | +----------------------------------------------+ ...
0
votes
1answer
42 views

Name for SELECT equivalent of UPSERT

UPSERT means INSERT and\or UPDATE, is there a standard name for a sproc that will INSERT if necessary and then SELECT? e.g. I want to get the IS of a row that matches fields and give me the ID, if ...
0
votes
3answers
34 views

How to create an Access query to find # symbol into a field?

It seems easy but I'm missing something. I have a MySQL database that is connected from access through ODBC connector. I'm looking for the fields where there are # symbol but I'm getting zero ...
1
vote
2answers
43 views

MySQL: Is it possible to order a query by a specific letter using ORDER BY?

What I'd like to do is order my query so that the results show every row found, but are ordered by showing results that begin with the letter 'D' first. Is this something that is possible to do with ...
-2
votes
1answer
58 views

How to prevent duplicated records when applying ORDER BY NEWID() to fetch them randomly? [closed]

I tried using the solution provided in http://stackoverflow.com/questions/1117761/sql-query-to-return-rows-in-random-order to fetch random records in my query. But I have to add NEWID() to the list of ...
0
votes
2answers
62 views

Select * on inner join with common columns

I have 3 tables (mysql db) on which I want to do an inner join. The problem is that these tables have common columns. I know I could use aliases to differentiate them (select A.x as Ax, B.x as Bx, C.c ...
2
votes
1answer
89 views

How can I use running total aggregates in a query to output financial accumulations?

I am attempting to write a query that will be consumed by a SSRS 2012 report. Given a paid invoice, with a certain set of account allocations towards which the money paid towards it must be saved, I ...
1
vote
0answers
28 views

How to join the tables in mysql to get wall messages and comments with name of person ? Only user and his friends messages

I have four tables in my mysql database as shown in the image url provided. I want to select data from the tables without duplication. I want to show messages and comments on wall according to the ...
0
votes
3answers
104 views

How to query for 'First showed up as'

Given the following table: Date Trd (INT(10)) Code (VARCHAR(10)) T-Date 1-Jan-14 1 B 1-Nov-13 1-Jan-14 2 B 3-Oct-13 ...
0
votes
1answer
35 views

Order result by number of occurence of a word in a text

I am working with sqlite3/python . I would like to order results by the number of occurence of %@% in text. For exemple Jimmy 10 // Jimmy wrote 10 strings like %@% James 2 Sophia 0 ..etc The ...
1
vote
1answer
35 views

How to calculate average of list of qualifications of each pupil MYSQL

I have this database and query: http://sqlfiddle.com/#!2/b4cf72/116 And I would like to add a column with the average of the exam qualifications. However, I try doing this but it tells me there are ...
1
vote
1answer
121 views

How do I query for leaf nodes in a hierarchy using a closure table?

I have some hierarchical data (representing musical instruments and playing techniques), where each leaf in the hierarchy is an audio file. The nesting depth of leaf nodes will vary because some ...
0
votes
1answer
42 views

How to remove duplicates in col1 and get group MAX in col2? / mysql

Given the following table: col1 Col2 A 1 B 1 C 1 B 2 B 3 A 0 C 5 how do I write a SELECT query (I just assumed has to be SELECT) that returns the unique items of col1 and the ...
1
vote
2answers
103 views

Select query to retrieve values even if are null

I need some help in a query. Need to retrieve data from 1 table, even if one of the field has null value. Here's an example: Select name, SUM(credit) as credit From expenses Where name like ...
-2
votes
1answer
63 views

how to count result set for select statment [closed]

I have to make query from DB. I found some solution but it isn't so precise as it need to be. Here result of my query: country weight Germany 1687.5 Gt.Britain 1687.5 Japan ...
0
votes
0answers
177 views

SQLite UPDATE two tables from VIEW or TEMPORARY TABLE

Here is the query that doesn't compile just to show how it should look like. But since I can't do Multiple table UPDATE's. This fake query should be able to run the inner UPDATE's for both tables if ...
2
votes
3answers
187 views

How to query efficiently from Postgres to select special words?

Let's say that I have a table called words with very many records. Columns are id and name. In the words table I have for example: 'systematic', 'سلام','gear','synthesis','mysterious', etc. NB: ...
1
vote
1answer
133 views

Select next and previous rows

I have the following table: CREATE TABLE post ( id bigint primary key, thread_id bigint, is_notice boolean, title text, content text ) I display the list ...
2
votes
1answer
702 views

MySQL query to evaluate ranges in date and time elements of a datetime column separately

TABLE EventID, EventTime (DATETIME) 1 2013-09-29 23:55:00.0 2 2013-10-01 00:05:00.0 3 2013-09-29 23:55:00.0 4 2013-09-29 23:45:00.0 5 2013-10-02 23:05:00.0 6 ...
2
votes
2answers
51 views

Get count of processed queries

I have been searching a while to find a way to retrieve the amount of queries my postgres database is processing and responding to. I found various statistics about disk I/O, insert/update statistics ...
0
votes
1answer
1k views

How to select only first column dynamically on mysql

I have to select only first column. Because of i dont want to select all columns like SELECT * FROM table. But my first columns may vary like following: id article_id user_id product_id page_id etc.. ...
-1
votes
2answers
494 views

How to get missing values in row order?

In a table, many rows have been deleted, how to get the id of missing rows for next INSERT? For example id col1 1 1 3 3 4 4 5 5 8 8 9 9 how can I get the ...
0
votes
1answer
142 views

How to avoid duplicate entries with SELECT statement?

+----+----------------------+---------+---------+---------------------+-------+ | id | translation | id_word | id_user | added | tuser | ...
-2
votes
1answer
80 views

How big the database should be in order to take 30 minutes to query

We have few tables each containing 2,5 million rows in average. Then there is a select query with joins. It takes more than 30 minutes to execute. The db server runs on a machine with 9GBs of RAM and ...
0
votes
2answers
221 views

How to use OR in query with priority?

In a simple query like SELECT * FROM table WHERE id='x' OR id='y' How to give a priority to get only one of the WHERE clauses? I mean getting rows with id='x'. Only if there is no row, get row ...
3
votes
1answer
165 views

Combine - Select Into, with Insert Into Select

I've read many explainations like this one here that say "Select Into ..." is to create new tables and "Insert Into ..." is to append to existing tables. I'm automating a coworker's sql scripts. ...
2
votes
1answer
453 views

Postgres nested hstore select query

I have a carts table with items hstore column. An example entry in this column is: carts.items row#1 = { "1614" => {:quantity=>"100", :price_cents=>1655}, ...
0
votes
2answers
66 views

Difference between the given queries

I am required to retrieve the list of pno ( product_id ) for the products which were purchased by two customers. For this, I have been given two relations which are ( along with the attributes ) as ...
-3
votes
1answer
3k views

How to get MAX and MIN in GROUP BY query?

In a simple query SELECT col1, col2, col3 FROM table1 GROUP BY col1 each row includes the first value of col2 and col3 for each unique value of col. How to select MAX and MIN for each value of ...
0
votes
1answer
437 views

How to create an Sql table from SELECT with a multiple row DISTINCT constraint?

I want to create a table from a SELECT statement based on a multiple row DISTINCT, but PostgreSQL does not allow me to do it. I need to create a table where the elements were previously selected from ...
1
vote
1answer
52 views

Altering strings while selecting them

I'm working in SQL Server 2008, I have a varchar(255) column in my table, I want to select out all of the strings which contain the ':' character, and select the first part of the string before (but ...
-1
votes
1answer
255 views

'ambiguous column name' error refers to database, table, and column [closed]

sqlite3 gives an 'ambiguous column name' error with the query below. The error itself references what seems to be about as an unambiguous column name as one can imagine, identifying the database as ...
1
vote
4answers
2k 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 ...
3
votes
3answers
199 views

Is there a way, in a single SQL statement to ensure that all items in a list are present in some column?

What I'm looking for is a way of verifying that all of a list of items (let's say 'FOO', 'BAR' and 'BAZ') are all in a given table.column. This would be something like SELECT something FROM sometable ...
3
votes
1answer
267 views

Index on foreign key makes query extremely slow

We are recently experiencing a tremendous query slowdown with spilled over temp tablespace. A specific query causes this problem. The queried table (table3) has an indexed PK, three FK with indexes ...
2
votes
1answer
79 views

How to select nodes where all children is satisfied?

I have a tree structure of light bulbs. I want to turn on all the light bulbs starting from the leafs of the tree. A light bulb cannot be turned on unless all its immediate children are turned on. ...
-1
votes
2answers
584 views

How to do division from select statements in sql server?

I am trying to create a select statement that can return a table of 1 column. Basically, I am looping through all the records of NewHire, and then using its id, to select stuff from another table. I ...
-1
votes
3answers
113 views

Add Contents in a Column and make them 0 [closed]

http://sqlfiddle.com/#!3/96f11/3 In the above fiddle, I need the required output in the Fiddle. ie., The UserID column in Filtered Table and Main table are equal. I need to get the Amt column data ...
0
votes
2answers
2k views

MySQL: Join one Row in a table with two Rows in other table

In my MYSQL Database COMPANY. I have two tables, like below in my diagram (arrow shows relations): `users` `user_login` +--------------+ ...
1
vote
2answers
39 views

Order by on an alphanumerical column

I have a column that has strings of the following type: Miller 10 Allen 20 King 10 .... Jones 100 I try to sort the column based on the numerical part of the data. I tried the following: SELECT ...
-4
votes
2answers
1k views

Get Hierarchial Data in Self-Referencing Table (Parents , Childs) [closed]

In the following Fiddle, sqlfiddle I will pass a sno and I need all the parents of that sno in a table. And all the children of that sno in another table Please refer to this question. Since ...
2
votes
2answers
2k views

How to JOIN two table to get missing rows in the second table

In a simple voting system as CREATE TABLE elections ( election_id int(11) NOT NULL AUTO_INCREMENT, title varchar(255), CREATE TABLE votes ( election_id int(11), user_id int(11), FOREIGN KEYs for ...
-1
votes
2answers
516 views

Select query having count and variable

I have a table like the one below. I need to form a select query. I'm going to pass a RefID like SELECT SNo FROM Register WHERE RefID = 1 And beside the SNo in the Statement, I need to have ...
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 ...
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 ...