Tagged Questions

An acronym for "Structured Query Language" (it's not the name of a specific DBMS product), SQL can be used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. It can also be used to manage schemas and data ...

learn more… | top users | synonyms (1)

0
votes
3answers
44 views

how to order a field based on two parts of the row value

I have a field with contents of the form 22/4, 30/8, 30/5, 30/3 I want it ordered in the form 22/4, 30/3, 30/5, 30/8 with the part after / ordered if the part before / is same! Please help!!
0
votes
2answers
67 views

Is there anyway to search PL/SQL reserved words with contains function?

Is there anyway to search PL/SQL reserved words with the contains function in the Oracle? We would like to search keywords such as "test for" or "test do" with the contains or catsearch functions ...
5
votes
2answers
216 views

Strange query results for DELETE with ROW_NUMBER

In the following DELETE statement I try to delete all rows except the first one ordered by some criteria. (The actual query makes more sense, this is just a repro. All the sys.objects stuff is just ...
-2
votes
1answer
41 views

Subqueries in SQL Server 2014 [closed]

I want to get results that is in more than one row using a subquery. here is what: Note. This is in microsoft SQL Server 2014. Relations: FeeStudent(Feeid, Studentid, MODE,LEVEL, SEM, Paid, Status) ...
2
votes
1answer
33 views

Best way of finding rows referencing a given id on PostgreSQL

I'm trying to find if there would be anything violating the foreign key constraint when deleting a row, to check if I can delete it or not (can't use ON DELETE CASCADE) For that, I have a piece of ...
1
vote
2answers
72 views

Fourth inner join to the same table increases query duration significantly

As soon as I add a fourth inner join to a specific table, the query duration increases significantly: sub 1sec to 45sec. The table in question is deployments d4. The SQL Query: select a.id as ...
1
vote
3answers
56 views

Performance first low, then high after adding index, then keep high after drop index

I am testing performance a complicated select query. It is very slow. using explain, I have found a ALL in type COLUMN. Then I ADD INDEX. result comes out fast. However, when I DROP the INDEX I ...
2
votes
2answers
60 views

Oracle: Helping hand writing this query. Do I need a sub query here?

I have the below query that returns the number of orders created on a particular customer number per month for the past 2 years. SQL SELECT TO_CHAR(o.entry_date, 'MM'), SUM(CASE ...
1
vote
1answer
29 views

group records according to column

hi how can make this result query: to this: I have this code and i know it's still farfetched: select max(case subcod when 'DISABILITY' then A end) as disability, max(case ...
2
votes
2answers
217 views

Join 3 tables and return only distinct values for max datetime

I've been trying to do this the whole 2 weeks but seems like to no avail. 3 tables involved here. but am expecting the result would be distinct case no. for the same date & user. otherwise, it ...
0
votes
1answer
58 views

SQL Server 2008 and constant expressions

If I have the following scenario, would Replace be evaluated a single time? DECLARE @text nvarchar(32) = 'This is a test field' SELECT textField FROM Table WHERE Id = @idICareAbout AND textField = ...
0
votes
0answers
26 views

Receiving duplicate results in one condition of JOINED query

I always end up here when Googling for solutions, so I thought I'd cut out the middleman since I'm unable to fix this via searching/forums. It's my first time posting, so please forgive any faux pas ...
2
votes
0answers
74 views

SSRS URL Changes while navigating

I am trying to track down the root cause of this issue I keep seeing. We have SSRS running with a custom URL like https://reporting.companyname.com/reports. When I connect to that address I can ...
3
votes
1answer
107 views

Need help with a Running Total Issue

Using SQL-Server 2008 R2 and Report Builder 3.0 Here is my query without the running total column: SELECT CostManagement_CommitmentDetails.Id ,CostManagement_CommitmentDetails.LineNumber AS ...
1
vote
1answer
56 views

2 Date Ranges - How many days from one range excluding weekends overlap

First - Thanks for your help. I'm looking to create a query in SQL that provides the me the # of days from one date range that overlap another date range and excludes weekends. Example Data: ...
0
votes
1answer
37 views

MySQL: Rank by Department in an employees table

I need Mysql query to rank the hours used by each department in an employees table, but it just ranks all without taking into consideration the departments.
1
vote
1answer
38 views

How to query a list of all the different errors?

I have a DB which is very big and stores a lot of eventlogs, around 15 million rows. When I try this query, it is totally impossible to get some results. Any idea how I could do this? Maybe indexed ...
1
vote
3answers
32 views

Study material for Advanced Databases module inc SQL, Relational Model, ERM & TM [closed]

For my next module in my masters, I am planning on studying Advanced Databases. (Long story short, the masters is designed for people with NON-CS degrees, so I have never studied databases, therefore ...
-2
votes
1answer
35 views

Oracle Concurrent Insertion

We have simple case like Create table t1 (c1 number primary); session1: insert into t1 values (1); //session1 do not commit transaction session2: insert into t1 values (2); Will Session2 ...
2
votes
2answers
43 views

SQL Insert Statement Persists Two Records When I Need Just One

I have altered it to include hardcoded variables when I pulled this chunk of code from the stored procedure. I ran it independent of the application with the same result. I need one insert statement ...
0
votes
1answer
26 views

SQL Insert Statement Persists Two Records When I Need Just One [duplicate]

I have altered it to include hardcoded variables when I pulled this chunk of code from the stored procedure. I ran it independent of the application with the same result. I need one insert statement ...
2
votes
2answers
62 views

Using collate: When to use and when not to use

In one of my applications we had tsql like shown below SELECT DISTINCT [Dblist].[DbName] AS [DbName] FROM [Common].dbo.[Dblist] WHERE dbname not in ( SELECT [name] FROM ...
2
votes
0answers
22 views

Best way to get time zone key name from SQL Server

Below is what I pieced together but I wanted to see what other ways are available. SET NOCOUNT ON; GO DECLARE @tz VARCHAR(50) EXEC [master].[dbo].[xp_regread] 'HKEY_LOCAL_MACHINE' ...
0
votes
1answer
38 views

to pivot or not to pivot - schema design question [closed]

I am implementing something similar to this schema http://www.databaseanswers.org/data_models/event_reservations/index.htm It occurs to me that the pivot table for Customer_Bookings would just keep ...
2
votes
3answers
305 views

Run multiple stored procedures in sequence

I have a stored procedure written in T-SQL (SQL Server 2008 R2). It is a master procedure that essentially call multiple other subprocedures in sequence. The call and error handling is identical for ...
0
votes
1answer
61 views

Merging tables, but ignoring duplicate columns

I have two tables I want to merge: city1(id, city, state, zip, lat, lon, county) city2(id, city, state) The tables contain overlapping city and state, but not primary keys. I can't figure out how ...
0
votes
3answers
41 views

Converting huge ASCII file to flat and convert

I am not a db administrator but I thought to ask this question here. I have several very large ASCII files (3-5GB each) that I need to extract the data and then sort it. The instructions say to ...
0
votes
4answers
65 views

SQL Server combine Update and Delete

Is it possible to combine both update and delete in a single SQL statement? I would like to execute something like this: update a set a.value = a.value + b.value from Table1 a join Table2 b on a.ID = ...
1
vote
2answers
56 views

MS SQL Server 2012 Log Shipping Questions

I have a few questions regarding Log Shipping in SQL Server 2012. 1) If the primary database server dies, is it possible to allow users to connect to the secondary database so as to continue working ...
1
vote
0answers
16 views

Is there a faster way to get UNION ALL behavior in Postgres? [duplicate]

I have a table (t0), in my Postgres DB, with data that looks something like this: t1_id t2_id 1 1 2 1 2 1 4 null 4 null 5 null And I have a query to return my ...
1
vote
2answers
45 views

Multidimensional aggregates

Suppose one wanted to cache simple aggregate data over a table or grouped parts of a table, one could simply make a table with the aggregated results. If the original table is only appended to, it is ...
0
votes
2answers
26 views

Create a test enviroment in Oracle 10g

I have a production enviroment in a Oracle 10g database which I would like to authomatically duplicate in a test enviroment. My production enviroment is just a number of tables in which I have usually ...
0
votes
1answer
38 views

Query output conditional upon values in dataset

If the number column contains values 5 AND 6, AND the value X exists in the chr column, I would like to exclude rows with 10 in the test column. But, if the number column contains values 4 AND 5, AND ...
2
votes
1answer
76 views

PostgreSQL return second 'Group' as an array

I have some tables that include websites and visitors and I am trying to write a query that produces a 'top 10' for each site, the tables look something like this... websites ------- website_id (pk) ...
5
votes
2answers
74 views

Is the runtime of checking for duplicate primary keys in SQL Server O(n)?

I was curious and I couldn't find too much info on this. Is it also O(n) for checking for duplicates among primary keys? Is it the same for most other SQL databases? Is there a place that lists this ...
5
votes
1answer
88 views

SQL Server stuck in single_user with auto_update_statistics_async on [closed]

Books online states Before you set the database to SINGLE_USER, verify the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When set to ON, the background thread used to update statistics ...
1
vote
1answer
42 views

Linking first and second level composite primary keys

I am trying to create a new database with a hierarchical structure of two many-to-many tables using composite keys. Unfortunately the creation of tables fails stating that the foreign keys do not ...
0
votes
1answer
21 views

Retrieve index column

I have this index schema: CREATE index comments_idx ON comments (books) How can I retrieve column name ("books") through SQL query?
1
vote
2answers
49 views

Get player count per match on a matchmaking server [closed]

This is my current structure of my matches table: id, title, description, max_players, host_username, host_ip and there's another table called matches_players: match_id, player_username ...
0
votes
1answer
35 views

How to order by Levenshtein distance?

I want to order a table according to a char field using Levenshtein distance or similar because some entries may contain underscores or grammar errors. It is a table with all the books I plan to read ...
0
votes
1answer
34 views

Complex query on big table

I have a big user table (3M), of structure: age (int) country (FK) city (text) gender (text) active (bool) My objective is to run a search finding 15 users of given age area, gender, city and ...
0
votes
1answer
22 views

SQL - Add column if match else, on aggregate data

I have an order table that roughly looks like this |Customer id |Date | revGBP |orderID |GiftOrder | --------------------------------------------------------------------- |1 ...
2
votes
1answer
132 views

Can you put a SQL DB on a NAS? [closed]

Can you put a SQL DB on a NAS drive? For example can I put the PostgreSQL DB "files" on a Sinology NAS or am I better to leave everything on a "real" PC? (like, say, a regular PC server running Linux) ...
1
vote
1answer
126 views

What's the term that describes filtering query that makes use of indexes

I am looking for a technical term that describes the type of query in SQL that makes use of indexes. I completely forgot the name and can not look it up anymore (there's even a Wikipedia article about ...
0
votes
2answers
31 views

How to use relationships to extract data via select

Disclaimer: Novice in SQL I have 3 tables. There is one "major" table (labeled used_for_lookup below) used to do look-ups on and the other two (labeled fid_table and did_table below) are foreign keys ...
1
vote
1answer
38 views

Question about the workflow/design process/tools used by professional database designers

I hope this in the right place, as it's really more a question about database design software than the philosophy and practise of database administration. I'm a masters student doing an "introduction ...
1
vote
1answer
25 views

SQL : get rows from two tables relatively

I've two tables named categories and products. Simple structure is given below. categories cat_id cat_name 1 c1 2 c2 3 c3 products prod_id ...
2
votes
1answer
73 views

SELECT query with DISTINCT on a table-structure for graphs is very slow

I have 2 tables where nodes contains about 60m rows and edges about 500m rows (both growing fast). These two tables represent a directed graph. CREATE TABLE edges ( node_from bigint NOT NULL, ...
3
votes
1answer
49 views

How to join by most recent preceding date?

Imagine I have a database with historic price data and a second table with dates Item | Price | Date Customer | Item | date ------------------------- --------------------------- ...
1
vote
3answers
92 views

How to run sql agent jobs in a sequence order.with T-sql

My requirement is to run the SQL Agent jobs (EX: job1 to job10) with T-sql in a sequence order. That means job2 starts automatically when job1 completes. This process should continue until job10 ...