Tag Info

Hot answers tagged

11

As I read the question, the basic recursive algorithm required is: Return the row with the earliest date in the set Set that date as "current" Find the row with the earliest date more than 90 days after the current date Repeat from step 2 until no more rows are found This is relatively easy to implement with a recursive common table expression. For ...


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 = ...


7

DateTime has a precision of has a 0.00333(1/300th) seconds, so sql server rounds value to nearest 1/300th. If you are using SQL Server 2008 above, use DateTime2 which can have a precision of millisecond, even down to 100 nanoseconds and can hold the accurate value. SELECT CAST('2014-11-28 15:06:02.165' AS DATETIME2(3));


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)


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 ...


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 ...


4

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


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 ...


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 ...


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 ...


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 ...


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


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 ...


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.


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 ...


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).


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 ...


1

One of the biggest problems with MyISAM is the low write concurrency. In the most general case, a single write (update, delete or insert) to a single MyISAM table will block all other writes and reads. Multiple reads, however, can happen concurrently. You can force MyISAM to perform concurrent inserts at the end (not for updates and deletes), but it ...


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 !!!


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.


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 ...


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 ...


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 ;


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 ...


1

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vGetVisits]') AND type in (N'U')) DROP TABLE [dbo].[vGetVisits] GO CREATE TABLE [dbo].[vGetVisits]( [id] [int] NOT NULL, [mydate] [datetime] NOT NULL, CONSTRAINT [PK_vGetVisits] PRIMARY KEY CLUSTERED ( [id] ASC ) ) GO INSERT INTO [dbo].[vGetVisits]([id], [mydate]) ...


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 ...


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 ) )


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 ...



Only top voted, non community-wiki answers of a minimum length are eligible