Tag Info

New answers tagged

0

Size of the table is 53Gb without indexes. Obviously you have indexes, one of them a GIN index, which is typically multiple times the size of a plain table storing only the indexed column. I expect you get around 180 GB for SELECT pg_total_relation_size(osmm.topographicarea); Details on measuring sizes: Measure the size of a PostgreSQL table row As ...


1

This is straightforward (a simple GROUP BY): SELECT Client, thickness, material, SUM(amount) as amount FROM your_table GROUP BY Client, thickness, material; Change your_table for the name of your table. If only 1 row exists of one combination of Client, thickness and material, it will select it as is. Check the GROUP BY MySQL page for more information.


0

If I understood your requirement correctly it should be something like. -- Clean up IF OBJECT_ID (N'dbo.udfLastHitRecursive', N'FN') IS NOT NULL DROP FUNCTION udfLastHitRecursive; GO -- Actual Function CREATE FUNCTION dbo.udfLastHitRecursive (@param1 int , @param2 int , @MyDate datetime) RETURNS TINYINT AS BEGIN -- Your returned value 1 ...


9

The query results are correct, as your query finds all teams and results where a team played against itself (home_team = away_team). You probably want to use 2 joins to teams in you query: SELECT home.team_name AS home_team_name, away.team_name AS away_team_name, r.score FROM results AS r JOIN teams AS home ON r.home_team = ...


1

From my (rather limited) knowledge of php, I assume you want to combine the two queries into one: SELECT m.*, p.frequency FROM ( SELECT sid, COUNT(*) as frequency FROM plays WHERE time > NOW() - INTERVAL 3 DAY AND sid <> '' GROUP BY sid ORDER BY COUNT(*) DESC LIMIT 50 ) AS p JOIN music AS m ON m.sid = p.sid ...


0

This can be done by creating a trigger in the database. This will execute no matter how the data gets modified. Triggers can act before or after an insert, update, or delete. In your case I believe you would want a before insert trigger. For example: CREATE TRIGGER mytrigger BEFORE INSERT ON table1 FOR EACH ROW BEGIN INSERT INTO table2 ...


1

SELECT U.User,IFNULL(U1.cnt,0) Count1,IFNULL(U2.cnt,0) Count2 FROM user U LEFT JOIN (SELECT User1 User,COUNT(1) cnt FROM events GROUP BY User1) U1 USING (User) LEFT JOIN (SELECT User2 User,COUNT(1) cnt FROM events GROUP BY User2) U2 USING (User); If the user has id as the primary key and fields in event as User1 and User2, do this SELECT ...


3

Based on your question, I would index the Timestamp column with the clustered index. And to make the index unique, just make sure to include the identity column in the index definition: ... PRIMARY KEY CLUSTERED ([Timestamp], [Id]) If query performance for queries on Exchange_Id is still an issue after that, you can also add a non-clustered index that ...


1

Please move the SET a.field=(a.field+b.subfield) after ON a.id = b.subid and before WHERE So, the query should look like this: UPDATE table a INNER JOIN (SELECT subid, subfield FROM subtable WHERE ..... ) AS b ON a.id = b.subid SET a.field=(a.field+b.subfield) WHERE ... Give it a Try !!!


0

Could be done much simpler, I guess; select id, type, rate, abs(25 - rate) as delta from rates order by delta LIMIT 3


2

There is no magic; if you have 8 columns to output then you'll need to have 8 expressions to change the output in any way. (Have you considered normalization?) A slightly less verbose approach would be: SELECT Hail_N = COALESCE(NULLIF(hail_n, ''),'0'), Hail_S = COALESCE(NULLIF(hail_s, ''),'0'), ... FROM dbo.vw_hail_wind_directions; -- always use ...


0

To save bytea to the disk server-side, it is necessary to be a database superuser. Normal users are not allowed to write to the filesystem. Assuming superuser rights, the simplest way is to implement it as a function in one of the "untrusted" languages. Example in pl/perlu: CREATE FUNCTION bytea_to_file(bytea,text) RETURNS void AS $$ open(my $fd, ...


2

SELECT DISTINCT a.id, a.etc FROM years y JOIN activitymapper am ON (y.targetyearid = am.targetyearid) JOIN activity a ON (a.id = am.activityid) WHERE y.targetyearname IN ('Year1', 'Year2') Should do it. We're going from years to activity through activitymapper, selecting each activity only once (DISTINCT).


6

select string_agg(c,'') from ( select distinct regexp_split_to_table(lower(name),'') as c from data ) t The inner select generates one row for each character, and the outer then aggregates that to a long string. If you want the characters sorted, you can use an order by for the aggregate string_agg(c,'' order by c)


0

According the error message posted in the comments this is a faulty disk that cannot serve write requests (at a certain location).


-1

In Sql Server: just go to security->schema->dbo.. Double click dbo... then click on permission tab->(blue font)view database permission and feel free to scroll for required fields like "execute"....help yourself with choosing....and grant,with grant or deny controls....hope this will help


0

Work around would be to remove the DB from AG and then clear the log and add it back to AG


0

Check this: DECLARE @jobId BINARY(16) SELECT @jobId = CONVERT(uniqueidentifier, job_id) FROM msdb.dbo.sysjobs WHERE name = 'Your_Job_Name'


0

PROPOSED QUERY SELECT NoOfTeams,COUNT(1) PlayerCount,GROUP_CONCAT(PlayerID) PlayerIDs FROM (SELECT PlayerID,IFNULL(NoOfTeams,0) NoOfTeams FROM player) p GROUP BY NoOfTeams HAVING COUNT(1) > 1; The key to making this query work was to make NULL values become zero and then tabulate SAMPLE DATA DROP DATABASE IF EXISTS deeba; CREATE DATABASE deeba; USE ...


2

If I can understand correctly what you are looking to do, you want to SELECT all records from TableA that only appear once, but don't already appear in TableB. You can do this with a GROUP BY and HAVING to identify those that only appear once in TableA, then do an EXCEPT to filter out those that already appear in TableB. SELECT int, com FROM TableA ...


-3

SELECT playerid,noofteams FROM `player` where noofteams >=1 or noofteams is null


0

Select a.PlayerID, b.PlayerID, isNULL(a.NoOfTeams,0) NoOfTeams From dbo.Table a Join dbo.Table b on isNULL(a.NoOfTeams,0) = isNULL(b.NoOfTeams,0) Where a.PlayerID <> b.PlayerID This will give duplicates though. So you could do something like this if you don't want them: Select a.PlayerID, b.PlayerID as PlayerID2, isNULL(a.NoOfTeams,0) NoOfTeams Into ...


1

Relational modelling starts for the identification of classes of "things." To these we apply normalisation to remove data update anomolies. The results are implemented as tables with a column for each attribute we identify. We do not start from a bunch of domains, look around for "things" with the same bunch of domains, shoehorn them into the same table ...


3

As developer, not a full-time DBA, I use Postgres for some of my work. But it is not my focus. I found Postgres very confusing and frustrating when getting started. After a long career using 4D, I was an pro in relational database design and normalization, but a newbie with SQL and "black-box" database engines. Below is a list of the resources that helped ...


4

You're going to have to make your application not put the ORDER BY inside the subquery (maybe it has an option to not use a needless subquery in the first place). As you've already discovered, this syntax is not supported in SQL Server without TOP. And with TOP, unless you want to leave some rows out, using TOP 100 PERCENT is going to render the ORDER BY ...


3

ASSESSMENT Your query is kind of dangerous to the MySQL Query Optimizer. I have an old post (Problem with MySQL subquery). The question posed involved this query DELETE FROM test WHERE id = (SELECT id FROM (SELECT * FROM test) temp ORDER BY RAND() LIMIT 1); Although the MySQL Query Parser will work with this query and accept it's syntax, the MySQL ...


1

Combine the two tables with UNION and ORDER BY timestamp. You can also add an addition (type) column so the source of a row can be identified: SELECT 'comment' AS type, id, comment_content AS content, time_stamp FROM comments UNION ALL SELECT 'activity', id, activity_content, time_stamp FROM activities ORDER BY timestamp ;


0

If you are using InnoDB, You can secure your transaction with SELECT ... FOR UPDATE sentence http://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html With this, you can perform an exclusive lock on the row while your transaction is not finished. Other sessions will wait until the row lock is released.


3

--======================================================= -- delete the duplicate records from table @t -- keeping a single unit of each -- marcelo miorelli 24-nov-2014 --======================================================= --======================================================= --create a table variable and insert records in it -- just for this ...


2

select ROW_NUMBER() OVER (PARTITION BY num, com ORDER BY identity_field) as 'RowNumber', t1.* into ##TableA from TableA t1 --delete t1 select t1.* from TableA t1 join ##TableA t2 on t2.identity_field=t1.identity_field where t2.RowNumber > 1 but first make backup of your table


6

Ok, there are a few things to explain first. (1) The Connect to Server prompt within the Login properties is only asking for authentication. You cannot specify a database name within the Server Name field of that window in order to connect to it. For the server name you specify either server or server\instance name. You would connect to a specific database ...


2

There are several ways to get to this answer. I'll tackle it in two parts. The first is to find out how many groups there are. This is a simple summary query: select count(distinct GroupID) from MEMBER The second is how many groups one person is in. This is similar: select PID, COUNT(*) from MEMBER group by PID Next you want to tie these ...


0

If you know the total number of groups, you could try: SELECT PID, COUNT(GROUPID) FROM MEMBERS GROUP BY PID; Then limit the results by the number of groups.


0

PostgreSQL is not so far beyond MySQL, and there is plenty of documentation and a variety of GUIs to work with. Check out NaviCat, it's very helpful (though pricy).


4

It shouldn't take long to get up to speed to a reasonable degree if you've used another RDBMS. Read some guidance on PostgreSQL for MySQL users to help you adapt to sequences vs auto_increment, ANSI-standard quoting (though you should be using that in MySQL already), the stricter data type checking, how authentication and roles work, psql's backslash ...


0

I was able to figure out my own problem. The HR system is based on Peoplesoft and it includes something called an effective sequence. For this specific employee they had more than one effective sequence on the 01/20/14 effective date which was causing the duplication. Here is the finished SQL: SELECT CheckViewHrsErn.EMPLID, JobDta.JOBCODE, ...


0

While there's still some missing info (it would help to know if there is only one table, or if there are two tables, the names of those tables, and which columns are held in each), I think this might help. From what you wrote, it sounds like there are two tables (let's call them 'table1' and 'table2'), and table1 has columns 'id', 'title', 'date', while ...


1

The WHERE clause needs a condition, if you type: SELECT Name FROM MyTable WHERE MAX(Age); there is no condition. Try to translate it to a sentence: Select the name from MyTable where maximum age... Now compare it with this statement: Select the name from MyTable where the age field is equals to (select the maximum age) And now in SQL code: ...


1

I believe Teradata supports window functions, so you can use them to pick the larges value. select name, age from ( select name, age, dense_rank() over (order by age desc) as rn from MyTable ) t where rn = 1; If there are multiple names with the same age, all would be shown. If you only want to pick one, use row_number() instead of ...


0

There could be more than one names that share the same maximum age. To find them all, you can use the fairly standard SQL statement, which should work in almost all DBMS: SELECT Name FROM MyTable WHERE Age = (SELECT MAX(Age) FROM MyTable) ; Only in some DBMS, like SQL-Server (and Teardata I think), you could use this as well: SELECT TOP (1) WITH TIES ...


0

The SQL term you are looking for is Join. This will allow you to combine records from the two tables. Using your example above the SQL syntax to produce your required results would be: SELECT roid,uid,uname,problem,prostatus from table1 JOIN table2 ON table1.uid = table2.uid


2

A numeric column without a value is null: select * from the_table where column_numeric is null; There is no equivalent concept of an "empty string" for numbers.


1

My team once had to build a database for reporting financials with alternative time windows, including fiscal years, fiscal quarters, and fiscal months. The relationship between dates and these units was documented, but really messy. So here is what we did. We created a table, call it Almanac, with one row per date. (In reality, we had one row per work ...


4

You missed closing bracket in line: user VARCHAR(100 NOT NULL, should be: user VARCHAR(100) NOT NULL,


1

create table `certificates`( `id` INT NOT NULL AUTO_INCREMENT, `common_name` VARCHAR(100) NOT NULL, `csr` LONGTEXT NOT NULL, `type` VARCHAR(100) NOT NULL, `certificate` VARCHAR(100) NOT NULL, `user` VARCHAR(100) NOT NULL, `creation_date` DATE, PRIMARY KEY ( id ) )


0

Looks like you have multiple entries in PS_AL_CHK_HRS_ERN that meet the criteria. To Summarize you will have to use SUM(CheckViewHrsErn.EARNINGS) and add a GROUP BY clause. SELECT CheckViewHrsErn.EMPLID, JobDta.JOBCODE, CheckViewHrsErn.CHECK_DT, SUM(CheckViewHrsErn.EARNINGS) FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn LEFT JOIN PS_JOB JobDta ON ...


2

So, as mentioned in the comments, since you apparently have the authority to modify the data model itself, there are perhaps better solutions available to you at this point. The direct answer to your question is to update your new prices table to reference your preferred products record in each duplication case, then purge the other duplicated products ...


0

In the absence of GROUP BY clause the query considers the whole relation as one group. e.g. select count(*) from dual having count(*) > 5;


0

Your inner query should look like SELECT DISTINCT rp.post_id, CASE WHEN pm.meta_key = 'htk_premiere_date' THEN pm.meta_value ELSE '' END AS premiere_date FROM wp_related_people AS rp JOIN wp_posts AS p ON rp.post_id = p.ID JOIN wp_postmeta AS pm ON pm.post_id = p.ID WHERE ...


2

Well, Apologies to all those SQL guru's - this might be a simplistic answer. Since the product table will only have one record per product you don't need the ID column and can just use the 'sku' as the key. Insert into that table a distinct list of the products e.g INSERT INTO products (sku,ProductName) SELECT DISTINCT (sku,ProductName) FROM ...



Top 50 recent answers are included