Tag Info

New answers tagged

2

Assuming you always have pair , the following should give you desired result: SELECT a.event_id ,a.pair,a.versus, a.score as score1, b.score as score2 FROM table_name a INNER JOIN table_name b on (a.event_id = b.event_id and b.pair =a.versus and b.versus=a.pair and a.pair<b.pair) Side notes. 1. It makes sense to have a surrogate primary key column in ...


1

As explained in the documentation, there are rules on what makes a valid identifier. This rule is what affects your case: Identifiers may begin with a digit but unless quoted may not consist solely of digits. So, as @ypercube says, you need to quote your table name, either with double quotes or backticks: SELECT * FROM "2521118" ORDER BY id DESC ...


0

A common pattern for avoiding dups goes something like this: SELECT ... FROM ( SELECT ... SUM(...) FROM w ... GROUP BY ... ) AS x JOIN y ON ... WHERE ... ORDER BY ... This assumes table w has multiple entries that you want to see only once in the result. Hence the GROUP BY to get one copy of each, plus any aggregates you desire. Then you ...


0

Not really an answer, but too long for a comment. There has to be some missing information in your question. If Koza has 3 appointments and 2 payments, customers JOIN addresses JOIN appointments should have a cardinality of 6, but you say that it is 3? Are there some relationship between appointments and addresses that is missing in the question? Why should ...


3

You might also find the tee command to be of use. Log all output to an operating system file mytabs.txt. mysql> tee mytabs.txt; Logging to file 'mytabs.txt' Issue your show tables command as below. mysql> show tables; +-----------------+ | Tables_in_sport | +-----------------+ | billy | | player | | seasons | | team ...


4

You can use the pager (see docs) command in MySQL console or use the --pager startup option (docs again) to redirect the output of your commands to an external executable. To use the pager command and list the tables try this: mysql> pager less mysql> show tables You can also start MySQL to send all your output to an external executable like this ...


1

Another way: Unpivot the columns. Filter out the 0's. Group by ID and concatenate the column names. Something like this (assuming you are using Oracle 11g): SELECT ID, LISTAGG(Col, ',') WITHIN GROUP (ORDER BY Col) AS descr FROM atable UNPIVOT ( Val FOR Col IN (C1, C2, C3, C4, C5) ) u WHERE Val <> 0 GROUP BY ID ;


2

Try: select id from tab where id not in (select id from tab where index=0) group by id having max(index)+1!=count(*) order by id; Preventing that the combination of id and index is unique


2

create table index_test(id number, idx number); insert into index_test (id, idx) values (1,0); insert into index_test (id, idx) values (1,1); insert into index_test (id, idx) values (1,2); insert into index_test (id, idx) values (1,3); insert into index_test (id, idx) values (1,4); insert into index_test (id, idx) values (2,0); ...


-1

DECLARE @Line TABLE(Id INT) INSERT INTO @Line (Id) VALUES (1) INSERT INTO @Line (Id) VALUES (2) INSERT INTO @Line (Id) VALUES (3) INSERT INTO @Line (Id) VALUES (4) INSERT INTO @Line (Id) VALUES (5) INSERT INTO @Line (Id) VALUES (7) INSERT INTO @Line (Id) VALUES (9) ;WITH CTE as ( SELECT ROW_NUMBER() OVER (ORDER BY Id) as RowId, Id FROM @Line ) ...


1

Make a concatenation of every field in the form of: CASE WHEN columnX!=0 THEN 'textX,' ELSE '' END This would make: select id, trim(CASE WHEN column1!=0 THEN 'text1,' ELSE '' END|| CASE WHEN column2!=0 THEN 'text2,' ELSE '' END|| CASE WHEN column3!=0 THEN 'text3,' ELSE '' END|| CASE WHEN column4!=0 THEN ...


2

Something like (untested): select id, rtrim( case when c1 != 0 then 'column1,' end || case when c2 != 0 then 'column2,' end || case when c3 != 0 then 'column3,' end || case when c4 != 0 then 'column4,' end || case when c5 != 0 then 'column5' end, ',') as descr from tab1;


4

SQLState: 55P03 In Postgres 9.4.x I tested this by performing an unresolved SELECT FOR UPDATE in pgAdmin and then doing a SELECT FOR UPDATE NOWAIT in my Java app. I used the JDBC driver JDBC41 Postgresql Driver, Version 9.4-1201. The result was the following PostgreSQL Error Code. Message:ERROR: could not obtain lock on row in relation "my_table_" ...


5

Why is there an additional row with an empty ga and a count of zero? Because there are NULL values in the ga column. Why the count is zero? While COUNT(*) can never give 0 as a result (as it counts rows), COUNT(column) or COUNT(expression) can give 0 because it counts not null values in the column/expression. Thus the only logical explanantion is ...


-1

There would be marginal difference if any between both the queries, however, the second query is much simpler, so if it were me, I would prefer the second one! Another way to do this would be: SELECT MAX(events.id), events.user_id, events.event_name FROM events WHERE events.user_id = 1 Oh, and if you want to get the results of all user collectively, this ...


0

I was using one of two ways to get a random row: SELECT * FROM mytable ORDER BY RANDOM(); and SELECT * FROM mytable LIMIT 1 OFFSET <random number>; The offset method was faster for low offset numbers, otherwise super slow. An offset of 1 million took ~600 msec, while 100 million was 60 seconds (average query time for midpoint offset was 2.5 ...


0

I am very sorry, but I don't like the idea or row processing unless really needed. cursors might not be good for several reasons, you can read here. I managed to change my select to generate the script I needed: use msdb go DECLARE @R TABLE ( NAME VARCHAR(108) NOT NULL PRIMARY KEY CLUSTERED) INSERT INTO @R(NAME) SELECT 'UK15SUMMProduct' UNION ALL ...


3

Here is the whole query: Declare @job sysname Declare jobs_cursor Cursor For Select S.name From msdb.dbo.SYSJOBS S Inner Join (values ('UK15SUMMProduct') , ('US15SUMMProduct') , ('DE15SUMMProduct') , ('AT15SUMMProduct') , ('FR15SUMMProduct') , ('EU15SUMMProduct') , ('AU15SUMMProduct') ...


1

After your code add this: declare @query varchar(1000), @job_name varchar(1000) while (select count(*) from @R) > 0 begin select top 1 @job_name = [Name] from @R order by [Name] select @query = 'exec sp_delete_job @job_name = ''' + @job_name + ''', @delete_unused_schedule = 0' exec(@query) delete from @R where [Name] = @job_name end


0

Try some like that declare @cmd nvarchar(4000) exec sp_delete_job @job_name = 'WebFeed UK15SUMMProduct', @delete_unused_schedule = 0 SELECT 'exec sp_delete_job @job_name = '''+S.name+''', @delete_unused_schedule = 0'+char(13) FROM SYSJOBS S WHERE NAME LIKE ( SELECT NAME FROM @R) exec sp_executeSQL @cmd


1

Better yet: where orders.created >= '2015-07-01' and orders.created < '2015-07-01' + INTERVAL 1 MONTH; This works for DATE, DATETIME, DATETIME(6), leap year, etc. (Notice the <, not <=.)


2

In your first query you do ...orders.created <= '2015-07-31'; which is actually orders.created <= '2015-07-31 00:00:00'; since you have a timestamp or datetime datatype. Either use the DATE() function like you did in your other attempt, or write it like orders.created <= '2015-07-31 23:59:59';



Top 50 recent answers are included