MYSQL :: Creating A Rank Column For Query

Creating A Column That's The Total Of Others
i'm trying to create a table in which there is a column that has the total of two other columns, for example:

----------------------------------------------------
| item | morning | afternoon | total |
----------------------------------------------------
| oranges | 56 | 20 | 76 |
| apples | 23 | 15 | 38 |
----------------------------------------------------

the values for morning & afternoon for each item would be incremented by the program / user, but the values in the total column should update automatically to be the total of morning & afternoon so that when requested you get the total rather than having to request the separate values then add them up, is an entry that updates itself something that MySQL can do? and if so what is the name / keyword that i should be looking for to find out how to do it?

Creating A Form That Allows A User To Choose What Column
I am trying to create a form that allows a user to choose what column to search through the data. The Database has many columns and the user wants to select - say - all clients that dr. fred diagnosed.

The column is ExamDoctor and they shouldn't have to learn sql to do this. (they're lawyers btw.) But they might need to see who was diagnosed on 2000-02-11, with the column name DateDiagnosed.

Storing Images, Creating A Image Blob Column?
I would like to add a column so that images can be inserted and viewed from a mysql database. I have set up the table as follows:

CREATE TABLE Staff
  (staff_no            CHARACTER(5)           PRIMARY KEY,
   fname               CHARACTER VARYING(20),
   lname               CHARACTER VARYING(20),
   position_           CHARACTER VARYING(20),
   sex                 CHARACTER,
   dob                 DATE,
   salary              DECIMAL(9,2),
   supervisor_staff_no CHARACTER(5),       -- FOREIGN KEY
   branch_no           CHARACTER(4));      -- FOREIGN KEY

I have been having problems in finding the right syntax to create the BLOB if that is what i need.

Creating Column Of Unique Values In Order To Have A Primary Key
I have a flat file that has no columns with unique values.In other words, there is no column available that I can define as the PRIMARY KEY, and thus, cannot incorporate the data into a table.

So, my question is this: How do I modify the table so that I can insert a column of unique values for each record? A column of unique values would be something as simple as a letter followed by the row number of the record (e.g, T1, T2983, etc.).

SQL Rank Problem
right i have a table data_9(domain,title,url,text)

basically i want an sql statement to rank in desc order the group of domains. pretty easy but i also want to rank in desc order the group of urls and merge this with domains.

basically i want the top ranking domains to be displayed and the title, url and text is the top result from the top ranking domain url group.

possible?

Getting Rank From A Table
I have a table with (id, score) and im trying to write a query that will return the rank of a certain id ranked by order of score.eg. highest score would return a rank of 1.

i got this far;

SET @i = 0;
SELECT id, @i:=@i+1 AS rank
FROM scores
ORDER BY score DESC;

which returns a table of ranks which is correct but then i need to select just one of those rows specified by an id. I tried some kind of subquery but it didnt seem to work.

Order Two Tables By Rank
I've got a table called "jingles". Jingles can contain "individual cuts". These individual cuts can be sorted into groups as well, so the order will be:

Jingle => IndividualCutGroup => IndividualCut
,or Jingle => IndividualCut

I store the individual cuts in a table called "individual_cuts", which has both a group_id (which is used when the cut belongs to a group) and a jingles_id (which is used when the cut belongs directly to a jingle).
Both tables contain a "rank" field by which I'd like to order.

How can I order by rank when the data is spread over two tables? Also, individual cuts containing a group_id should be ordered by rank within that particular group.

Selecting Rank Over Time
I have 4 colums in my table.. they are id(int), usrid (int), datestamp (date) and credits(float).

here's what i'd like to do.. select the numeric rank of a given userid over the past 30 days based on the most credits... ideally i'd like it all to be in one query but that may not be possible.. any ideas are welcome here, i'm stumped on this one.. so far all i've been able to do is select all the entries from the last 30 days..

SELECT *
FROM daycredit
WHERE TO_DAYS( NOW( ) ) - TO_DAYS( datestamp ) <= 30
and usrid = $usrid LIMIT 0, 30

Ranking System: Retrieving Results Around Your Rank
I'm building a ranking system for a game. I'm trying to determine the best way to select a players rank, and then display the records around that players ranking.

For instance:

Username | Rank
steve | 6000
bob | 5000
jane | 4000
chris | 3000
brian | 2000
tim | 1000
molly | 0

Assuming chris is the current player, I want to select his rank, and then run a query that returns the two players ranked higher than him, and the two players ranked lower than him. So the query would return the following:

bob | 5000
jane | 4000
chris | 3000 //I'd rather leave his own rank out.. but it's not bad like this.
brian | 2000
tim | 1000

What I can't figure out how to do, is determine the row that is specific to Chris's rank. I would need to define a starting point (-2 rows from Chris), and an end point (+2 rows from Chris), order by and limit the results... what I can't figure out though, is how to get the specific row number to start from.

For instance, lets say Chris is row 583 of my database... how do I retrieve that number?

Retrieving Rank Of Player After Order By DESC
I'm new to MySQL but I do have some knowledge of SQL. I need some advice regarding writing retrieving statement.

My objective: I want to retrieve the rank of a player according to his score. All ranks are calculated base on their scores, where higher rank means lower score. Code:

How To Get Sort Index (Rank) Without Returning All Prior Records?
I want to get the sorted rank of a record without returning all prior records and counting them. Is it possible to get the order index given a unique key value, or even using the unique value of a field within any SELECT statement?

For example:
100,000 players in a network game.
Player ID is unqiue and scores are indexed.
Specific player asks for his rank among the scores of the 100,000 players.
Return rank of the record without returning all records up to player's record.
Returning player's specific record with the rank would be a plus.

Creating An A To Z List From Query
I am creating an a to z list - basically a count of all results that
start with the letter "A", "B", "C" .... and so on.

I am pretty poor at SQL so I am sure some brains out there can do
better than I have here. What I have is working, I just want to make
sure that it is optomized.

So let's assume I have some query "$query" that I want to run and get
an A..Z list based on column "$column".

Let's further assume that '$query" produces the following results, and
that $column is equal to "last_name".

last_name
---------------
Anderson
Bitmore
brown
Bogus

My AZlist query would look like this:

select * from
(SELECT count(alist.$column) as a from ($query) as alist where
alist.$column like 'a%' or alist.$column like 'A%' ) as a_result,
(SELECT count(blist.$column) as b from ($query) as blist where
blist.$column like 'b%' or blist.$column like 'B%' ) as b_result,
....
(SELECT count(zlist.$column) as z from ($query) as zlist where
zlist.$column like 'z%' or zlist.$column like 'Z%' ) as z_result;

And this retuns the following result:
a | b |...| z
--------------
1 | 3 |...| 0

Meaning that $query has 1 result where the first letter in $column is
"A" or "a", 3 results where the first letter is "B" or "b" and 0
results where the first letter is "Z" or "z".

What I am afraid of here is that "$query" is being executed 26 times
(once for each letter of the alphabet) . Is there a way to refine
this, or is MySQL (4.x and 5.x) smart enough to optomize this on its
own?

Creating A Query From A Form
I'm struggling building a SQL query from the output of a form, i.e. the user inputs into a form which in turn decides the query.

I have never done this before and was just wondering if anyone had any links tutorials of something like this!? I have searched but haven't found anything too useful yet.

Basically all I want to do is for the user to pick from a drop down menu how they want a leaderboard displayed, i.e. top 50 results, bottom 50, 50 to 100 results, etc.

Do I just tie a complete SQL statement with the corresponding LIMIT info inside to a variable. The variable being the value of the chosen item in the drop down menu.

Pseudocode as follows:

Creating Complex Query
its a booking form. user selects start date, finish date, and room type.
on my table i have roomInfo(roomNo, type, NoOfBeds);
occupancy(roomNo,DateStart,DateFinish,GuestNumber);
i am trying to do something like:
select roomNo from roomInfo where roomNO = single and roomNO not IN ...
cant do subselect its mysql 4.x version... would i have to do an outerjoin/innerjoin?

Creating An Optimal Query
What I am doing at present is displaying a list of topics such as on a forum main page. That data is in a table say "my_topics" for example
The list I am displaying also references data in another table say "my_images". Now each item in my_topics could reference zero or more items from my_images and they would need to be displayed alongside that topic. So each item in my_images has an id which is an index into the my_topics table.
At present I am dumping the entire my_images table into an array so I can access it without having to do multiple sql queries. I can't use "left join" as far as I know because the relationship between my_topics and my_images is one-to-many rather than one-to-one.Obvious downside is that as my_images table gets larger - dumping it to an array increases the memory usage of the script which eventually leads to it not working.

Creating A Query Based On Dates
I am trying to write a query (in PHP) which selects from a database all of the items which are in the future. My query is as follows

SELECT * FROM news WHERE ((news.date)>$today ORDER BY date

where news is my database, news.date is the the field which holds the date for the item and $today will be replaced my current date. At the moment it seems to display all values, which suggest its not functioning properly.

Creating A Query Based On Dates
I am trying to write a query (in PHP) which selects from a database all of
the items which are in the future. My query is as follows

SELECT * FROM news WHERE ((news.date)>$today ORDER BY date

where news is my database, news.date is the the field which holds the date
for the item and $today will be replaced my current date.
At the moment it seems to display all values, which suggest its not
functioning properly.

Creating Non-existent Rows In Query With Join
I want to make report using PivotTable/CrossTab and I used an application to create it.
The problem is, I want to so show NULL value to the temp table that will be the source of my report.

I'm using this query:

MyISAM Vs InnoDB While Creating A Table Using Select Query
I have created a table using the select query.

****
for eg: create table table_name1 as select * from table_name2
****

now the created table is by default MyISAM format, what changes should i make to the query so that the created table is InnoDB.

Column Name Query
suppose that we have a dB has 200 tables .
how can i find out which ones have column name is username ?
what is requirement query ?

Getting Sum Of Column Within A Joint Query
Wondering if this is at all possible - For now, I am running two query.

(1) First query searches for all records in table A
(2) Using a while loop to display data grid
(3) Within loop, running a 2nd query against table B to get the SUM of two columns ....

Query Result As Column Name?
I now have made a view with results of several years for all the people in the database.

I can query the view to get the three winners, sorted decending on Winstperc. The result looks like this:

Seizoen    Naam        Winstperc
2006/07    Jim        1,0909
2006/07    Bill        1,0896
2006/07    William    1,0879

And that's exactly what I want to know but I need it like this:

Seizoen    Nr1        Nr2        Nr3
2006/07    Jim        Bill        William

Looking through the threads I got some nice ideas but...
I tried it with IF and with Case but never succeeded.

So, I think I'm overlooking something. Please show me the way?

Query A Column Into Fields In A Row
I'd like to query a table and pull a column from (n) rows, and place that in a new table as (n) fields in a single row.

Is this possible?

like this ....

Query By Column Comparison
I'm trying to pull a row by comparing that the numerical value of one column is greater than the numerical value of other columns. The manual is quite confusing as to how to phrase such a thing, and I haven't done much more than simple queries up until now.
MySQL version: 4.0.22

What I started with that doesn't work:

select * from someTable where col1 > col2 and col1 > col3 and col1 > col4 order by someThing limit 1

I'm basically trying to grab one row where the value of one column is greater than the values of the remaining similar columns.

Query Performance On Column Conditions
I have a question regarding MySQL performance on column conditions. How MySQL reacts where you add a condition to the same column?

For example:

SELECT * FROM users WHERE status<>4
and the same query with extended column check
SELECT * FROM users WHERE status<>4 AND status<>5 ...

I am specially interested in situation where the conditions checks a column value and whether the data is specified:

SELECT * FROM users WHERE status<>4 OR status IS NULL

What's with the MySQL's performance in such cases? Is the same column (status in the above example) checked twice or the optimizer takes care of it in some special way?

Display Result From Query In More Then 1 Column
I want to make a query to a database, and display it on my php page either through php code or with 2 queries to my mysql database.

This is how i want it displayed:

Data 1 Data 2
Data 3 Data 4
Data 5 Data 6
Data 7 Data 8
... ....
Data X Data X

This is my current code which i use and get it displayed in 1 row:

How To Query For A Column With White Spaces Only?
A column with varchar type and NULL allowed. How do I write a sql so that I can retrieve the records with unknown number of white spaces only?

Running Query For Column Names
Is there a way to run a query just to see what the columns are in a database?

Column Names On Top Of The Query Output
Is it possible to get the columnnames on the first row of a query output? Is there a "flag" I have to add?

Distinct Column In Multi-Table Query
I want one article from each 'sub topic'. Each sub topic is given a 'sub_id'.

PHP

$garticle = mysql_query("SELECT DISTINCT(feeds.sub_id),
articles.title, articles.feed_id, articles.abstract,
articles.link, articles.date, site.articles,
$formula AS importance
FROM articles,feeds,site
WHERE articles.date > DATE_SUB( now() , INTERVAL 1 day )
AND articles.feed_id=feeds.feed_id
AND feeds.site_id = site.site_id
ORDER BY articles.article_id DESC LIMIT 16", $connection)
or die(mysql_error());

The article has a 'feedIid', this JOINS the feeds table. The feeds have a 'sub_id'. I only want the 16 most recent articles from DISTINCT 'sub_id's

A Query To Select A Column When A Percentage Of Values Non Zero?
I wounder whether some of the experts out there might be able to help
me with a problem I'm having. I do not know whether this is possible or
not...

I have a large table of stock price data which is straight-forward
enought. I can select prices based on a ticker and date ranges.
However, what I'd like to do is to select prices only when, say 75% of
them are non-zero (with the goal of eliminating new/suspended/delisted
stocks).

Of course I could just select where price > 0, but then I might get
only a few rows where this is the case. What I would like to do is
always get the full date range of prices, but only if >75% are there.

How Do I Test If A Value Exists In A Table Column With A Query?
I know this is probably something that should be obvious or easy, but I can't figure out how to test whether a value exists or not.

For example, in table "bookgroup" in the "meetingdate" column (which contains dates for meetings), how could I test whether the date "20040307" exists? I don't want to return a value in the same row... I simply want to return a boolean TRUE or FALSE value... like 1 if it does exist, and 0 if it doesn't. Can I do this without creating another column specifically for this purpose? Is there a query which can do this?

All I have so far is

FROM `bookgroup`
WHERE meetingdate='20040307'

but I don't know where to go from there.

Unknown Column Error With Left Join Query
I can't get my left join operation working whenever my on clause includes a comparison to another table's column. I keep getting errors like this:
#1054 - Unknown column 'pr.risk' in 'on clause'

The query I am trying to execute is shown below. All was working well until I put that top join in. And yes there are risk and issue columns in the pr table....

Select Query On Latin1 Or Utf8 Column: Which Is Faster?
Assume you have two varchar (or Text) columns named L and U which are
identical except that the charset for L is latin1 and the charset for
U is utf8. All the records in L and U are identical in terms of
content, consisting of only 7 bit ASCII characters. Both columns have
indexes of the same type (e.g. assume Unique indexes if you want).

Here's my question: Will the fact that column U has a utf8 charset
make select queries run slower on that column? For example, will the
query

Select * from table where U='blahblah'

run slower than the query

Select * from table where L='blahblah'

??

Significantly slower?

I'm thinking that a query on the latin1 column would go faster since
the program knows upfront that one byte equals one character, and
vice-versa; whereas in the same query on a utf8 column the program has
a lot more "overhead" because it has to constantly be determining how
many bytes represent a character. Since queries on string columns are
case insensitive, the program can't just do a byte-for-byte
comparision; rather, it has to compare *characters*, and sometimes
convert a character from upper to lower case, or vice versa, in order
to do the case-insensitive comparison.

The actual column in question is going to store URLs, so it should
only need to hold 7 bit ascii characters (in theory at least). So, in
terms of content, it shouldn't matter whether I make the column latin1
or utf8. But in terms of query speed....on, say, a few million
records...??

I would like to do everything in utf8 (web pages, forms, mysql
database columns, etc.). But since that one column might be heavily
queried, maybe I should make an exception and do it in latin1?? I wish
the mysql docs would speak to these issues....

Selecting Records, Then Summing Parts Of A Column, Then Sorting By That Column...
Iâ€™m keeping track of baseball stats, and each row represents one line of stats (from a box score) for one player of a single game. Because of this, a single player may have multiple rows in the table. I want to cumulate each playerâ€™s stats (so theyâ€™ll be one row per player) and display as output, which isnâ€™t a problem. Then I want sort by a certain stat, but by now Iâ€™ve already looped through the table, so I canâ€™t sort using a mySQL query at this point. I tried first putting values into an array in a previous project, but that became extremely complicated. Whatâ€™s the best way to approach this?

Update Part Of Column Into Another Column
I'm looking for a way to update a SQL column with a portion of info from another column in the same table.

example of a sql command
--------------------------
UPDATE table1
SET table1.columnname1 = table1.columnname2
FROM table
WHERE blah blah blah

Here's the thing... I only need a portion of the data found in the source column. I'm not sure how I would do this then.

for example, the database has countries and states combined into one column like this 'US-DC', 'US-CA', US-FL', etc. I want to separate these into two columns, a country column and a state column.... and I dont want to go though all the results and do this line by line. How would I write the SQL command so that it puts just the country in the country column, and puts just the state in the state column, and it omits the dash all together.

any ideas?

Differentiate Between Column Alias And Other Column With Same Name
Sample SQL:
SELECT
a,
(SELECT a + 3) AS b,
(SELECT y FROM t2 WHERE b = b)
FROM t1

1. SELECT a + 3 will calculate a column with alias b
2. t2 has a column named b

Question: How can I differentiate between column alias b and t2.b. MySql treats alias b as t2.b in statement (SELECT y FROM t2 WHERE b = b)

Creating A Db With Php
My host will not allow SSH access, or do any modifications to the MySQL database. He set it up, gave me a login and pass, and thats it. Fine. But I am trying to create a table, and here is what I have for code:

Creating Id
Have a database that records data taken in the field and was using an
"auto_incremented" integer. I now want to distribute the database to others
so they can record data then centralise all the data on one database so
people can share records.

However will an "auto_incremented" integer on it's own won't suffice? I
suppose it might if that field didn't have to be unique but is that best
practice? I was thinking along the lines of creating a unique field by
combining for 3-4 fields such as time, date, member and "auto_incremented"
integer.

What is this technique? I am sure there's a lot of stuff already on the 'net
explaining how to achieve this but I am a bit stuck as I don't know what
search terms to use.

Creating A DB
I use a MySQL DB to store Japanese characters. I didn't specify any character settings or any kind of language settings when creating the DB and tried with the defaults.
I use MySQL 3.23.56 Now I have a problem of viewing the Japanese characters stored in the DB. The charcters get stored in unidentified format but when query from the DB and display in the browser it gives the correct values from DB.Can anybody suggest what is the problem and how to solve it. May be I have to give some language settings when creating the database and if so can somebody please reply me with sample script explaining how to do.

Creating A New Db
Im trying to create a new db in phpmyadmin, after already creating the db in cpanel mysql, but im not sure how to create a login, its easy in the dos shell on the pc, but im now using a mac.

Creating A Db From A Script
I have a PHP script that will create the db for me, but I'm having trouble creating all of the tables.
I have a .sql dump from phpmyadmin that I would like to use, but somebody told me that I actually have to break down each block into a separate query.
$query1 = "CREATE TABLE `admin` (
`admin_id` tinyint(3) unsigned NOT NULL auto_increment,
`username` varchar(16) NOT NULL default ''," blah blah blah
$query2 = "INSERT INTO `admin` VALUES (1, 'admin',"; blah blah blah
Instead of doing that - which will be a real pain if I happen to add more tables, columns, etc. - can I use LOAD DATA INFILE instead?

The only examples I've seen are for tables, not complete databases.

Creating A Queue
I am working on a queuing system in which I insert records into a database table that begin with a status='U' for Unprocessed, status='P' for Processing and status='C' for Completed.

I am currently doing a SELECT * FROM queue WHERE status='U' LIMIT 1;.

I immediately run an update on that record to UPDATE queue SET status='P' WHERE id=ཆ'

This seems to work great if I only have one process pulling items from the queue. However, if I have multiple processes running at the same time, there is the possibility of pulling the same record twice before the UPDATE occurs. Is there a way to prevent this other than by locking the table?

Creating Index's
i'm wondering what's the best method to use?

have an index of something lile

PHP

KEY (`pieceid`,`userid`,`rating`)

or to seperate them ala

PHP

KEY (`pieceid`)
KEY (`userid`)
KEY (`rating`)

Creating A Table
I made a following code for creating a table....

Creating Tables
Can someone post a link with detailed info on how to go about creating individual tables in any certain db? Also I need to be able to name them. In the least that is and if you could please tell me how to go about this if your not in a big hurry. Something new I have never tried yet.

On a side note aside from the above required info here is my situation. I installed a cms with a plug in. However in doing so the plug in gives an error saying it is missing certain table names. I figure if I can create these manually it should fix the problem. Thanks so much for your time and yes ive already done some research. Nada!

Creating Table
Is there a way to create a table with the results of a query? i.e.

I want to create a table of the with the users who registered on my site on January, Feb and so on ...

All this records are stored in a single table: users, and there is a field called date_join, so i want to create a table with only the users who registered on january and name it users_jan for example ..

Creating Report
I have a table "officers" table two with 4 fields all with variable char which will end up just being names. However, I need to create a report that counts each the occurance of each name and ouputs the name with the count next to it. The names in table "officers" are concatinated from a first name field and last name field in a separate table "persons";

I need some help figuring out how to write this using PHP.