Grouping Elements

4 Message(s) by 3 Author(s) originally posted in mysql discussion

From: Legend

Date: Saturday, October 20, 2007

I was wondering if it is possible to group

A group G is a non-empty {set} upon which a {binary} operator
	* is defined with the following properties for all a,b,c in G:

	  Closure:     G is closed under *,  a*b in G
	  Associative: * is associative on G, (a*b)*c = a*(b*c)
	  Identity:    There is an identity element  e  such that
		       a*e = e*a = a.
	  Inverse:     Every element has a unique inverse a' such that
		       a * a' = a' * a = e.  The inverse is usually
		       written with a superscript -1.

	(1998-10-03)

linked elements using SQL

<language, database, standard> /S Q L/ An industry-standard
	language for creating, updating and, querying {relational
	database management systems}.

	SQL was developed by {IBM} in the 1970s for use in {System R}.
	It is the {de facto standard} as well as being an {ISO} and
	{ANSI} {standard}.  It is often embedded in general purpose
	programming languages.

	The first SQL standard, in 1986, provided basic language
	constructs for defining and manipulating {tables} of data; a
	revision in 1989 added language extensions for {referential
	integrity} and generalised {integrity} {constraints}.  Another
	revision in 1992 provided facilities for {schema} manipulation
	and {data administration}, as well as substantial enhancements
	for data definition and data manipulation.

	Development is currently underway to enhance SQL into a
	computationally complete language for the definition and
	management of {persistent}, complex objects.  This includes:
	generalisation and specialisation hierarchies, {multiple
	inheritance}, user defined {data types}, {triggers} and
	{assertions}, support for {knowledge based systems},
	{recursive query expressions}, and additional data
	administration tools.  It also includes the specification of
	{abstract data types} (ADTs), object identifiers, {methods},
	{inheritance}, {polymorphism}, {encapsulation}, and all of the
	other facilities normally associated with object data
	management.

	The emerging {SQL3} standard is expected to be complete in
	1998.

	According to Allen G. Taylor, SQL does _not_ stand for
	"Structured Query Language".  That, like "SEQUEL" (and its
	pronunciation /see'kw*l/), was just another unofficial name
	for a precursor of SQL.  However, the IBM SQL Reference manual
	for DB2 and Craig Mullins's "DB2 Developer's Guide" say SQL
	_does_ stand for "Structured Query Language".

	{SQL Standards (http://www.jcc.com/sql_stnd.html)}.

	{An SQL parser
	(ftp://ftp.ora.com/published/oreilly/nutshell/lexyacc/)} is
	described in "Lex & Yacc", by Levine, Mason & Brown published
	by O'Reilly.

	{The 1995 SQL Reunion: People, Projects, and Politics
	(http://www.mcjones.org/System_R/SQL_Reunion_95/)}.

	["A Guide to the SQL Standard", C.J. Date, A-W 1987].

	["SQL for Dummies", Allen G. Taylor, IDG Books Worldwide].

	(2005-11-17)

directly. For example, if I have some table

<database> A collection of {records} in a {relational database}.

	(1997-06-04)

like:

Column A | Column B | Column C | Column D
1 | 2 | A11 | A32
2 | 3 | A13 | A22
3 | 4 | A14 | A23
5 | 6 | A19 | A24
7 | 8 | A18 | A25
1 | 9 | A17 | A26

I want to group all linked items together. So in the above case my
output'd look something like:
Table 1:
1
2
3
4
9

Table 2:
5
6

Table 3:
7
8

or maybe something like with the above one still preferred:
1,2,3,4,9
5,6
7,8
extracted into another table...

One other question I had was, can a data

<data, data processing, jargon> /day't*/ (Or "raw data")
	Numbers, {characters}, {images}, or other method of recording,
	in a form which can be assessed by a human or (especially)
	input into a {computer}, stored and {processed} there, or
	transmitted on some {digital channel}.  Computers nearly
	always represent data in {binary}.

	Data on its own has no meaning, only when interpreted by some
	kind of {data processing system} does it take on meaning and
	become {information}.

	People or computers can find patterns in data to perceive
	information, and information can be used to enhance
	{knowledge}.  Since knowledge is prerequisite to wisdom, we
	always want more data and information.  But, as modern
	societies verge on {information overload}, we especially need
	better ways to find patterns.

	1234567.89 is data.

	"Your bank balance has jumped 8087% to $1234567.89" is
	information.

	"Nobody owes me that much money" is knowledge.

	"I'd better talk to the bank before I spend it, because of
	what has happened to other people" is wisdom.

	(1999-04-30)

base have a million tables? I
mean, if I had many such groups,'d it be efficient to create a
table for each group or is there any other approach?

From: Kees Nuyt

Date: Saturday, October 20, 2007

On Sat, 20 Oct 2007 05:38:22 -0000, Legend
wrote in message:

I was wondering if it is possible to group
linked elements using SQL directly.

A lot is possible with SQL.
It's not clear what you mean with 'linked'.

For example, if I have some table like:
Column A | Column B | Column C | Column D
1 | 2 | A11 | A32
2 | 3 | A13 | A22
3 | 4 | A14 | A23
5 | 6 | A19 | A24
7 | 8 | A18 | A25
1 | 9 | A17 | A26
I want to group all linked items together. So in the above case my
output'd look something like:
Table 1:
1
2
3
4
9
Table 2:
5
6
Table 3:
7
8My IQ is limited, I can not discover a reason why the given

input'd lead to the requested output.
Are you sure your sample

<digital signal processing> The result of measuring the
	amplitude of an analog signal at a specified time.  In
	{digital signal processing} a sample is a signed or unsigned
	number and the number of samples per second is called the
	{sample rate}.

	(2001-06-06)

data is exact?
Can you elaborate on what you are trying to achieve?

or maybe something like with the above one still preferred:
1,2,3,4,9
5,6
7,8
extracted into another table...

It can be done, but why store

[probably from "main store"] In some varieties of Commonwealth
	hackish, the preferred synonym for {core}.  Thus, "bringing a
	program into store" means not that one is returning
	shrink-wrapped software but that a program is being {swap}ped
	in.

	[{Jargon File}]

in another table?

One other question I had was, can a database have a million tables?

In general, even if physically possible, that's a very bad
idea, and a sure sign of a flaw in the database design.

I mean, if I had many such groups,'d it be efficient
to create a table for each group

No, not at all.

or is there any other approach?

Only store primary data in the database,
write views to look at the data in any way you want.
--
( Kees
)
c[_] Famous last words - Do not worry, I can handle

1. <programming, operating system> A simple item of data that
	identifies a resource.  For example, a {Unix} file handle
	identifies an open file and associated data such as whether it
	was opened for read or write and the current read/write
	position.  On the {Macintosh}, a handle is a pointer to a
	pointer to some dynamically-allocated memory.  The extra level
	of indirection allows on-the-fly {memory compaction} or
	{garbage collection} without invalidating application program
	references to the allocated memory.

	2. <jargon> An alias used intended to conceal a user's true
	identity in an electronic message.  The term is common on
	Citizen's Band and other amateur radio but, in that context
	usually means the user's real name as {FCC} rules forbid
	concealing one's identity.

	Use of grandiose handles is characteristic of {crackers},
	{weenies}, {spods}, and other lower forms of network life;
	true hackers travel on their own reputations.

	Compare {nick}.

	[{Jargon File}]

	(2004-07-20)

it. (#441)

From: strawberry

Date: Saturday, October 20, 2007

wrote in message:

On Sat, 20 Oct 2007 05:38:22 -0000, Legend
wrote in message:
>I was wondering if it is possible to group
>linked elements using SQL directly.
A lot is possible with SQL.
It's not clear what you mean with 'linked'.
>For example, if I have some table like:
>Column A | Column B | Column C | Column D
>1 | 2 | A11 | A32
>2 | 3 | A13 | A22
>3 | 4 | A14 | A23
>5 | 6 | A19 | A24
>7 | 8 | A18 | A25
>1 | 9 | A17 | A26
>I want to group all linked items together. So in the above case my
>output'd look something like:
>Table 1:
>1
>2
>3
>4
>9
>Table 2:
>5
>6
>Table 3:
>7
>8
My IQ is limited, I can not discover a reason why the given
input'd lead to the requested output.
Are you sure your sample data is exact?
Can you elaborate on what you are trying to achieve?
>or maybe something like with the above one still preferred:
>1,2,3,4,9
>5,6
>7,8
>extracted into another table...
It can be done, but why store in another table?
>One other question I had was, can a database have a million tables?
In general, even if physically possible, that's a very bad
idea, and a sure sign of a flaw in the database design.
>I mean, if I had many such groups,'d it be efficient
>to create a table for each group
No, not at all.
>or is there any other approach?
Only store primary data in the database,
write views to look at the data in any way you want.
--
( Kees
)
c[_] Famous last words - Do not worry, I can handle it. (#441)

This question was also posted in mysql.com's own forums.

I understand the relationship between the dataset and the result set:
1 is linked to 4 via 2 & 3.
5 & 6 are linked directly, as are 7 & 8, and 1 & 9

So the OP wants a result that looks like:
1,2,3,4,9
5,6
7,8

This requires recursion, which can be achieved in a variety of ways,
including:

1. LEFT JOIN the table to itself as often as you think necessary to
traverse the tree.
2. Use a Stored Procedure
3. Use a nested set model instead of an adjacency

<networking> A relationship between two {network} devices,
	e.g. {routers}, which are connected by one media segment so
	that a packet sent by one can reach the other without going
	through another network device.  The concept of adjacency is
	important in the exchange of routing information.

	Adjacent {SNA} {nodes} are nodes connected to a given node
	with no intervening nodes.  In {DECnet} and {OSI}, adjacent
	nodes share a common segment ({Ethernet}, {FDDI}, {Token
	Ring}).

	(1998-03-10)

list
4(my personal favourite). Use an external language

1. <language, programming> {programming language}.

	2. <human language> {natural language}.

	(1998-09-07)

(like php) to
handle the recursion
5. A combination

1. <mathematics> A {set} containing a certain number of
	objects selected from another set.

	The number of combinations of r objects chosen from a set of n
	is

		n C r = n! / ((n-r)! r!)

	where "n C r" is normally with n and r as subscripts or as n
	above r in parentheses.

	See also {permutation}.

	2. <reduction> In the theory of {combinators}, a combination
	denotes an expression in which {function application} is the
	only operation.

	(1995-04-10)

of some of the above.

From: Kees Nuyt

Date: Saturday, October 20, 2007

On Sat, 20 Oct 2007 14:51:23 -0000, strawberry
wrote in message:

This question was also posted in mysql.com's own forums.
I understand the relationship between the dataset and the result set:
1 is linked to 4 via 2 & 3.
5 & 6 are linked directly, as are 7 & 8, and 1 & 9

Aha, now I see. Thanks for the clarification.
--
( Kees
)
c[_] I do not want to be your other half.
I believe that One and One make TWO.
(Alanis Morrisette: "Not the Doctor") (#185)

Next Message: Previous Row Comparison

Blogs related to Grouping Elements

xml generation tools
... xml multiple namespaces xml multiple root elements xml multiple schemas xml music feed xml music file xml music files xml music player xml mysql xml mysql database xml mysql import xml mysql java xml mysql php xml mysql tutorial xml ...

msdba.dll
... msde manager freeware msde manager serial msde manager v1.1.1 msde manual msde maryland msde memory msde memory allocation msde memory leak msde memory limit msde memory usage msde mysql msde named instance msde network cable 1722 ...

Több mint 600 AJAX szkriptet
Ajax Slider Bar Form Element. Update a value inside a MySQL Database using the AJAX method when drag stops. .... FX.Sort - Sort with style. This effect will rearrange a group of elements using a transition. ...

database driven menu asp
... sql database frontpage template database frontpage templates database fsbo home database fsbo home top database ftd movie database fuel management pump station system database fuengirola hosting mysql database full gale group text ...

list swore
... users group list users groups list users in a group list users in active directory list users in group list users in group active directory list users in group linux list users in group unix list users in linux list users in mysql ...

free web hosting hotlinking
... web hosting mysql free web hosting mysql image site free web hosting mysql php free web hosting mysql support free web hosting no ad banner free web hosting no ad banners free web hosting no adds free web hosting no adds php mysql ...