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

learn more… | top users | synonyms (2)

1
vote
1answer
40 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
43 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
30 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
44 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
64 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
47 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
67 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
31 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
80 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
43 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
253 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
19 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
32 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
41 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
35 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
85 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
49 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
160 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
91 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
481 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
46 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
31 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
147 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 ...
2
votes
0answers
51 views

Does Trace Flag 8295 applies to SQL Server 2012?

I've been searching about the undocumented Trace Flag 8295. It's been introduced with KB 2476322 in SQL Server 2008 and "'Creates a secondary index on the identifying columns on the change tracking ...
0
votes
1answer
50 views

How to declare reserved words as variables in Oracle PL/SQL?

What is the best way to bypass the Oracle SQL reserved-word constraint for PL/SQL value declaration? I realize bind and substitution may be acceptable alternatives for an actual query, but what about ...
0
votes
0answers
30 views

SQL Server Index recomended but already there (DMVs)

I am using the DMV views to get a list of index recommendations and then I am reviewing each of them to make sure they make sense and don't overlap etc. Some examples are Index ON X ( Y ) and Table ...
4
votes
1answer
20 views

Sort table rows and save the row numbers using UPDATE

I have Postgres 9.5 with a table movimientos that has the following data: | id | concepto | movimiento | numero | orden | | 1 | AJUSTE 1 | 2542 | 0 | 2 | | 2 | APERTURA | 12541 |...
0
votes
0answers
18 views

Error with displaying ranges of dates when using pivot table

can i know is there any problem with this codes as when i use datagridview and then te pivot table it sometimes the dates runs whereby sometikmes the dates does not follow in sequence. I not sure ...
1
vote
1answer
24 views

Add variable value in single quoted string. Expand variable within single quotes

I have tried different variations of the below commands but non work. I simply want to run the following command using a variable within the location string: CREATE TABLESPACE data OWNER appuser ...
0
votes
3answers
68 views

How to check if range between two numbers is available in table of ranges

I have following table of number ranges Create table Ranges { Id int identity(1,1) primary key, RangeFrom int not null, RangeTo int not null } Insert into Ranges(RangeFrom, RangeTo) VALUES (1,...
1
vote
1answer
25 views

Query: How to join on different tables depending on the filled column?

I'm pretty new to the field of SQL, but already solved a lot of problems, so until now i am familiar with the different possibilites of JOIN(s) (explicit and implicit syntax), group by, distinct and ...
5
votes
3answers
381 views

Why set Auto Update Statistics to False?

I've just inherited about 20 instances of SQL Server, as part of a wider acquisition project. I'm in the process of assessing performance and I don't like the way maintenance plans have been ...
1
vote
2answers
93 views

How to get the Sum of all column values in the last row of a resultset without using UNION?

I need to get the sum of all column values of a result set in the last row. Here is my SQL query. SELECT CS_YEAR AS YEAR, CS_MNTH AS MONTH, CS_WK AS WEEK_NO, 'Total' AS ...
1
vote
3answers
70 views

How to find values from one table missing in another?

I am a Junior Developer. I have been asked to create a subquery to solve this problem. If the question is not clear please let me know so I can provide more details. I have two tables - Article and ...
1
vote
1answer
44 views

how do I change regexp_substr(to_char(bar), '^.*?$', 1, 1, 'm') to only read 132 characters each time

how do I change the query below so it only reads and displays 132 characters each line with w(id, line#, line, rest) as ( select id, 1, regexp_substr(to_char(bar), '^.*?$', 1, 1, 'm'), ...
0
votes
1answer
21 views

How do you normalize a table that is indirectly dependent on a has-many relationship?

I currently have the following database design: Product: contains various description attributes Belongs to many ProductSets ProductSet: Has many Products Belongs to many Stores Store: Has ...
0
votes
1answer
44 views

extract values from different stored procedures and create one report

At the company I work, the third party taking care of the database. So there is giant complex stored procedure that produces the result for one parameter only. When management needs a desirable ...
0
votes
1answer
63 views

Slow search by LIKE and other values (1 million rows+)

I'm currently developing a kind of search website where one can search for business companies in my country. The database table has 1 million rows and quite a few colums (42 columns). But I find it ...
5
votes
2answers
75 views

Postgres Update Limit

I'm curious if Postgres has anything in place where I could limit the amount of rows kept for a ID. For example, say I have a users table and login_coordinates table. Every time the user logs in, ...