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

learn more… | top users | synonyms (1)

5
votes
2answers
66 views

Same record shows up multiple times in ordered paginated query

Some records show up on multiple result "pages" with the following query: SELECT `description`, `wallpaper` FROM `bbr_bar` WHERE `country_id` = '2510769' ORDER BY `online` DESC ...
1
vote
1answer
59 views

How can i improve my count(distinct id) query in mysql laravel 4.2

I am using three join and fetching more than 40lacs of records for now it is 15 lacs . Below is my query, can you please take a look? select count(distinct enrollments.userid) as aggregate from ...
0
votes
0answers
66 views

Find all possible pairwise 2 rows such that there intersection is only one element I have also a general question help on the same

Kind help to find the intersection of all pairs of rows of table and display only those pairs whose intersection is only at one element. Here I have a table say with 4 varchar elements for a sample ...
0
votes
2answers
32 views

Avoid user login Directly to AlwaysOn High Availability Primary

I have setup an AlwaysOn High Availability in Sql Server 2014. my IP addresses are according to this : 192.168.1.189 -- Listener 192.168.1.180 -- Replica 192.168.1.181 -- Primary Right now users ...
1
vote
1answer
33 views

Strange upper() behaviour for dateranges in PostgreSQL

I'm running PostgreSQL 9.4.5 and I've just got an unexpected result from this query: select upper(('[2005-12-01,2005-12-04]')::daterange) It returns 2005-12-05 instead of 2005-12-04. The ...
6
votes
2answers
671 views

Is ELSE 0 implied in my COUNT CASE WHEN statement?

What's the difference between COUNT(CASE WHEN [Column A] = ____ THEN 1 END and COUNT(CASE WHEN [Column A] = ____ THEN 1 ELSE 0 END? I've been using the former and I haven't seen the difference thus ...
3
votes
1answer
22 views

SQL Server Changing logical name

I have some db's created with an incorrect logical name for both rows and log. how can I change the logical names to match the db name with a script? (and does not want to change physical file names) ...
0
votes
0answers
28 views

Updating a table which contains an Image datatype column

I have a table which contains a few columns, one of which is an image datatype, this is used to store BLOB data, which could be anything from a jpg to a large PDF. I have a requirement to update one ...
0
votes
1answer
48 views

SQL Dependent Percentage

Sample data Excepted output Math Operation Sum all Period time for each EmpName as Total Employee Time Sum Similar Tasks For EmpName, divide it by Total Employee Time and multiply by 100% ...
4
votes
1answer
31 views

Update foreign key from csv file

I am trying to set up a simple database for the first time using MySQL Workbench. I'd like to update the foreign keys of the child table based on the values of parent_child.csv. Here are the current ...
1
vote
0answers
36 views

SQL Server licensing in a virtualised environment [closed]

My company has 6 instances of SQL Server 2012 Standard. These instances all run on VMware virtual machines. The licenses for SQL Server came packaged with WonderWare products (WonderWare Historian for ...
2
votes
1answer
31 views

Unresolvable Ambiguity problem on Join Tables

I have searched for all answers, but nothing works. Somehow this query gives me the ambiguity error or 'ID': SELECT wpp.ID AS theid, s.ID AS supplierid, post_title, wp_terms.name AS category FROM ...
1
vote
3answers
85 views

why in explain in mysql row count is very high

i am using mysql, here is query- mysql> select count(*) from payments as Payment WHERE `Payment`.`fordate` BETWEEN '2015-11-28 00:00:00' AND '2015-12-31 23:59:59'; +----------+ | count(*) | ...
3
votes
1answer
87 views

SQL Server: How to add all dates of the month?

I need help. I am trying to add all dates for the month. I have this information: Month | Year ============= 1 | 2012 2 | 2012 3 | 2012 4 | 2012 I need to add all dates of the month ...
2
votes
1answer
47 views

Adding foreign key that references a column which is not Primary or Unique key

I got into trouble when I want to limit values of Table A's column to be taken only from a set of optional values in Table B's relevant column. Problem is table B's relevant column isn't a foreign ...
1
vote
0answers
69 views

Improve lookup of 1.5 million rows

I have this table with data but we need to get all the (unique) rdx and rdy values: CREATE TABLE public.positions ( datetime timestamp without time zone NOT NULL, unitid bigint NOT NULL, rdx ...
0
votes
1answer
68 views

MySQLSyntaxErrorException: You have an error in your SQL syntax;

This is the issue I am trying to fix since hours, almost a day. I am unable to get an understanding of what might be wrong. I have a SQL table notification I am trying to persist a java object ...
-8
votes
1answer
74 views

FULL OUTER JOIN much faster than UNION ALL in SSMS, but slower in Excel [closed]

I am trying to optimise a view pulled from SQL Server 2008 to Excel, and I've managed to drastically improve the performance on SSMS. Using FULL OUTER JOIN instead of UNION ALL, I've managed to ...
1
vote
2answers
45 views

Sql tables design

I have a table with two columns: Brand(varchar) | optional_os_type(varchar) for example: +--------------+ | Lenovo | 1 | | Dell | 2 | +--------------+ Whereas 1 = win8.1 2 = win10 Currently ...
1
vote
3answers
68 views

How to create a non-sa sql server login / access without control server permission

I have an ms sql server, and I need to give access to other users to run basic queries (just select on 1 database). I have created a login for a user, and have amended their permissions, but once I ...
4
votes
1answer
113 views

Hash Join vs Hash Semi Join

PostgreSQL 9.2 I'm trying to understand the difference between Hash Semi Join and just Hash Join. Here're two queries: I EXPLAIN ANALYZE SELECT * FROM orders WHERE customerid IN (SELECT customerid ...
1
vote
2answers
104 views

Are these database indexes correct for my search

I am new to Database Administration. I am following information provided by: http://www.brentozar.com/blitzindex/ I have a table VehicleMonitoringLog. When I execute the sp_BlitzIndex script it shows ...
1
vote
0answers
59 views

SQL query causes server gone away

I'm developing a database system which stores and caluclates engine performance data. I have been created a new view which selects data from several tables and calculates some values. Now I'm running ...
0
votes
1answer
55 views

Selecting Only One Record with Most Recent Date Using Table Join

I have a list of 463 CARRIER_CD. I am attempting to retrieve the most current BILL_DT for each of the 463 CARRIER_CD. I have uploaded the 463 unique CARRIER_CD to a database named edw_sbx_bt the ...
0
votes
1answer
40 views

Update row contents on some condition

For example, let's say I'm building a database for an auction system, in which various users can partake in both selling and bidding on different items, I'd want for an "Auction" instance to also ...
1
vote
1answer
47 views

Oracle: using “as of” clause with table aliases?

I can run this flashback query with no problem: select x from a as of timestamp sysdate; But if I use a table alias I get an error. select foo.x from a foo as of timestamp sysdate; ORA-00933: SQL ...
0
votes
0answers
30 views

Display Complete Month With or Without TIMESTAMP field

Please edit the title if you have a better, I didn't find other words to describe this. Environment Fields: |id|time|text| Situation I have to make a report with the total of records per day, ...
1
vote
1answer
39 views

Restoring an Oracle database from files

I am running into a problem now, i have an oracle database that is stored inside a server and now because of the sudden blackout the c drive is messed up. The drive is partitioned, windows on c and ...
2
votes
1answer
61 views

Get max url from PostgreSQL statement

I have a very complex query which generates a view in PostgreSQL. Now the query returns duplicated rows, well almost duplicated: one field, the "url" column is different. Now we still want to group ...
0
votes
3answers
72 views

How to update one column with the value of another if one of their columns match. SQL

I've found a lot of similar questions, but not exactly what I need.I want to make a SQL command to find all duplicate email rows and update the notes of them with 'do not email' if it is written in ...
2
votes
1answer
94 views

SSIS package fails intermittently with: Unsafe assembly 'microsoft.sqlserver.integrationservices.server, version=11.0.0.0

I have an SSIS package deployed to the SSIS catalog on my Data warehouse which has run successfully for months. The package is executed by a SQL agent job. Now it fails intermittently with the ...
1
vote
1answer
66 views

How to get max value of union query?

My database schema is as follows Department (dept_id, dept_name) Student (student_id, student_name, major, level, age) Professor (prof_id, prof_name, dept_id) Class (name, meets_at, ...
0
votes
0answers
55 views

Could not open file control bank (FCB) for invalid file id 0 in database [closed]

Has anyone come across this error before? I have recently encountered this error when running a query on one of our databases. I ran a checkdb and found no errors in the database itself. The db ...
1
vote
1answer
25 views

Help writing a query that select the min column in a group by

I have two simple table CREATE TABLE user ( id INT NOT NULL AUTO_INCREMENT, nickname varchar(35) NOT NULL, PRIMARY KEY (id), ); CREATE TABLE user_service ( id INT NOT NULL AUTO_INCREMENT, ...
1
vote
1answer
37 views

Limit number of user logins in SQL Server

I have a user login named 'manager' currently logged in PC1, I want to prevent this user from login in PC2. So I created a trigger, and I got an error Msg 4621, Level 16, State 10, Line 2 ...
0
votes
1answer
41 views

ODBC connection fail when running AD account for SQL Server services

I am following up a question from Remote backup to folder path in domain I asked earlier. I could not get my proxy SQL Server Agent user to work to back up to a remote location and have given up for ...
1
vote
1answer
42 views

Jetbrains 0xDBE : How can I load a SQL Server .bak file?

I'm trying this new Jetbrains IDE and I can't figure out how to load a SQL Server 2008 R2 .bak file. Maybe I'm looking in the wrong menus. Is it possible?
2
votes
1answer
81 views

How to refactor this subquery to be written only once?

procedure foo(p1 IN INTEGER, p2 IN INTEGER, ooo out sys_refcursor) is begin open ooo for select T1.* from T1 where T1.x=p1 and ( p2 in (SELECT z from T2 where ...
1
vote
1answer
34 views

Multiple inserts across tables with serial primary keys

I asked this question on stack overflow, but I'm thinking it's better on this stack. Apologies if I've got that wrong. The motivation for this question is to prepare some sql scripts I can run during ...
3
votes
3answers
101 views

Having both a integer Primary Key and a GUID/UUID collumn on same table-is this bad

I have my MySQL database having tables where on each table, we have a ID (int,auto increment) Clustered Primary key and a UUID(varchar36) collumn thats just a GUID. The ID is there just for indexing ...
4
votes
1answer
85 views

Getting Unavailable dates for renting a product that has stocks

Database queries, normally so simple, yet sometimes so difficult. (brain trainer) So I have products, stocks and rentStockOrders. These products can be rented for a set of days. The stocks also have ...
1
vote
1answer
34 views

Cursor with Begin and End Transaction

I've got the following script that keeps hanging on the 2nd iteration through the loop. BEGIN BEGIN TRY BEGIN TRANSACTION DECLARE @itemID int DECLARE LoopCursor CURSOR FOR ...
1
vote
2answers
70 views

Join on multiple columns with OR

I have a table of 1 million record to join another table with 100000 records. However, there are 5 potential keys (lets assume Account Number, email address, membership number, alternative email, and ...
0
votes
1answer
24 views

Modify table constraint in PostgreSQL

Say I have a constraint ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5); that needs to be modified so that zipcode length is 6. Do I have the drop the constraint ...
0
votes
2answers
46 views

More Efficient Use of Union

Looking for a more efficient way to achieve what my view is doing. This view joins tables to themselves to show where each part in the database is used. In other words if I am a part of a part list ...
0
votes
1answer
30 views

Catching Errors in procedure

I have created procedure for orders table. The procedure checks if the order has shipped, then the customer can returns all/part of the shipped items. Once verified by the procedure and the order has ...
1
vote
3answers
196 views

Replication taking too much time between requests

We are using transactional replication in SQL Server 2014 with one master (publisher), 1 distributor (dedicated server), and 3 slaves (subscribers). All writes are made to master and reading is done ...
1
vote
1answer
67 views

what is the formal statement terminator with db2 sql

I have added / character as statement terminator all in my db2 statements. I can run sql file in data studio client without any issue. But when I run the sql file in db2 command prompt by issuing db2 ...
1
vote
2answers
64 views

would it be possible in postgres to get a count of sql hits on a select statement

Say I have the following table in Postgres 9.4: items id | name | tag_ids 1 tire -1--2- 2 wheel -1--3- 3 transmisson -3- I'd ...
0
votes
0answers
32 views

How do I design a database for a registration system?

The concept is something like this: Organiser creates event. User registers for events. Events can be for multiple days. One event can have different competitions. User can enter into any ...