The data in my table represents physical locations with the following data: A municipality name, a state(province/region), and a unique ID which is comprised of a prefix and postfix separated by a dash (all NVARCHAR). Name State UniqueID Atlanta Georgia A12-1383 The dash in the UniqueID is not always in the same position (can be A1-XYZ, A1111-XYZ, etc.). The postfix
Tag: sql
Retrieve rows where Column1 != Multiple Values from Column2
I have 2 Location columns. I need to find a way to show only exceptions, where Location1 != Location2, BUT there are multiple values in Location2 column that can be associated with value in Lcoation1. For example: San Diego in Location1 column can have 4 possible values in Location2 column (North, Central, South, San Diego) and such rows need to
How do I use lag to get the previous row before a specific time window of data?
Every day I create a table that looks like this: user_id received_at age_pref ethnicity_pref 1 10:01 18-28 open_to_all 2 10:05 18-23 open_to_all 1 10:08 18-30 open_to_all 2 10:07 18-25 Hispanic/Iatino 3 10:09 56-33 White It’s a table that lists the actions a user takes from 10am-11am. As you can see, there are 3 distinct user IDs. Using this, I’m trying
Postgres: Searching all sub-arrays of an array
I have a Postgres table with a json column, named raw_data, that is structured like this: [{“id”:1234, “name”:”John Doe”, “purchases”:12}, {“id”:1234, “name”:”Jane Doe”, “purchases”:11}] The number of sub-arrays can differ. It may be 1 or 20 or any number. I want to perform a query where I can return the entire table row if, say, ‘John Doe’ occurs in the
can’t set a foreign key on oracle sql
Here is my code: and it gave me this error: What is wrong with parenthesis in my code? this only happens when i set foreign key. Note: i am using SQL cmd from Oracle Database 11g Express Edition. I’ve tried making theses tables on MySQL and it worked fine. Answer No “FOREIGN KEY” keyword is used for inline constraints. So,
Count rows between time periods excluding time-overlap
I have a table where it holds some vehicle events, I need to count the number of event occurrences every 5 minutes. Sample : I was able to get the results by the following query : query results : The issue with the above results is that the query excluded Ids (15 and 28) the correct output should be :
PostgreSQL- If data in table, delete for stored procedure
I have this procedure, which I call with CALL pr_calc_quarter(2,2022), that insert data into a table erp.tb_quarter from a query. I need to add code to check that if there is already data in the table, to delete it before inserting new values. I was thinking of inserting the code at the beggining or before the insert into, but I
How do I conditionally select a unique value in SQL?
I’ve been tasked with returning only rows with unique IDs but returning a row for every ID in SQL. How would I go about this? Logic: For primary row, select where JOB_INDICATOR = ‘P’. If there are multiple rows, then use the record where PRIM_ROLE_IND = ‘Y’. If there are still multiple then select the lowest numbered EMPL_RCD starting at
How to find the two combinations of elements that are present the most in all the groups? [closed]
Closed. This question needs details or clarity. It is not currently accepting answers. Want to improve this question? Add details and clarify the problem by editing this post. Closed 3 months ago. Improve this question For example in this simple table: Group Element xxx a xxx b xxx c xxx d xxx e yyy a yyy b yyy f yyy
How to order queries execution in SQL script so that they are executed in the right order?
Let’s say that I have three tables: Table References A – B A C B and I have created a SQL DDL script with these tables’ definitions: script.sql: IMPORTANT: The order of definitions in the script can be random! Is there are way/tool that would take the script as input and return another SQL script with sorted DDL queries? Example: