MYSQL :: Random Selection

Random Rows, Non-random Ordering
Normally to select random rows from a table I'd do something like:

SELECT field1, field2, RAND() AS random
FROM mytable
ORDER BY random
LIMIT 5
Unfortunately, this time I need 5 random rows sorted on a different field:

SELECT field1, field2, RAND() AS random
FROM mytable
ORDER BY field2
LIMIT 5
Is there a way that I can still sort by field2 and yet still select 5 rows at random? Could the former query be run as a subquery and then a second ORDER BY clause run on those results? Worst-case I can re-sort the results in my host language, but a pure SQL solution would be best I think.

Bonus points if the answer can always put a particular row first in the results.

Many-to-many Selection
I did a search, but they results were not helpful.

This should be a very simple query, but I can't figure it out.

I have a table like:
A | B
-----+-----
1 | 5
2 | 4
2 | 5
2 | 6
3 | 5
3 | 6
3 | 7

What I want to do is select unique ids from A such that for each item from A, B = 5 and B = 6 BUT NOT B = 4.

So in the sample table above, the result would be:
A 1: fails, since there's no B = 6
A 2: fails, although there is B 5 and 6, there is a B 4
A 3: passes

Therefore, the selection query would return just 3 from column A.

Does this make sense? What would the query to do what I want?

Selection
I want to SELECT only the next row of data that is after the current date. i.e. If I had a show coming up on the 15th of may, today being the 9th, how would I setup a select statement to select only the row that has the show listing for the 15th?

Top Ten SELECTION
I have a table with reviews, each record has a name in it, how do I count the names and select out the top ten?

Here's my MSQL Details

rev_id
item_id
title
name
email
date
review
rating
valid
ip
timestamp

Conditional Selection
I have a table that contains some words and their translation, in various languages. I would like to select the translations for a particular language but use the English word in case their are no translations.

I know how I could do it with PHP, but ideally I would like to do it in SQL only.

I'm not sure it's clear at all, so here's an example:

ID, translation, language
'BACKGROUND', 'Background', 'eng'
'INTRODUCTION', 'Introduction', 'eng'
'BACKGROUND', 'Contexte', 'fra'

I would like to retrieve the French translations whenever they exist. In this case, only "BACKGROUND" has been translated, so I would need "INTRODUCTION" in English.

The SELECT result should thus be:
'INTRODUCTION', 'Introduction', 'eng'
'BACKGROUND', 'Contexte', 'fra'

Date Selection
This is my sql selection, it works good but when I do my query with a selection with two months it wont work.

So when I select between June 1 2004 and June 30 2004 it works okay but when I select between June 30 2004 and July 16 2004 it wont select anything. Of course I understand why it is because the 1st datepart is bigger then the 2nd datepart so it cant select anything.

But I dont know how to solve this. This is no MySQL but MSSQL, prolly that wont make a big difference in solving this.

SELECT
comment, name, datefield

FROM TGESZ

WHERE
(DATEPART(DAY, datefield) >= 30) AND (DATEPART(MONTH, datefield) >= 6)
AND (DATEPART(YEAR, datefield) >= 2004)
AND (DATEPART(DAY, datefield) <= 16) AND (DATEPART(MONTH, datefield) <= 7)
AND (DATEPART(YEAR, datefield) <= 2004)

ORDER BY
datefield

DESC

Hierarchical Selection
I have a problem that I'm hoping someone here will be able to solve for me.

I have two tables in my DB-

t_Items and t_Relations.

t_Items is basically a list of items, identified by a unique ID.

|ID | Name
---------------
| 0 | item 0
---------------
| 1 | item 1
---------------
| 2 | item 2
---------------
| 3 | item 3
---------------

t_Relations defines relations between two items.

| FirstID | SecondID
------------------------
| 0 | 1
------------------------
| 1 | 3
------------------------
| 3 | 5
------------------------

Now, What I need to do is this:

I need to write a select which finds the link between two items, so If I were to give that select the IDs: 0 And 5 It would return the items: 0, 1, 3 and 5 (with some kind of marker which signifies that they are one "tree").

If no Hierarchy exists it dosn't return any rows, and if more than one Hierarchy exists (there could be two or even more routes from one item to another) it returns all the items, with some kind of marker to distiguish the different "tree"s.

Version Selection
I am wanting to perform a fresh install of mysql on Fedora Core 6. I am assuming the Generic RPM package is best, but not sure whether to use the static or dynamic linked version.

5 Greatest Of A Selection
if i have all score made by all player of categorie and i just want to keep all 5 best scorer of each team

so imagine that i have for each player the info : player name, player team player score.

so i want to find the 5 greatest of all team in only one request.

because for one team at the time it is not hard.

i work on this and now im there:

SELECT `Team`,`Player`,`Score` FROM `Score` LEFT JOIN `Team` USING (PlayerID) ORDER BY `Team`,`Score` DESC

And this is close from where i want to be, the only thing that is wrong is that i just want the 5 best scorer of EACH team

MYSQL Selection
I have a question on how I do a multiple selection from a database. Basically I have one table that is this:

SELECT * FROM show_episodes ORDER BY show_episodes.release_date DESC LIMIT 6

but within this table is a (Show ID) number that represents the name of a show on another table within the same database.

How do I write up a php script that basically says if the show id=2, then show name?
Do you know what I mean?

Invert Selection
I have two tables. They are 'contacts' and groups_to_contacts

In one instance I need to select all contacts who are assigned to a group. This is easy enough.

SELECT contacts. *
FROM contacts, groups_to_contacts
WHERE contacts.id = groups_to_contacts.contactsID;

But in the other case, I need to select all contacts that are not assigned to a group. How do I invert the above query to return exactly the opposite results.

If I write
SELECT DISTINCT contacts. *
FROM contacts, groups_to_contacts
WHERE contacts.id != groups_to_contacts.contactsID;

I get every record returned, because every contact.id does not equal at least one of the groups_to_contacts.contactsID.

Selection Problem
I have a table as follow:

Name Country Month Major Year
Pete Canda Jan Sci 2004
Pete Canda Feb Sci 2004
John China Jan Art 2004
John China Jan Art 2005
Mary Italy Jan Art 2004
Lond Italy Feb Sci 2005
Lond Italy Mar Sci 2005

My objective is to extrct part of the table so that the output cannot have a record that only has different in Month. i.e. the result should be:

Name Country Month Major Year
Pete Canda Jan Sci 2004
John China Jan Art 2004
John China Jan Art 2005
Mary Italy Jan Art 2004
Lond Italy Feb Sci 2005

How can I input the SQL in order to achieve this target?

Conditional Selection
I'm trying to select all rows where if the field isChargable is Yes then the field charged must equal Yes to be selected. At the moment I'm trying:
PHP Code:
$query = 'SELECT * FROM calls WHERE status="closed"
AND (CASE isChargable IS "Yes" THEN charged = "Yes" END)
ORDER BY "opened" ASC';

But this doesn't work. If anyone could help me out with this I'd be very grateful, as I haven't managed to find anything relating to my problem online so far.

Database Selection
say I have a questionnaire where user has to answer a question from a selection list, e.g. "describe your personality" and the possible answers are relatively long. e.g. "I try to stay on top of things", "I sometimes fall behind", "I tend to procrastinate", etc.
What is the most efficient way to store the user's answer if I need to display them later? (a) use abbreviations e.g. "staytop", "fallbehind", "procras" and then use another function to translate those values (b) use an ENUM list or (c) store the whole answer.

Line-selection
when doing a boolean match search on a text field, is there a posibility that mysql returns 2 lines where searched words are present?
I want something like google: at google-search-result yyou always see 2 lines out of the document where the searchwords are in.

Selection From Two Tables
i have two tables say tab1 and tab2.in tab1 i have every record identified by an ID i.e tab1.1d the same id is entered in tab2 by the name of sid i.e tab2.sid for each record the data is same based on tab1.id=tab2.sid
i am using the following query to select data from both table.i have confimed data is there in both tables with id=sid=39 but still i am geting EMPTY result.. i.e nothing is selected and neither i get any error

SQL Selection Based On Date
I'm using this function / string to pull data based on yesterday:

$tomorrow = date('Y-m-d',strtotime('tomorrow'));

How can I pull data similarily based on "last month"?

Advanced Date Selection
My database table holds 2 date fields containing the start of an event and the end of an event.

Eg:
Row 1 : event_start = 񟭇-05-31' | event_end = 񟭇-06-02'
Row 2 : event_start = 񟭇-06-06' | event_end = 񟭇-06-07'
Row 3 : event_start = 񟭇-06-12' | event_end = 񟭇-06-12'
Row 4 : event_start = 񟭇-06-31' | event_end = 񟭇-07-02'
...

Now i would like to run a query on this table to get me the days that have events planned within the month of a given date.

If i would run this query correctly i would get the following results with the data above:
01
02
06
07
12
31

This is what i've got till now which only gives me the days an event start, i'm having trouble implementing the range in this query.

Using mysql 4.1.20

SELECT
DAY(event_start)
FROM
events
WHERE
event_start BETWEEN ADDDATE(LAST_DAY(SUBDATE(񟭇-06-27', INTERVAL 1 MONTH)),1) AND LAST_DAY(񟭇-06-27')
OR event_end BETWEEN ADDDATE(LAST_DAY(SUBDATE(񟭇-06-27', INTERVAL 1 MONTH)),1) AND LAST_DAY(񟭇-06-27')
GROUP BY
DAY(event_start)

Grouping Fields For Selection
I'm pretty sure this is possible, don't know if maybe I need to use an index, nor do I know whether I should use an index.

What I have is contacts table with nearly 30 columns. 10 are for personal details, and 10 are for office details. I then have two fields, one to say add personal details to public address book, another to say add office details to public address book. There is no "public address book" table, I'm just going to pull the details from the contacts table where these values are set to Y.

What I want to do is basically the following, using one select statement:
CODESELECT home_address1, home_address2 ... FROM contacts WHERE home_public = 'Y', SELECT office_address1 ... FROM contacts WHERE office_public = 'Y'

Selection Of Records In List
I was wondering how to query MySQL to display only a selection (1-20,
21-40) of a query in an HTML list. Can that be done in an SQL
statement, or do you have to use PHP to dissect the returned array
with for n elements to n+20 elements. How does Google achieve the
returning of results in its search engine. I imagine that the same
query may not necessarily be executed each page listing. I am
wondering whether the results from the query would return an array of
say 36000 elements.

Selection From Multiple Tables
I've got three tables which all use a slightly different table structure but have a few similar columns.What I need to do is have a Query that looks through EACH of the three tables for rows that have a status of active and a spotlight of on.Then once I've got those rows,I need to order it via date_full.

I've never had to do a large query involving multiple tables before but I had originally thought the below would work, but unfortunately it doesn't:

SELECT * FROM community_events, knowledge_base, press_releases
WHERE status = 'active' and spotlight = 'on'
ORDER BY 'date_full' DESC

I get the following error:

"#1052 - Column: 'STATUS' in where clause is ambiguous".

Obviously this is because the status (and spotlight) column is in each table. How do I get around this? I'd like someone to aid me in writing the query, but also some explanation of what the query does?

Limit The Selection To 25 Rows
I am using MySQL 3.23. I would like to know how I can limit the selection to 25 rows based on the latest 25 tests and same batch. For example;

select SN from tester where current_timestamp() > Date and UPS BC-06X1-H-SP-AUX1-C = Batch,? limit to 25 rows?

Help With Selection Of Datetime Fields
I'm having some trouble with certain SELECT statements that I need to execute. Basically I have a field of DATETIME format, and I need to be able to do things like:

Select where <mydatetimefield> is between now and 15 minutes ago.
Select where <mydatetimefield> is between now and 1 day ago.
Select where <mydatetimefield> is between now and 1 hour ago.

Can anyone point me in the general direction of some examples of this sort of thing?

Specific Month Selection
how to select the results for just cpecific month?

Query Distinct Selection
What I want 2 do is to select from 2 different columns that could have the same values e.g.My_Table
{
I_pk
input1
input2
X_value
}
in the input1 field I have several names that could be or not in the input2 field so I need that my query shows every word that's in the input1 & the input2 but without repeating any of this words like a distinct but with the 2 fields

Error - Problem In Product Selection:
I had just updated my website, however when I click on any subcategory on the Index.php page I get the following error message:

Problem in Product Selection: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay.

I have reviewed individual .php files and cannot find anything that could link me to this error message, I also looked through the MYSQL and could not find anything. I have been adding new products to the site but can see it except if it is though the main index page.

I was told by my hosting company to do the following, I have minimal knowledge of the MYSQL. The Developer that worked on my site, did a crappy job and I am just fixing so far all his bad work.

*****************************************************
Regarding the large MySQL query you are creating, please use an ON or USING clause to each table join so that you limit or decrease the amount of permutations the MySQL database needs to examine (thus decreasing the SELECT size).

Please be advised that the code you are using should have this in front of the main query.

mysql_query("set sql_big_selects=1");

Engine Selection For Temp Tables
You can select which engine you're using when creating temp tables like this:

CREATE TEMPORARY TABLE tmp ENGINE=Memory
...

I would preferably like to use the Memory engine for all my temp tables since it gives me the best performance, however as the size of the database grows (and because of this the temp tables created will grow in size) I will run up against the size limit for the temp table.

Is there a way to specify a "fallback", for example if I select the Memory engine but there isn't enough memory to have it gracefully fall back to MyISAM?

Conditional Selection Of Table Columns
I am searching for a mysql-command to make a selection on some table-columns only if a certain column exists otherwise the query should be on some alternative column.
e.g. I'd like to make a selection 'SELECT columnA, columnX FROM table' only if the column 'columnA' exists, otherwise make a selection on 'columnB' instead of 'columnA' (lets suppose column 'columnB' exists).

Is there a possibility to form such a conditional mysql-query. Please no replies suggesting a workaround with multiple queries.

Product Price Selection Through Mysql Query
I have following data in table named "productprice" in mysql.

id minqty maxqty price
1 10 20 10.00 $
2 21 30 19.00 $
3 31 40 28.00 $

My question is that what will be the query to find the price

Case #1 :
If quantity is greater than 30 [maxqty] then it should select record # 3.

Case #2 :
If quantity is less than 10 [minqty] then it should select record # 1 .

Multiple Table Selection Is This Possible - Newbie Question ?
I hope I have explained this well enough for you to understand what I am trying to do.
I table A

Code,CodeType

depending on CodeType it needs info From Different Tables, How can I do this in SELECT
statement?

Select A.Code,A.CodeType,B.NameDesc,C.NameDesc,D.NAmeDesc FROM A
if CodeType=1 then return B.NameDesc FROM B
else if CodeType=2 then return C.NameDesc FROM C
else if CodeType=3 then return D.NameDesc FROM D

Multiple Many To Many Relationships Within A Heirarchical Selection Process
I have run into an interesting problem in having to develop a group of tables with an odd relationship set. I'll give you an idea of what it is:

- I have 4 tables (3 contain classifications and the 4th contains the record's information)

- the information for the end-user is taken from the 4th table based on selections made from the 3 tables

- assume that each of the three tables contains a many-to-many relationship with the other two, i.e. a member of the third class can be a member of multiple second and first classes

- the ideal selection process will go:

select first(if second choices exist then select second, else display results from 4);
select second(if third choices exist then select third, else display results from 4);
select third(dislay results from 4).

I am currently trying to implement this with 3 tables containing a class name and id then 2 linking tables between 1 and 2, and 2 and 3 referencing the class id's to denote the many to many relationships. What I'm not sure on is how many columns to use to categorize the records in the 4th table, or which to refrence them to, because they should be able to be accessed from any one of the three steps. If in fact the best idea is to have a foreign key in the 4th column for each of the other 3 tables then how would a many to many relationship actually be implemented? Also, it seems to me like there might need to be another linking table between 1 and 3 in case something from the third class resides under multiple first classes but only a single second class.

Select Fields On Selection Box(html) By Database Values
I have a form that I have put together to update, and add new fields. I have no problem with connecting to the database, updating fields, and adding new fields. My form autofills with current database information if the user wants to update certain fields.

What is the easiest way to make these fields selected, if the database has the entries already? Thereâ€™s about 30 fields, so I would like to do it dynamically. The fields in the database(in one single variable) are comma delimited...

"value1,value2,value3"

Sql Random Pwd
i read somewhere that it is not safe to use a mysql randomly generated password . I have a table with many records and want to reset everyone's password to a random alpha-numeric password.
What is the best way to do this one ? something using a php random pwd and then update the table ? or there is something better than that ?

Random Row
I am trying to get one random row at a time. Right now I am using this SQL:

Code:

SELECT * FROM `trtrack` ORDER BY RAND( ) LIMIT 0 , 1

But it's really slow, some queries taking up to .5 to 1 second. Is there any other way? I use PHP.

Add A Random Value
in php, this would simply be rand(1,5)
in mysql, I tried

update `table` set column=(column+rand(1,5));

but that was not a correct guess.
how can I add a random value of 1 digit bt x and y?

Random Row Select?
if I have a table with 200 rows in it. I want to select 1 random row. Does anyone know how to do that in a select statement?

Random Keyword
I have a question about what query I should use to achieve the following...

I have a table called 'keywords' that has the following fields

id (auto_increment)
pagename
keyword

There can be multiple keywords for one pagename, but each keyword has it's own row. For example, this could be in the table:

id pagename keyword
11 test-news test
10 test-news news
9 test-news article
What I want to achieve is to select 1 random keyword for each unique pagename. So there can only be 1 keyword selected for a pagename, but it should happen for all unique pagename entries. What query would I use to achieve this?

Random Retrieving
data in myTable

(id) name
(1) Tom
(4) Jane
(8) John
I have data in myTable like the above.

Can I retrieve one of names in name column randomly?

Random Images
is there sombody who can helpme witht he following, i am ''pretty new'' to
php and am looking for a script to show images randomly at a 10/ 15 seconds
interval.

Random Query
I have been tasked with getting daily quotes out of a sql database, there needs to be 3 that are randomly selected. I have not found any documentation in regards to doing this.

How would I qurey a database and pull 3 quotes of the day out randomly. This should even occur when a user refreshes the page, is there a sql function that would do this? or do I need to do it via php, perl, etc...

Random Query
$querystring = $querystring." ORDER BY status, dateposted desc";

How can i make my query that its sorts first by status and then random for date posted.

Random Function?
I would like to know if there is an existing random function I can use to generate a random number so I can concatenate it to a string?

Select At Random?
I have a db that I'm using as a mailing list. In many (but not all) cases, there are several contacts per company. I rarely want to send my mail to more than 1 person at any one company, and I never want to send to more than 3.

I don't have any criteria that I can use to select which contact to select, so I want to do it essentially at random. So I want to contruct a SELECT query that says, "when there are >1 contact at company X, pick 1 (or 2) of them at random. When there is only 1 contact at a company, include that contact/company in the result as well."

I'm sure there's an easy way to do this, but having searched online and read a lot of the 1500-page manual, I haven't figured it out.

Inserting Random #
I have a db name image records. It has 3 columns recno_no, file_name and active_fl.

is there a way to insert 100 random #'s into the database table and its columns for testing?

Random Records
I'm trying to get a random record for use with my website. I've tried figuring out some premade code and it's led me nowhere..

Here's basically the rundown...

I have a database setup by my site's shopping cart system, and in it are 2 tables i need to read from, "specials" and "featured". I want to pull 4 random entries from "specials" and 1 from "featured" to have posted on my site's main page outside of the store.

I know how to connect to and select the database, I even know how to send queries, but the whole selecting a random entry is what's got me at a brick wall.

I'm not used to PHP and MySQL, i'm just now learning after 4 years of using Perl(and never using a database system, but creating my own database-like files).

Random Selection

Basically I have this: (category INT,type INT,item TEXT) I want to be able to select (say) 20 distinct random records with category=x and type=y. How can I do that ?

Basically I have this:

(category INT,type INT,item TEXT)

I want to be able to select (say) 20 distinct random records with category=x and type=y.
How can I do that ?