Structured Query Language (SQL). For questions about the language, not Microsoft SQL Server (tag sql-server).

learn more… | top users | synonyms (1)

1
vote
3answers
61 views

Retrieving the top row of data

I have very limited SQL knowledge and need to report on the top most current row of data within a table. The table holds Effective Date, Information and a Renewal Date. select DATA_KEY_ID, ...
0
votes
0answers
17 views

RDS Large SQL Server Instance high cpu low connections?

We are routinely getting high CPU usage with only 12 connections on our M3.large instance. I'm not particularly skilled with AWS RDS, please forgive any ignorance or obvious mistakes. But I don't ...
-1
votes
1answer
75 views

Group when there are multiple items by a particular user within a certain timeframe [closed]

SELECT * FROM ( SELECT articles.id, articles.author_id, users.username, COUNT(articles.id) AS number_of_articles, GROUP_CONCAT(DISTINCT articles.id) AS articles_published FROM articles ...
-2
votes
1answer
44 views

Extract ID String - PostgreSQL 9.2 [closed]

I've got this directory path: /userfiles/account/5554/attachment/26687981/file/2790000/main.with_name/photo.jpg /userfiles/account/(accountid)/(filecontext)/(ownerid)/(variation)/(filename) -- ...
1
vote
1answer
50 views

Combining two queries

I am trying to write code for this query: Find the names of the actors that played at least in one movie since 2000 and where not directors of any movie at this period of time. My thought was to ...
-2
votes
0answers
42 views

Data file NDF Vs MDF and performance overhead

We have database with multiple data files (*.ndf) and one of the data files we made to have static data tables i.e. not a transnational data (LibraryPrimary.ndf). Here is the load test result with ...
2
votes
1answer
54 views

SQL Query for Internal Messaging System

I'm creating an internal messaging system (No SMTP/POP3) and I'm looking for a way to query a user's list of messages. Here's my schema: Messages _MessageID (PK) _MessageParentID (if a reply this ...
3
votes
2answers
48 views

Unable to create stored procedure to access excel files in SQL Server?

I need to create a stored procedure to access Excel files in SQL Server. I have created the below stored procedure. Since I am a sysadmin, the stored procedure is working fine for me. But for the ...
4
votes
1answer
86 views

ORDER BY and comparison of mixed strings of letters and numbers

We need to do some reporting on values that are usually mixed strings of numbers and letters that need to be sorted 'naturally'. Things like, e.g. "P7B18" or "P12B3". @The strings will mostly be ...
1
vote
1answer
48 views

INNER JOIN for 3 tables [duplicate]

I am doing a small course in SQL during my academic degree, I am very new with this material. I saw Inner joinning 3 tables on StackOverflow, I tried to do INNER JOIN between my three tables, the ...
0
votes
1answer
31 views

SQL Server Alerts (notify operator when job failed)

I setup database mail, created an operator today and enable notify operator when job failed. Is that all or do I need to create an alert too? My boss just needs emails if any job failed. Also how ...
0
votes
1answer
40 views

Left Join Not returning rows when date is in range of longer than 1 day

How do I left join Table A and B? SELECT dev_hour.hour,rtdogs.delivery_date,rtdogs.delivery_hour,rtdogs.total_offer FROM ( SELECT hour FROM ( SELECT * FROM delivery_hour ) xx ...
1
vote
1answer
62 views

How to correct this oracle error?

This query SELECT praogrupos0_.ID_GRUPO_SEDE "ID1_209_", praogrupos0_.ID_EJERCICIO "ID6_209_", praogrupos0_.ID_INSTITUCION_LIQUIDADORA "ID7_209_", praogrupos0_.ID_PERIODO "ID8_209_", ...
6
votes
4answers
131 views

For SQL Server, how to fix simultaneous parallel table updates?

I have to update all records (add Guids) on two (indexed) empty columns of 150 tables, each table with around 50k records (using a script to create 40k updates at once in c# and post it to the server) ...
-1
votes
1answer
32 views

How can I automate the creation of SQL Server Agent Notifications on job failure?

There are no alerts setup on any of my SQL Server Agent jobs ( for failure,severity code etc.). Is there is a script to help or do I have to create an alert manually for every single job?
0
votes
1answer
76 views

How do I write this query?

I have a table that looks like this: CREATE TABLE `message` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(10) unsigned NOT NULL, `grp_id` int(10) unsigned NOT NULL, ...
1
vote
1answer
49 views

Displaying Distinct Max Value autonumber rows from tables in MySql [duplicate]

i am trying to execute the distinct records from mysql table having max value of it sno column. I have tried the following query but having no luck. SELECT DISTINCT p.sno AS sno, ...
1
vote
5answers
145 views

Should I have a column for every data type or 1 column that stores everything

I have a database table that currently looks like this: PageField -Id (Int, PK) -FieldType (String) Can be Text, Decimal, Integer or Bit -Value (This stores the value regardless of the FieldType, so ...
0
votes
0answers
26 views

Sqlite memory management [migrated]

I am using sqlite browser to run some queries. In case of queries which return huge data, sqlite consumes large amount of RAM and does not free it until the browser is closed / killed. Is there a way ...
0
votes
0answers
10 views

ERD question, problem with connecting

So I have a question involving an assignment i got. The question is I can see out of a query the total amount of days a room was occupied. However I think my Reservations and Rooms should be many to ...
0
votes
2answers
64 views

Removing last two digits

If I have 12345678, 1234567, 123456 in a table as a column and using sql I want to create a new column that will have new numbers that removed the last two digits of each number. The new column will ...
-5
votes
2answers
105 views

Query that finds the names of the directors that played in movies that they directed

Given database of the website "eMovies" that manage data about cinema movies. Write in SQL query that finds the names of the directors that played in movies that they directed The tables: ...
-2
votes
0answers
37 views

Query that finds the names of the directors that played in movies that they directed [duplicate]

Given database of the website "eMovies" that manage data about cinema movies. Write in SQL query that finds the names of the directors that played in movies that they directed My attempt: ...
-2
votes
0answers
40 views

Query that finds most common genre [duplicate]

Given database of the website "eMovies" that manage data about cinema movies. Write in SQL query that finds most common genre (with the biggest number of movies) The tables: My attempt:: ...
5
votes
2answers
341 views

CREATE as SELECT with CONSTRAINT?

I have a piece of SQL that creates a TABLE form a SELECT statement in PostgreSQL ; I want to add constraints to the table so that for example a column cannot be null. I cannot find valid SQL for this ...
1
vote
1answer
55 views

Making data fetching faster on VIEW

I have created a rest service which fetches data from Oracle database and send it back to the application from which the rest service is called . Below is the query to fetch data from VIEW ,this ...
-2
votes
1answer
48 views

How to solve the below problem [closed]

SELECT TOP 1 @MUTUAL_FUND_FAMILY_MASTER_ID = T.MUTUAL_FUND_FAMILY_MASTER_ID FROM (SELECT DISTINCT DENSE_RANK() OVER (ORDER BY MFFM.MUTUAL_FUND_FAMILY_MASTER_ID) AS RN1, ...
1
vote
0answers
57 views

Steps or suggestion to break up a 2,000 line sql query into many smaller pieces [closed]

Query was managed by the business team, had been running and appending to it for at least 5 years, nobody quite know exactly what it does, and it takes 15 seconds to finish. Working on project that ...
1
vote
1answer
432 views

Is introducing a where condition into the join clause bad practice

In the query below, the condition and r.master_seq=206 is used on the on clause as a join condition. In the programme code, the value for r.master_seq is set by a parameter. My concern is ...
0
votes
2answers
91 views

Trigger to compare inserted value with value from another table

I have two tables: Stockitem (serialno, qty) serialno is the primary key and Order(serialno,orderno,qty) orderno and serialno are the primary keys. I want to create a trigger in order to ensure ...
-3
votes
3answers
128 views

Finding the most common genre for “eMovies”

Given database of the website "eMovies" that manage data about cinema movies. Write in SQL query the most common genre (that have the biggest number of movies) The tables: Actors ActorID ...
0
votes
2answers
49 views

should I denormalize for user profiles?

Assuming the choice is to stay with relational model, how are tables structured in the case of large User profiles? All the examples I see consider only simple cases, such as a "profile" containing ...
1
vote
2answers
62 views

Query on bridge table

I have three tables dealing with books: Authors, Titles of books written and a bridge table TitleAuthor, from from which I am trying to find all books (Titles) with more than one author. These are the ...
0
votes
1answer
34 views

Return the most recent event in a series of same title events [closed]

I need to be able to return the most recent event of a occurrence that may or may not have occurred more than once. For instance: Each time a product is moved from position A to Position B, it is ...
2
votes
2answers
56 views

Multi level hierarchy relationship

My table design is users (id, username..., parent_id) Data is id username parent_id ------------------------------- 1 | admin | null 2 | reseller 1 | 1 3 | client 1 | 1 4 ...
2
votes
2answers
54 views

Update multiple rows with different columns

I am trying to perform a bulk MySQL update where I know only certain columns need to be updated for each row, e.g. row A only "name" changed to "Sue", row B "name" and "address" changed to "Joe" and ...
2
votes
1answer
47 views

Selecting only columns where sum(column) > 0

I have a table like this: Date Bob Joe Will 1/1/15 1 0 1 1/2/15 0 0 1 1/3/15 0 0 0 1/4/15 1 1 1 Preferred solution With ...
1
vote
1answer
35 views

'Logical file name “MyDB” is already in use' Even though the database doesn't exist [closed]

I recently was given a SAN with a couple of RAID arrays to apply to my database server to increase performance. I have (and will only ever have) one user database called DDAS, in addition to the ...
0
votes
1answer
66 views

how can we merge sum and count? [closed]

My result of the request of group by is : ref state quantity total PO3253 assinged 300.0 323400 PO3253 done 300.0 323400 When I delete state from ...
0
votes
0answers
15 views

How to calculate percentage of multiple values of columns in PostgreSQL?

I've the table named reviews which shows the hotels' services ratings given by guests who stayed at respective hotel as shown below dtime prop clean staff location amenety travel_type ...
0
votes
0answers
20 views

Select ID put into a new Table - PostgreSQL 9.2

I need to select all the File ID's on a table and put the results in the new table I've created. How can I do that? Please, have a look: 1: Select all the file ID's of gorfs.inodes/segments for ...
0
votes
2answers
22 views

MySQL Workbench - store a query to apply to various schemas

We have multiple identical schemas. How can I store a query and easily apply it to any of multiple schemas? Bonus points for a drag & drop or point & click method of executing the query. My ...
0
votes
0answers
24 views

How can i make month columns in MySQL? [duplicate]

I want to return table like that(column for each month) : Jan-Feb -Mar -Apr-May-Jun-Jul-Aug-Sep-Oct-Nov-Dec My query : SELECT CASE WHEN month(date) = 1 then 'January' WHEN ...
4
votes
3answers
192 views

SQL OrderBy When using an In() query

Example query SELECT * FROM Table WHERE ID in (5,3,4) How do I adjust this query so that the order of the returned entries follows the input of the ID's (IE, 5 first, 3 second, 4 third). Edit - To ...
0
votes
2answers
44 views

How to transpose rows to column in datatabse [duplicate]

I have one table which contain 3 column,Col_ID, Col_Group, Col_Price for this my column is like below Col_ID Col_Group Col_Price 1 AA 0 1 BB 2000 1 ...
1
vote
1answer
70 views

Error at line 1, column 25: ORA-00923: FROM keyword not found where expected

I have some code that works in Access but now I want to forward the code to our IT department to make a view in our SQL database. Could anyone please help me convert it so it is correct for normal ...
1
vote
1answer
36 views

Help with adding a time filter to Postgres query

I've asked this question over in SE GIS, but told it would be better suited to this SE, so here goes... I know Postgres is brilliant for grouping points based on their spatial values - that I can do ...
-6
votes
1answer
37 views

Select Max in stored procedure [closed]

WHY does the following stored procedure not execute correctly? declare @shID smallint, @osID smallint; set @shID = 15; set @osID = 4; IF (SELECT Max(oofficeid) AS Expr1 FROM ooffice ...
3
votes
2answers
43 views

Enabling SQL Instant File Initialization when Restoring to a Network Path

We have a large Read Only SQL DB (SQL 2012) that we’re restoring to a NAS as a current duct tape solution. I had wondered why the restore process seemed to be hanging. Part of my guidance along the ...
1
vote
1answer
38 views

If I use varaibles in the order by clause, is there any guarantee the result will be sorted?

I have a table like this: // Table1 +----+--------------------+------+------------------+---------+-----------+ | Id | QuestionOrAnswer | Type | AcceptedAnswerId | related | timestamp | ...