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

learn more… | top users | synonyms (2)

0
votes
1answer
48 views

Multiple databases in production

We have 3 level hierarchy in our business model. Entities at level 3 contains huge data (like dailyfacts tables). In our current DB architecture, we have one single database & all level 1 ...
4
votes
2answers
216 views

Optimize query to find top N users who commented on a post

Problem Trying to find the most efficient query to retrieve the top N (5 in the examples) users who have commented on a post, where a user is considered 'top' if they have the most followers. The ...
0
votes
0answers
23 views

Calculate total number of faults that occurred today?

I have created a database application in Access that can be used to view fault reports that have been logged against a computing department's inventory. So far I have created queries which enabled the ...
2
votes
1answer
68 views

Select where value in row is different and update

I have a large table that includes rows like the following: Query: select * from cartographic_text_vm where fid = 'osgb5000005104723440' order by ckpt; Results: -[ RECORD 1 ]---------------+---...
1
vote
2answers
79 views

How to get the DDL for Oracle object without extra characters

When using Oracle's dbms_metadata.get_ddl for getting DDL I see some extra characters, especially there are whitespace characters at very beginning of DDL script. For example, if I create this sample ...
10
votes
5answers
210 views

Convert a date range to an interval description

A requirement in a recent project was to report when a resource would be fully consumed. As well as the exhaustion calendar date I was asked to show the remaining time in English-like format, ...
4
votes
2answers
121 views

find the beginning and ending values of sequential runs

This article does the inverse of what I need: Find which numbers in [1, 161] are not in the result set? Given the sample data they used: mysql> CREATE TABLE wp_blogs -> ( -> ...
0
votes
1answer
26 views

Postgresql maintainenace queries fail with foreignkey violation

I maintain a Django app with a postgresql backend, where users post interesting URLs (links) and then either leave replies under these or upvote/downvote them. All this is done via 4 postgresql tables ...
0
votes
1answer
40 views

Update a valid column when another column has changed

We have a valid_phone column that just stores 1 or 0 for valid or not valid. When the user changes their phone number we want that field to go back to 0 so they will have to validate their number ...
1
vote
1answer
42 views

Display last date grouped by warehouse and product

I have a table called daily_stock with the following fields: +-----------+----------+------------+----------+ | warehouse | product | date | stock | +-----------+----------+------------+----...
4
votes
1answer
130 views

Subtracting sum of previous rows' data from current row's data in SQL

I want to create a table or view in SSMS which should has a column called qoh. In that column should be calculate sum values of the column rel_qty. i.e. The first data of qoh is the same of the ...
5
votes
4answers
224 views

Total duration “server” was in “In” state per day

I have data of a server start & stop time on a daily basis for one month. I need the result containing the first start time of the day, last stop time of the day, total time the server was started ...
0
votes
2answers
32 views

Group By Month With Overlapping years

I want my query to show all months grouped together, regardless of the year. For example, I would want this to be the output: 01/13/2015 01/01/2016 02/07/2015 02/01/2016 This is sample DDL - what ...
2
votes
1answer
94 views

SQL Server MDW - Query for data collector retention period

I have data collectors active on many remote servers and I am trying to find a query that will capture the retention periods for all data collectors active on an instance. These are system data ...
1
vote
1answer
28 views

Show Dollar Amount By Name On A Month To Month Basis

I want to show dollar amount by name on a month to month basis, but my syntax gives me an entry for each different dollar amount. Below is what I get from my query (subset of data) Name Value ...
0
votes
1answer
100 views

SQL query to count number of payments made in a time period with data from two different table

Could someone please tell me how to write sql query for the following question? I am not able to get how to write the condition where it selects those user_id which is present on the said date and ...
1
vote
1answer
50 views

Get last login time of each User ID

How to get last login time of selected IDs? Table | user_id | lastlogin | 100 | 2016-03-05 | 110 | 2016-03-21 | 100 | 2016-03-29 | 100 | 2016-04-03 | 110 | 2016-...
0
votes
1answer
45 views

How to express functional dependency in relational calculus and SQL?

I want to express in relational calculus and SQL the following functional dependency associated with Vote. This is the first time I try to implement it in actual SQL and relational calculus. Vote: #...
1
vote
1answer
31 views

Subquery inside a select vs left join

Often I have to return the count of of some row in a query that fetch other non related rows. For example A table User a table Review and a Table picture User: id nickname Review: id to_user_id ...
0
votes
3answers
80 views

Syntax on Changing return String by Concatenation?

I successfully created the scalar function FN_TotalBilled in SQL Server, which gives me the total amount invoiced to a company using tables Invoices(InvoiceID, InvoiceTotal,..) and Vendors(VendorName, ...
4
votes
1answer
49 views

PostgreSQL join subquery can't restrict query

I'm working on a check-splitting project that splits out amount owed for an activity. Users can be a part of multiple 'groups', which each have their own running balances. I'm trying to run a query ...
0
votes
0answers
34 views

SQL Query with double distinct dates iteration, and start date < my date < final date

I have a kind of rental system database where user can rent an entire house, or just a room of the house. I have a table called offers which has columns id, room_id and a few more. If room_id = NULL,...
0
votes
2answers
73 views

Check if the trigger was fired by INSERT or DELETE

I know this is the easiest question you can find on this website but I wasn't able to find its answer on the internet. How to check if the trigger was fired by an INSERT or DELETE? Example CREATE ...
0
votes
1answer
75 views

how to get multiple rows from multiple tables concatenated into a single string in a stored procedure in Oracle

I have a table A that has 7 different 1-N relationships. Given an A id, now I am running 7 simple queries to get the N side rows. I suspect this could be sped up. Is it possible to: create a stored ...
3
votes
1answer
71 views

Correlated subquery to find total larger than state average

I have tables Vendors (VendorName, VendorState,....) and Invoices (InvoiceID, InvoiceTotal,...). I want to get the Invoices (as InvoiceId) that are larger than the Avg state InvoiceTotal. I know I ...
4
votes
1answer
34 views

Oracle SQL 12c How to correctly use compound foreign keys in compound primary keys?

DISCLAIMER: I am a student. I do not work for an airline. I do not pretend to be a professional programmer. Greetings, I want understand what I need to correct in order for these tables to work ...
0
votes
1answer
49 views

How to enrich event based data per row based on future events

I'm trying to figure out a not too terribly hacky way to solve the following issue. This data is currently available both in MySQL and RedShift, so a solution for either is fine though RedShift is ...
3
votes
2answers
83 views

Max date record for items for given set of dates

I am looking for some help to create an efficient SQL query (SQL Server 2012) that will identify the latest records (date) for all the items in the table (table 2 below) on each of a list of given ...
-1
votes
1answer
45 views

How to read the deleted value using a variable

I'm trying to get @OldValue = (select @fieldname from #del) to give me the value. Instead I get the column name for the result. Is there a way to write this so it can output the value. If I write ...
1
vote
0answers
22 views

How to design for scalable set operations?

This is the requirement for an educational application to teach words to language learners: Learner can declare that he/she knows a given word Learner can memorize the given word (to be managed by ...
0
votes
1answer
27 views

Append Records to a fixed number of columns using dynamic data [duplicate]

I have a table with 22,000 records formatted like so: Input [ID],[Cost Center],[Function] 1,01A1001710,GF000001 2,01A1001710,GF000802 3,02R1012000,GF000001 4,02R1012000,GF000059 5,02R1012000,...
8
votes
4answers
278 views

Separate letters from digits in alphanumeric string

I have an alphanumeric string as input and I want to get two results out of it: a string where all the numerals are removed and an integer that is the sum of all the digits in the input string. ...
0
votes
0answers
23 views

Specify what columns to display in a SHOW COLUMNS statement

Does MySQL offer any way to specify which columns to display in a SHOW COLUMNS FROM my_table statement? From this : Field Type Null Key Default Extra ------ ...
1
vote
2answers
37 views

Create view with group by and extra fields

I had a hard time putting the question title together but essentially I want to create a view like this: ALTER VIEW vPlaceStatistics AS SELECT COUNT (TypeID) as Count, TypeID FROM PlaceStatistics ...
0
votes
1answer
24 views

FullText Search

We came across an issue using FullText Indexes and SQL 2008 R2, not sure if it's by design or I am missing something. When we search using a fulltext index, I noticed SQL appears to trim leading and ...
0
votes
0answers
38 views

How would you migrate a virtual 2012 sql server cluster to a virtual standalone?

I have been tasked with coming up with a plan for p2v and v2v migration. I have never done v2v migration so I'm unsure how to plan it. Specifically, from a virtual SQL server cluster to a virtual ...
1
vote
2answers
43 views

Order by custom filter without certain data

I have a similar issue to the question in: ORDER BY using custom priorities for text columns ...except that I want to only get the data out if I have certain values. To clarify, we have a table ...
0
votes
3answers
33 views

Formatting out a column value - mm:ss.hh

I have a time in seconds and I want to convert it to the following formats possibilities: 121.17 -> 2:01.17 | 34.28 -> 34.28 | 133.56 -> 2:13.56 I am using the following: IF(Time > 60, ...
0
votes
1answer
37 views

Set values conditionally in Postgres UPSERT

We have an UPSERT feature achieved through on conflict do set. How can I set the values conditionally in the set statement, like if(excluded.col1 is null) col1=table.col1 else col1=excluded.col1 ...
1
vote
2answers
91 views

Counting number of occurences in a time period

I have a view that has 4 columns, an order date, machine reference number, item number, and quantity. How would I generate a list of machines that have consumed an item more than once in a given time ...
0
votes
1answer
51 views

How to make a query on multiple columns without writting all possible permutations?

I'm at my first question on stackexchange, as i have a few days since I struggle on this matter: I want to make a complex query(PLSQL) on a table that has col1,col2,col3,col4,col5 having values like (...
1
vote
4answers
176 views

Display both null and not null under same id but not both null or not null like XOR logic

In this table I need to select only the ids which have both null and not null values, and it should not be both not null or both are null in col1.
1
vote
1answer
128 views

Slot Time Challenge - Doctor Appointment database schema

I am trying to make a doctor appointment system where patient online will have two options: First time visit (this visit should be 20 mins) Follow up visit (this visit should be 10 mins) ...
6
votes
5answers
497 views

UNION ALL or other ways to return first row of result-set

I am writing a table valued function to geocode an address based on its Suburb, State and Postcode. I try to geocode the address using different methods, in order of decreasing accuracy: Exact match ...
1
vote
1answer
32 views

MySQL datetime to just date

Currently I have a table containing a column of type datetime. I have no need for the use of the HH:MM:SS that trail the variables stored in this column. I just need the YYYY-MM-DD. Is there any way ...
0
votes
1answer
47 views

Query distinct emails not logged in within a period of time

I need help in writing a query to return distinct emails that have not logged into ABC database in the past 5 months. The exact task I have been asked is as following: Create a table in ABC ...
0
votes
1answer
32 views

ORA-00932 on subquery join from subquery join

The only guess I have is that perhaps the size of the subquery is considered a CLOB? I have no null values for the fields selected AFAIK. SELECT DISTINCT kbq_ent.qid AS kbq_ent_qid, kbq_ent....
1
vote
1answer
41 views

Justified multi-value field in MySQL?

I have a table of data that contains 10 point values based on a score for an event (0 - 9 points). If a time is within the range, the point is awarded. There are 20 possible events. Is this a valid ...
0
votes
2answers
32 views

Update field in SQL Server Table Dynamicaly

I need to do a 1 time update of a Sql Server table. The table has a unique ID, contains a member number field and a FileName field which tells us what file the member data came from. What I am ...
5
votes
1answer
151 views

Concatenation, with separators, where any combo of input fields might be null

I have three columns, CITY, STATE, COUNTRY. One, two, or all columns may be NULL. With the following logic, a single string is made using the SQL below. It is messy, and I am wondering if there is a ...