I'm starting a thread here on stuff that I've found in my reading, and sort of half remember having been asked more than once (or at least once) on the Forums.
The discussion on random numbers for banners have been discussed several
times, and in several ways. Getting a random record (a la jump.cgi) has
been asked a few times. In the next release of MySQL (current 3.23.xx.beta)
There is a RAND() function that can be used in a Select statement:
> SELECT * FROM table_name ORDER BY RAND() Limit 1
Would return a single random record. Limit 5, would return 5 random records.
.
Count & Distinct
> Select COUNT(DISTINCT Field_name) FROM Table_name
Would return the count of distinct field names.
.
> Select COUNT(*) FROM people WHERE sex='M'
Would return the number of "Male" records in the table 'people'.
.
> Select sex, COUNT(*) FROM people GROUP BY sex
Would return a table with the number of M values and number of F values:
M | nn
F | nn
.
and finally,
.
> Select ID, Name, COUNT(*) AS count FROM Category
-> GROUP BY Name HAVING count > 1 ORDER BY count DESC
Would show which category Names in the Category table were duplicated.
ie: duplicate categories. (note: the -> means "continued" and should not
be typed into your query) You'd then:
> Select ID, Name from Category where Name = 'name_found_above'
to get the ID's of the records that needed merging, or just type the
name into the Links Admin and it would show both Category Records.
.More later.
http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/
Code:
Random Numbers/Random Record: The discussion on random numbers for banners have been discussed several
times, and in several ways. Getting a random record (a la jump.cgi) has
been asked a few times. In the next release of MySQL (current 3.23.xx.beta)
There is a RAND() function that can be used in a Select statement:
> SELECT * FROM table_name ORDER BY RAND() Limit 1
Would return a single random record. Limit 5, would return 5 random records.
.
Count & Distinct
> Select COUNT(DISTINCT Field_name) FROM Table_name
Would return the count of distinct field names.
.
> Select COUNT(*) FROM people WHERE sex='M'
Would return the number of "Male" records in the table 'people'.
.
> Select sex, COUNT(*) FROM people GROUP BY sex
Would return a table with the number of M values and number of F values:
M | nn
F | nn
.
and finally,
.
> Select ID, Name, COUNT(*) AS count FROM Category
-> GROUP BY Name HAVING count > 1 ORDER BY count DESC
Would show which category Names in the Category table were duplicated.
ie: duplicate categories. (note: the -> means "continued" and should not
be typed into your query) You'd then:
> Select ID, Name from Category where Name = 'name_found_above'
to get the ID's of the records that needed merging, or just type the
name into the Links Admin and it would show both Category Records.
.
http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/