SQL recipes - Revision history

Shawn at 01:28, 8 September 2013

2013-09-08T01:28:46Z

← Older revision		Revision as of 18:28, 7 September 2013
Line 37:		Line 37:

	The negative searches use NOT REGEXP, and search for our figures 2 and 4 preceded exclusively either by the beginning of the string or a comma, and followed exclusively either by a comma or the end of the string		The negative searches use NOT REGEXP, and search for our figures 2 and 4 preceded exclusively either by the beginning of the string or a comma, and followed exclusively either by a comma or the end of the string
			==See Also==
			[[Switching a web site to UTF-8]]

	[[Category:Computers]]		[[Category:Computers]][[Category:Programming]]

Shawn: /* Finding Duplicates */

2013-08-15T02:15:48Z

Finding Duplicates

← Older revision		Revision as of 19:15, 14 August 2013
Line 9:		Line 9:

	We use ORDER BY to show the duplicates in adjacent rows in the result.		We use ORDER BY to show the duplicates in adjacent rows in the result.

			===Finding multiple matches in a matching table===
			In the following example we search for pics with tags 1 and 3 but not 2 or 4:
			SELECT picid, GROUP_CONCAT(tagid ORDER BY tagid) AS thispicstagids FROM matches
			GROUP BY `picid`
			HAVING thispicstagids REGEXP '(^\|,)1(,[0-9]),3(,[0-9]),73(,\|$)'
			AND thispicstagids NOT REGEXP '(^\|,)2(,\|$)'
			AND thispicstagids NOT REGEXP '(^\|,)4(,\|$)'
			We create an ordered list of all the tags of each pic with the GROUP_CONCAT function.

			Then we use a regular expression to search that list. Here's a breakdown of that expression:
			'^' starts us at the beginning of the string. If this wasn't here, due to the nature of the next part, we might match numbers that ended with the figures (like allowing '24' when you only wanted '4').

			'([0-9],)' searches for any amount (the last asterisk) of the group in parenthesis, being any amount of digits (the class declared by the square brackets) followed by a comma.

			'1' searches for our first search id. Replace with the first figure in your array.

			'(,[0-9])' searches for any amount of the group of a comma and any number of digits. This is where the search should end if you are searching for only one tag.

			',' then there should be at least one comma between the first tag and the second. Only insert this comma if you're looking for more than one tag.

			'3' searches for our second search id. Replace with the second and following figures in your array.

			'(,[0-9])' searches for any amount of the group of a comma and any number of digits, allowing for tags to appear after your last search figure. If this wasn't here, we'd match 3 when 33 was present.

			'$' matches the end of the string, for the same reason we matched the beginning.

			The negative searches use NOT REGEXP, and search for our figures 2 and 4 preceded exclusively either by the beginning of the string or a comma, and followed exclusively either by a comma or the end of the string

	[[Category:Computers]]		[[Category:Computers]]

Shawn: Reverted edits by 200.176.13.215 (Talk) to last revision by Shawn

2012-01-04T05:41:51Z

Reverted edits by 200.176.13.215 (Talk) to last revision by Shawn

← Older revision		Revision as of 22:41, 3 January 2012
Line 1:		Line 1:
	~~Stands back~~ from the ~~kyeboard~~ in ~~amazement! Thanks!~~		I use a database often, mostly for structuring and holding the data at EnergyTeachers.org. I usually use MySQL, but also sometimes sqlite. Here are some of my tricks.
			===Finding Duplicates===
			It's one thing to identify data that is duplicate, and you see hints how to do it by counting members in a group grouped by what field(s) should be duplicate and showing those groupings that have a count larger than one. But we often want to do something to the duplicate, like delete it. A better method in SQL is to create two tables from one and find pairings across them. Here's the code for showing all duplicate rows where two fields match exactly:
			SELECT t1.*
			FROM tablename as t1, tablename as t2
			WHERE t1.uniqueid<>t2.uniqueid AND t1.field1=t2.field1 AND t1.field2=t2.field2
			ORDER BY t1.field1, t1.field2
			The first WHERE condition ensures we don't match rows to themselves. This only works on tables where there is a unique id.

			We use ORDER BY to show the duplicates in adjacent rows in the result.

			[[Category:Computers]]

Xeentav: FTTKStLSVhbOfDlB

2012-01-03T03:12:29Z

FTTKStLSVhbOfDlB

← Older revision		Revision as of 20:12, 2 January 2012
Line 1:		Line 1:
	~~I use a database often, mostly for structuring and holding the data at EnergyTeachers.org. I usually use MySQL, but also sometimes sqlite. Here are some of my tricks.~~		Stands back from the kyeboard in amazement! Thanks!
	~~===Finding Duplicates===~~
	It's one thing to identify data that is duplicate, and you see hints how to do it by counting members in a group grouped by what field(s) should be duplicate and showing those groupings that have a count larger than one. But we often want to do something to the duplicate, like delete it. A better method in SQL is to create two tables from ~~one and find pairings across them. Here's~~ the ~~code for showing all duplicate rows where two fields match exactly:~~
	SELECT t1.*
	~~FROM tablename as t1, tablename as t2~~
	~~WHERE t1.uniqueid<>t2.uniqueid AND t1.field1=t2.field1 AND t1.field2=t2.field2~~
	~~ORDER BY t1.field1, t1.field2~~
	~~The first WHERE condition ensures we don't match rows to themselves. This only works on tables where there is a unique id.~~

	~~We use ORDER BY to show the duplicates~~ in ~~adjacent rows in the result.~~

	~~[[Category:Computers]]~~

Shawn: /* Finding Duplicates */

2011-12-10T21:20:04Z

Finding Duplicates

← Older revision		Revision as of 14:20, 10 December 2011
Line 6:		Line 6:
	WHERE t1.uniqueid<>t2.uniqueid AND t1.field1=t2.field1 AND t1.field2=t2.field2		WHERE t1.uniqueid<>t2.uniqueid AND t1.field1=t2.field1 AND t1.field2=t2.field2
	ORDER BY t1.field1, t1.field2		ORDER BY t1.field1, t1.field2
			The first WHERE condition ensures we don't match rows to themselves. This only works on tables where there is a unique id.

			We use ORDER BY to show the duplicates in adjacent rows in the result.

	[[Category:Computers]]		[[Category:Computers]]

Shawn: Created page with 'I use a database often, mostly for structuring and holding the data at EnergyTeachers.org. I usually use MySQL, but also sometimes sqlite. Here are some of my tricks. ===Finding …'

2011-12-10T21:16:55Z

Created page with 'I use a database often, mostly for structuring and holding the data at EnergyTeachers.org. I usually use MySQL, but also sometimes sqlite. Here are some of my tricks. ===Finding …'

New page

I use a database often, mostly for structuring and holding the data at EnergyTeachers.org. I usually use MySQL, but also sometimes sqlite. Here are some of my tricks.
===Finding Duplicates===
It's one thing to identify data that is duplicate, and you see hints how to do it by counting members in a group grouped by what field(s) should be duplicate and showing those groupings that have a count larger than one. But we often want to do something to the duplicate, like delete it. A better method in SQL is to create two tables from one and find pairings across them. Here's the code for showing all duplicate rows where two fields match exactly:
SELECT t1.*
FROM tablename as t1, tablename as t2
WHERE t1.uniqueid<>t2.uniqueid AND t1.field1=t2.field1 AND t1.field2=t2.field2
ORDER BY t1.field1, t1.field2

[[Category:Computers]]