New answers tagged

2

You could just use %_99_% instead of %99%. % means 0 ore more characters _ means exactly 1 character So %_99_% means at least 1 character before and after 99, and anything before and after. SQL> select * from t1; C1 -------------------- 1991 1199 9911 SQL> select * from t1 where c1 like '%_99_%'; C1 -------------------- 1991


0

Please see below for my suggested answer. The answer below was written for and tested on Microsoft SQL Server but it appears that you are using MySQL so you may have to translate it a bit. It's not the best answer and I hate using a cursor, but it's the only thing I can think of for this specific problem. DECLARE @Table1 TABLE ( Heirarchy ...


0

Ok, it take me some time but I think it works well: select clickcume nr_clicks, avg(view_b) avg_view_before from( select userid, clickcume, max(viewcume) view_b from( select a.*, b.time_v, b.viewcume from ( select userid, time as time_c, count(*) over (partition by userid order by time) as clickcume from views) a join ( select userid, ...


4

rn cannot be referenced in WHERE because it's defined in the SELECT clause. Try using a subquery instead: SELECT * FROM ( select payee_id,start_dt, row_number() over(partition by payee_id order by start_dt) as rn from xxx ) AS sub where rn = 1; For more reading, google "logical query processing order".


1

You can use CROSS APPLY to get the MAX(date) for each Venue and then ORDER BY it: SELECT Event_number, Venue, [Date], Attendance, rn FROM dbo.SampleTable s CROSS APPLY ( SELECT md = MAX([Date]) FROM dbo.SampleTable WHERE s.Venue = Venue ) ca ORDER BY ca.md DESC, Venue, s.rn SQL Fiddle By the way, you can insert into the sample table with 1 ...


2

You can use the the max() with an over() clause partitioned by Venue to get the max date for each venue and then use that as the first column in the order by clause. select T.Event_number, T.Venue, T.Date, T.Attendance, T.rn from ( select S.Event_number, S.Venue, S.Date, S.Attendance, ...


0

Perhaps changing your JOIN condition slightly might help if you want the value B_GRADUATION_TERM that doesn't match TERM_CODE without creating a Cartesian join : From: grad.B_GRADUATION_TERM = prog_enroll.TERM_CODE; To: (grad.B_GRADUATION_TERM = prog_enroll.TERM_CODE or grad.B_GRADUATION_TERM is NULL); As shown completely here: SELECT ...


0

As a rule of thumb conditions on the Outer table are usually placed in WHERE, but on the Inner table in ON: SELECT DISTINCT student.Student_ID as 'Student_ID', case when class.class_id is null then 'No Class ATM' else class.class_id end as 'Class_ID', case when Attendance.Attendance_Status is null then 'Not Present' else ...


0

A transaction like Kenneth Fisher answered And look at optimizing Why are you updating after the insert? Why not just insert a hard coded TargetAmountSetTable.isModified of 1 in the insert? Another option is to output the whole lot of joins once to a #processTable


2

In general what you want here is an explicit transaction around your code. BEGIN TRANSACTION ... your code .... COMMIT However there are down sides to concurrency here as well. While your transaction is open any locks needed to perform the tasks will be held until the end of the transaction. That means that the next set of code that needs to run will ...


3

According to the My Oracle Support, ORA-01792 is caused due to an unpublished bug. Oracle suggest to alter the query and/or view definitions to avoid the error. However in cases where the SQL cannot be adjusted then the checking can be disabled by: SQL> alter system set "_fix_control"='17376322:OFF'; Alternatively an interim patch for this fix (patch ...


0

Tricky around the oldest driver but I'll have a go: SELECT name, birthday FROM driver WHERE name in (SELECT name FROM driver where driver.address = 'haifa') AND d.id in (SELECT d.id FROM event_list) AND birthday = (SELECT DISTINCT MAX(birthday) FROM driver WHERE name IN (SELECT name FROM driver WHERE driver.address = ...


0

Try this code: SELECT TOP 1 name, birthday FROM driver INNER JOIN event_list ON driver.d_id=event_list.d_id WHERE driver.address='haifa' ORDER BY birthday;


5

I suppose you know the date_start and date_end and you won't work on more than 1000 days although it can be extended. This example works with these values: SET @date_start := CAST('20150101' as date); SET @date_end := CAST('20150108' as date); Below queries return each day between @date_start (2015-01-01) and @date_end(2015-01-08). This is similar to a ...


0

Another solution, probably with the same performance: SELECT * FROM ( SELECT post_id, COUNT(IF(vote > 0, vote, NULL) AS voters, AVG(IF(vote > 0, vote, NULL) AS average FROM wp_imdb_rating GROUP BY post_id ) ORDER BY voters * average DESC LIMIT 0, 100 Note: Whenever ...


0

Do I need to learn how to create a Macro/ a Routine/ a Sub Routine? Yes. See the Stack Overflow question below for an example of how to do it using VBA code and QueryDef objects. Is it possible to pass parameters programmatically in a Microsoft Access update query? If you require further assistance please as a new question on Stack Overflow.


0

It seems to be some old limitation of the optimizer maybe, aliases to aggregating expressions are not allowed in more complex expressions in ORDER BY. You can get around it by using the full expression instead of the alias: ORDER BY COUNT(CASE WHEN vote > 0 THEN vote ELSE NULL END) * AVG(CASE WHEN vote > 0 THEN vote ELSE NULL END)


2

That can be done with a simple SELECT statement. SELECT count(*) AS count_ , favorite_color_ FROM person_ GROUP BY favorite_color_ HAVING count(*) > 1 ORDER BY count_ DESC ; The count(*) command is an aggregate function supported by Postgres and gives the number of rows with duplicate values in a column specified by the GROUP BY clause. To omit the ...


1

You can use the LIKE clause. SELECT Office FROM typology WHERE NAME='Jones' AND OWNER='Lasi' AND TYPE='TKT' AND LIST_OF_VALUES LIKE '%AUD%'


2

As mentioned in the comment by jkavalik constraints are there to enforce data integrity. While most modern optimizers can use the information in constraints to help make access decisions that is not their purpose. Here is a question you need to ask yourself - if the integrity of the data is not important - then how important is the data? If it is worth ...


3

There is absolutely nothing wrong with joining on columns that are not PKs/FKs. If you are concerned about efficiency then the key is to have appropriate indexes defined to support the join operations you are using. Also, don't assume that the existence of a foreign key implies the existence of an index - some databases automatically create such an index but ...


0

This is most easily accomplished in an Access query by using the ConcatRelated() function. For more information see the following question on Stack Overflow: Combine values from related rows into a single concatenated string value


1

Let's think out of the box. Instead of having a "snapshot", let's have a "log". What you currently have is "current" state of things; adding a "log" would provide the "history", from which could be derived the 'lost' info. One way to implement the log is to have a TRIGGER on INSERT or UPDATE of the table, and have the trigger write to the log file. This ...


0

Okay, the performance here is being limited by the index of "urlShort" even though the field isn't being updated. Another reason to normalise the schema so that this is no longer necessary.


10

I would suggest storing the number as a BIGINT, and simply displaying the number the way you want it. DECLARE @Num BIGINT; SET @Num = 2421402015; SELECT CONVERT(VARCHAR(11), REPLICATE('0', 11 - LEN(@Num))) + CONVERT(VARCHAR(11), @Num); As pointed out by Aaron Bertrand in his comment, it is better performance-wise to not calculate the length of the ...


5

To demonstrate that what you're saying in your question is not true: DECLARE @x TABLE(y VARCHAR(11)); INSERT @x(y) VALUES('02421402015'); SELECT y FROM @x; Results: y ----------- 02421402015 You need to tell us more details about what is going on so we can understand your problem better. But most importantly, you need to consider separating storage ...


10

Query that adds "is_primary_key" field for each column The sys.key_constraints and sys.index_columns catalog views will identify the PK columns. You can JOIN them together and then use that set as a derived table to LEFT JOIN to your main query which will allow for not filtering out columns that are not part of a PK. Also, you want to use [user_type_id] ...


4

Ques.1: First remember that primary keys are not always clustered keys which is what I think you really want. However the answer is pretty much the same either way. You need to look at sys.indexes to see the is_primary column. This will tell you that the index is they primary key. Then you need to look at sys.index_columns to see the actual columns in ...


2

It's possible on most database systems, but you can't have 2 columns with the same name -- so you would have to rename one of them, e.g. Select a.Field1, a.Field2, b.Field2 as NewFieldName, -- using an alias b.Field3 INTO... Notice in this example, both table 'a' and 'b' have a column Field2.


0

Try the below query will solve your problem: SELECT ID, STUFF( (SELECT ', ' + CAST(Item AS VARCHAR(20)) [text] FROM TBL12 WHERE ID = t.ID FOR XML PATH(''), TYPE) .value('.','NVARCHAR(MAX)'),1,2,' ') Items FROM TBL12 t GROUP BY ID Since you add many tags in the post, I solved in MSSQL using STUFF. Working fiddle: ...


3

Yes, it will affect on the scheduled backups (LSN chain). If you need to make one additional full backup. You need to use COPY_ONLY option. This option will make the additional full backup that does not alter the differential chain. This way you do not affect the LSN chain at all. The LSN’s of any differential backups taken after the new ad hoc backup ...


3

Itzik Ben-Gan's blog post (link provided by Daniel Hutmacher) has some nice solutions to the problem for SQL Server 2012 and later: The Last non NULL Puzzle. Here is one more that will work in older versions (even 2005) and can be easily adapted for more columns. I haven't tested for efficiency or compared to the other solutions but I'd expect it to work ...


3

The error message you show would be the result of: VALUES ('3915105', E'\x1A', 44), -- and not '44' ('3915135', E'\x1A', 'fe88ff8f-6b4d-4e3d-8020-3475a101d25e') (Trimming some of the irrelevant columns.) Data types of columns in a free-floating VALUES expression are determined by the first row. String literals default to text, numeric literals ...


2

Many variants have been posted, but the simplest check to find violating rows that have NULL in every column of the set is: SELECT * FROM integrations.accounts WHERE (qb_id,xero_id,freshbooks_id,myob_id,ppy_id) IS NULL; The WHERE expression evaluates to TRUE if and only if every single column IS NULL. Details: NOT NULL constraint over a set of columns ...


5

This query: Uses ANSI JOIN (LEFT, INNER, ...) Uses LEFT JOIN for each table in order to count Sessions IPs without Input or Downloads Uses DISTINCT for each COUNT in order to remove duplicates added by the JOIN between tables Counts values for totals and counts ids for unique counts Query: SELECT s.ip , COUNT(DISTINCT s.id) , COUNT(DISTINCT ...


0

If you want to run in parallel you have to design for it. Things to consider is using partitions so they can be scanned in parallel. Don't forget that parallelism in general will try to use full scans on segments. Partition pruning can help reducing the size of your scans. If you really are using pq for this sort of small queries, make sure you have enough ...


1

I recently experienced this same issue which brought me to this page. @MartinSmith was on to something when he recommended updating your statistics and explain plan. I will like to add that you should also try to ensure you take a look at running jobs/queries which may create locks and thereby slow down response time. In my case the culprit was the job ...


1

I happen to work for an IoT\M2M company and we have been using SQL Server just fine for years... Much of the architecture is all event\transaction driven and process based on an \time\event\status and have denormalized the data for multi-purpose uses. Now, ideally when you experience the type of growth that we have over the last few years with millions of ...


0

Better solution would be to install docker or virtualbox and run the OracleXe image within one of those containers. Once docker is running you can easily launch a new OracleXe container with two commands. Already configured to allow easy access via SQLDeveloper. I tested my own steps by downloading the docker toolbox, installing and running the OracleXe ...


1

There are two sites which really stand out for learning SQL, especially Oracle (for MySQL see below). Oracle forums. Pay particular attention to the posts of Frank Kulash and Solomon Yakobson (sometimes things of rare beauty) but anyone with a rep of > 20k merits attention. Check the rankings on the forums page. Orafaq forums. Solomon Yakobson (again), ...


1

The references in the function's WHERE clause are comparing with the identically named columns in its SELECT clause, rather than the argument list. Try changing it as follows (note the addition of ARG_ to the argument names and the predicates in the WHERE clause): create or replace function func_get_Open_Days_2 ( ARG_SUBMITTED_BY_PERSON_ID in ...


1

You can utilize NTILE to create n quantiles: select id, min(value) from ( select id, value, ntile(10) over (order by value) as nt from tab ) as dt group by id, nt


-2

@JulienVavasseur is correct in that you need to format your queries using Ansi92 join syntax. If nothing else, it will make it easier for us to read and help you with your questions. However, looking at the problem, it seems as easy as adding a Distinct to the count of session.id to reduce it from 7882 to 752. When you ask for a count, you're going to get ...


3

This query GROUPs by Ids and uses: CASE WHEN to invert 0 and 1 and SUM it for Unsuccessful sessions SUM of all the 1 for Successful sessions. Query: SELECT t1.ip , COUNT(*) as `ALL` , SUM(t2.success) as Successful , SUM(CASE t2.success WHEN 0 THEN 1 ELSE 0 END) as Unsuccessful FROM Tab1 t1 INNER JOIN Tab2 t2 ON t1.id = t2.session GROUP BY ...


2

You have a non-SARGable query - even if you have good indexes, you're not using them with that query. First, a knee-jerk reaction to pageiolatch_sh is reading pages from disk into the buffer; you don't have enough RAM for the data it's trying to pull. Second, you need to look at the execution plan and its use of indexes - or the lack thereof. Quit using ...


7

If you want the interestingData and timestamp from the same row (the most recent row that exceeds the threshold), and if you want to include all rows that exceed the threshold even if some rows for that ID don't meet the threshold, then: ;WITH x AS ( SELECT ID, interestingData, [timestamp], [count] = COUNT(1) OVER (PARTITION BY ID), rn = ...


1

assuming the pattern in your example Column 1 is constant (begins w/1 followed by some number of zeros) then it should be a simple matter of converting your int to text, removing the left-most character, and then converting back to int. The variable-length run of zeros should be disregarded in the process. Something like: Cast(Ltrim(Cast(Column 1 As text)), ...


2

Based on your sample, I believe you only need a GROUP BY ID with an HAVING clause on MIN([interestingData]) > 300. Query: SELECT ID , interestingData = MIN([interestingData]) , timestamp = MIN([timestamp]) , count = COUNT([ID]) FROM Table1 GROUP BY ID HAVING MIN([interestingData]) > 300; SQL Fiddle with sample data and query. Output: ID ...


3

The WHERE clause is specific to each individual SELECT so you would need to move the one you have to the first SELECT like this: SELECT PubKey, Title FROM Publication WHERE EXISTS ( SELECT * FROM UserPublication WHERE UserPublication.PubKey = Publication.PubKey ) UNION SELECT NoteKey, Title FROM Note However, if you want to specify the ...


1

create a composite non clustered index on mainview(typeid, timestamp). change your "where" on mainview so that you're not using a function against the mainview columns. this may require you to precalculate these values as variables prior to running the query if you need these to be more dynamic. WHERE MainView.TimeStamp BETWEEN '2015-12-09 10:00' and ...



Top 50 recent answers are included