MYSQL :: Ordering Multiple Fields

Ordering Certain Fields Last
I have a search query:
select * from mgi WHERE activate='1' && mgi_name Like '%".$search."%' ORDER BY mgi_name ASC
This list all the business names for a search.
I have another column in the table called "placement"
placement has either a Yes or No value.
How can I change this query so that all the rows with placement "No" is listed last

Problem Ordering By Relevance (from Multiple Tables)
this is my first post here at SitePoint

Found a few useful threads, which seem to have got me in what I feel is the right direction but still having some troubles with ordering a search query by relevance.

Here is what I have got so far:

SELECT DISTINCT p.paper_id,
(
CASE WHEN pa.author_id = 4
THEN 1
ELSE 0
END
) + (
CASE WHEN pa.author_id = 14
THEN 1
ELSE 0
END
) as relevance
FROM papers p, paper_authors pa
WHERE pa.paper_id = p.paper_id
AND pa.author_id IN (4, 14)
ORDER BY relevance DESC
I have 3 tables. Papers, Authors and Paper_Authors.

Papers includes all the paper details, Authors all the author details and Paper_Authors is a link between the 2. Having Paper_id and Author_id.
A many to one relation....

I'm trying to search for all the papers linked with the author_id as 4 or 14. Then order this by relevance. i.e. if a paper is ordered by both 4 and 14 then it will be of higher relevance than those ordered by just one or the other.

Hope this makes sense.

Anyone have a way of doing this or an idea of how I can improve my table structure?

Multiple Fields
i have a query regarding mysql like clause.can we use
select name like 'john%' or 'bill%' from nametable ;
Is the above syntax correct or is it wrong.If wrong please give me the correct syntax.
what i am trying to do is that i am trying to select either john or bill with sum last name in all the rows.

Search With Multiple Fields
I have a form with essentially 5 different selects, let's call them country, state, subject area, tag, and age range. What I want to do is find all of the users who are the closest match to the search query (once I know how to do this, I can modify the query to find their names, email addresses etc...)

In the database, users have a country_id (not name!), state_id in one table, and subject area id, tag id, and age range id in another table (all in the same column as multiple rows for the same user).

I want to do a database search and match all of the users who meet at least 1 of the criteria, and sort them in order of closest match (all 5 criterion) to least match. Assuming I consider the order of preference to be subject area, tag, age range, country, and then state, how do I do this?

To make the problem even slightly more difficult, the form doesn't return the actual country name, it returns the row the country shows up in the country table (same for the other fields) so I'm currently doing a look up for each field to figure out which country/state/etc... I'm actually dealing with.

I don't see how I can do a FULLTEXT search for this information because it is all in different columns.

Update Multiple Fields
I need to update some 900,000 records, each different. I will be pasting the code into the sql window of PHPMyAdmin at my web site. The individual updates look like this:

UPDATE table SET Field1='sample', Field2='sample1' WHERE UniqueID=12345

UPDATE table SET Field1='sample2', Field2='sample3', Field3='yes' WHERE UniqueID=2021432

How can I string together a bunch of these where the SET field values are always different and there is one each for 900,000 different ID's. Right now, I have these in 30 text files but I can't seem to get the syntax correct.

Count Of Multiple Fields
I have a database that stores some stats for a hockey league in the following manner:

Field names are GP, G, A

Whenever a player Plays a game, his ID is added to the column GP while other columns go to 0
If a player scores a goal, his ID goes under G, the guy who assisted goes under A

What I want is a query that can give me the count for each ID that shows up in GP for GP, G and A

Is there an easy way to do this with just one statement? Right now, it's really messy since I have to loop through each player ID in GP and make individual queries to count GP, G and A because I can't figure out an easier way and I don't want to rebuild the DB

Update Multiple Fields With 'OR'
Is there a better way to do this (more efficient).

I have up to 200 items to update per query, the id's are not sequential. Using up 200 (well 199) OR's in a single query doesnt strike me as being the best way to do this. All the fields get set to the same value.

# Example
UPDATE table SET value=value WHERE id=11 or id=23 or id=31 or id=74 or id=56 or id=96;
# Up to 200 fields

Or am I stuck with this query? (which works).

Multiple Distinct Fields
Okay SQL gurus, I have a table laid out as so:
RowID | UserID | UserType | pID

Now, how can I write a SQL query that would select every distinct UserID and pID combination.

So if the data in the table were:

1 jjdoe internal 19
2 jjdoe internal 6
3 jjdoe internal 6

It would only return rows 1 and 2.

Displaying Multiple Fields
I am sure amongst this community that this question is rather straight forward.
I have a database where a table is added that contains the user login, password and other user information.
What I wish to do now is for each user I want to be able to add a product description, old price and new price.
So when this particular user logs in, they will see a list of products on one column and then the old price in another column and new price in the third column.
The prices obviously have to be matched to the same product.
Eg:
productA new_price1 old_price2
productB new_priceX old_priceY

How would one do this?

Comparing Multiple ID Fields
I have an advert table which contains three category ID fields (catid, catid2, catid3). These catid fields match up with the catid field in the category table. I want to run a query that returns any row that contains any mention of a specific category.

SELECT * FROM advert, category
WHERE advert.catid = category.catid
AND advert.catid = '3'

The above query works fine for the first advert.catid field, but once I introduce...:

OR advert.catid2 = '3'

... the results start getting strange. The same row is returned 9 times.

GROUP BY Multiple Fields
Can somebody show me how to properly use GROUP BY if I'm using multiple fields.
For instance:

I have a table that has a month field and a year field. I want to group all entries by month and year. How do I prevent grouping a record from January 2007 with a record from January 2008?

Indexes With Multiple Fields
I have seen some tables that have indexes which contain multiple table fields. What is the purpose of having an index with multiple fields instead of a seperate index for each?

Duplicates On Multiple Fields
The table has fields:
home_id, owner, street_number, direction_one, street, street_type, city

I have a problem where the same address information was entered in but with different owners. How do I find all the duplicates? I tried this query:
SELECT home_id, street_number, street, city
FROM home_data
GROUP BY street
HAVING ( COUNT(street) > 1 )

but that only works for duplicates on street, at the very least it should check for dups on street_number and street and city. When I ran the above query it took forever. My table has over a million records in it. Basically my computer froze up but kept running and I kept getting virtual memory errors.

How To Update All Fields With A Multiple Of The Field Value?
Is there a MySQL command to replace all the values of a field with the field's original value that's had a mathematical formula applied to it? Basically, I want to divide the field's original value in half, then add 40% more. The formula would be y=x/2+.2x where y is the new value and x is the original value.

I know I could write a PHP script that would select each field's value, apply the formula, then update the new value, but it'd be cool if there's a MySQL built-in function to do this.

SELECT DISTINCT With Multiple Fields
I'm kind of stumped on a query I'm trying to build. basically I have a forum table like this:

Post_ID | Thread_ID | Post_EntryDate | etc.....
90 | 22 | ....
89 | 21 | ....
...
...
...
85 | 1 | ....
84 | 1 | ....
83 | 10 |....

and so on. I would like to get the last 20 threads with most recent posts filtering for Thread_ID duplicates of course. so far I have this:

SQLtemp = "SELECT DISTINCT Thread_ID FROM Posts ORDER BY Post_ID DESC LIMIT 0,20"

this succesfully returs a list of unique Thread_IDs of the last 20 threads with most recent posts. However, I need to get the Post_IDs and other fields but when I throw Post_ID in the SELECT statement all I get is duplicate values for Thread_ID

How Change Multiple Mysql Fields?
I have a table with 2,733 records.

I need to change a field (published) on all records to show ....

Multiple Search Fields And Subqueries?
I'm trying to construct a query in PHP that will be able to handle multiple search fields. The data is in multiple tables.

COMPANY
company_id
name
address

SERVICE TYPE
company_id
service

OFFICE LOCATIONS
company_id
city_name

My question is how do I construct a query that will allow there to be these three search fields (company name, service type, location).

I know that subqueries are probably needed, but I'm a newbie and still not quite clear how to get these to work properly.

The queries I've been able to construct return zero results unless all fields are filled in, or they return multiple listings of the company name since the company may have more than one office location. I don't want the user to be required to fill in all fields to get meaningful results.

Count Fields Over Multiple Rows
I have a database like this

id, field1,field2,field3,field4,field5

Database contains 100 rows, some rows have no fields filled, some
1field , some 2 fields etc.

How would i count the number of fields filled in total?

So the outcome is (number of fields filled in row1)+(number of fields
filled in row2)+(number of fields filled in
row3)....................+(number of fields filled in row100)

Sort By Multiple Fields In Different Directions?
I'm wondering if I can sort by multiple categories in different directions. I tried this and it doesn't work:

SELECT * FROM table ORDER BY field1 DESC ORDER BY field2 ASC LIMIT 0,10

JOINing From Multiple Fields To One Table
I have two tables in a MySQL database. One has the publication data (title, ISBN etc), and the other has the data on the authors that wrote it (first name, second name, etc.)

I am trying to produce a single recordset that will show the authors' names (first name + a space + second name). The staff.staffID corresponds to publications.author1, publications.author2, etc.

The problem I have is that there are potentially 5 authors for each publication record, so when I try to LEFT JOIN them all them to the staff table, I get a '1066 not unique table / alias: staff' error - I can see why this is happening (becuase of the 5 left joins onto the same table) but how should I change the code to get the results that I need? Code:

Multiple Fields Linking To One Foreign Key
I have a table that is going to track project info, time, date, people working on the project, materials, etc.

My question is what would be the best way to have multiple fields for the people working on the project, for example this is my current layout (don't laugh too hard):

tbl_washLog (washID*, time, date, crewID**, crewID2**, crewID3**, tailID, paintID)

The crew ID fields need to be foreign keys back to the crewID field in the crew table which is:

tbl_crew (crewID*, firstName, lastName)

(* = Primary Key, ** = Foreign Key)

Is it possible to have crewID, crewID2 and crewID3 all be foreign keys of tbl_crew.crewID? If not, what would be the best way to go about doing this?

Select Fields Accros Multiple Dbs
I am trying to use the SELECT DISTINCT statement to create one list of email address I have which are in two different databases, I have tried the below syntax, but does not seem to work...can anyone please tell me where I am going wrong!

SELECT DISTINCT m2e_db.email, events_bookings.email FROM m2e_db, events_bookings ----Ment2Excel (M2E)----
--LEARN BY EXAMPLE--

Query To Search Multiple Fields
I have a table with over 50 columns that contain yes/no values. I want to know if there is a way to write a query to get all fields that have a value of "no".

Extract Date Into Multiple Form Fields
I'm storing standard datetime values in a MySQL table (i.e. 1999-12-31 23:59:59). A query such as:

"SELECT thedate FROM tablename"

returns the expected 1999-12-31 23:59:59

How can I extract the indivdual elements into separate PHP variables like:

theyear = 1999
themonth = 12
theday = 31
thehour = 23
theminute = 59
thesecond = 59

I've searched through the forums and thought DATE_FORMAT would be the ticket, but so far haven't been able to find anything that addresses this particular issue.

Updating Multiple Rows With Same Fields (in One Query?)
I have 2 tables here

table categories

+--------------------------------------- +
| cat_id | cat_name | cat_total_articles |
+----------------------------------------+
| 1 | PHP | 23 |
+----------------------------------------+
| 2 | MySQL | 17 |
+----------------------------------------+
table articles

+---------------------------- +
| article_id | article_cat_id |
+-----------------------------+
| 1 | 1 |
+-----------------------------+
| 2 | 2 |
+-----------------------------+
Now I've changed an article's category from cat1 to cat2, and I need to update cat_total_articles of both cat1 (minus 1) and cat2 (plus 1) in category table.

Is it possible to combine the following queries into one statement?

PHP

mysql_query("UPDATE categories
SET cat_total_articles = cat_total_articles + 1
WHERE cat_id = 2");

PHP

mysql_query("UPDATE categories
SET cat_total_articles = cat_total_articles - 1
WHERE cat_id = 1");

Is It Possible To Sum The Values Of Multiple Fields On A Single SQL Record?
I have fields for Goals and Assists.

I want to take those 2 fields and sum them to make a 3rd field PTS, then send them to variable in my PHP code to be printed to the screen.

My statement is this:

SELECT player_stats.G, player_stats.A, SUM (player_stats.G + player_stats.A) PTS
FROM player_stats
WHERE player_stats.PID =10
AND player_stats.season =1

The statement breaks with the bolded part.

Joining Multiple Fields To A Single Table?
I have 1 table with 2 columns, 'id' and 'name':

tbl_names:
idname
------
1Bob
2Jeff
3Fred
4Joe
5Bill

I then have another table which contains several fields which hold id's
from the above table:

tbl_output:
idperson1person2person3
-----------------------
1231
2543

I need a query that will return the names for the specified id from
tbl_output.
If I have just one 'person' field in tbl_output I would do it with an
inner join like this:

SELECT name from tbl_names
INNER JOIN tbl_names on tbl_names.id = tbl_output.person
WHERE tbl_output.id = ?

but I can't figure it out when theres multiple fields to be joined from
the same table...e.g I want to specify tbl_output.id = 1, and it give me:

person1person2person3
---------------------
JeffFredBob

Sorting By Same-data Fields From Multiple Tables *without* UNION
Is there anyway I can sort by two columns (which are the same datatype) from separate tables (whether selected via Table1, Table2... or by LEFT JOIN) without using UNION?

Selecting Language Depended Fields Through Multiple Table References
For a long time i think i need your instructions dear people on sitepoint. I'm making a sql query for getting user info, but i dont know how to handle this situation:

SELECT
user.name, user.firstname, user.lastname, ___ as user.city, ___ as user.province
FROM
user, languagefield, city, province
WHERE
user.id = %i
AND languagefield.language = %i
AND city.id = user.city
#tricky part
AND city.name = languagefield.id
AND city.province = province.id
AND province.name = languagefield.id
So i'm trying to get users first and last names plus city and province names from language tables. Is it possible with one query? How would you make SELECT and WHERE clauses?

Im using this db tbl scheme:

table : city
id (int4) primary key
name (int6) ref languagefield.id
province (int4) ref province.id

table : province
id (int4) primary key
name (int6) ref languagefield.id

table : user
id (int4) primary key
firstname (varchar32)
lastname (varchar32)
city (int4) ref city.id

table : language
id (int3) primary key
char2name (varchar2)
name (int6) ref languagefield.id

table : languagefield
id (int6)
language (int3) ref language.id
value (varchar255)

id + language unique

*****

I'm also thinking and planning to set more current language depended content to my application, so this is very important part of my design, 'cause it could be reused when dealing with other tables and fields.

Fields In One Table Overwrite Fields In Another Via JOIN
I'm writing a simple web game which uses mysql to store data. The prototype works ok so far, but now I'm refactoring it to support multiple players. This means coming up with a way to store the player's current world state.

I'm thinking of doing this by having a table that defines the initial world state, and then a table containing the 'state' of anything different.

Example:
The 'objects' table looks like this:
object_id, name, room
1, Hammer, 1
2, Mirror, 1
3, Spade, 2

So, at the start, room 1 contains a hammer and a mirror, and room 2 contains a spade.
Now, if player 12 picks up the hammer then drops it in room 2, I store this override in the 'state' table

player_id, object_id, room
12, 1, 2

So. I'm trying to see if there's any kind of join syntax that would return all the objects in a room for a given player's 'session'. Obviously it could be done with temporary tables, or just comparison outside of SQL, but I keep thinking this might be possible *somehow*

Ordering
I've got a few 'course' records with titles and dates. The result set should be ordered like this:

title4|29.10.2007
title2|03.11.2007
title2|07.11.2007
title3|04.11.2007
title1|20.12.2008

So, the records should be grouped by title and *within* the same group they should be ordered descending by date (title2 in example). However, overall the first record should be the one with the closest date (i.e. date descending).

The fact that title3 comes third, is obvious then (I think) because its date is "higher" than the closest title2 record.

Ordering A NOW() Column?
I have built a web application that allows users to input data into the database, and each entry is marked with the date and time in its seperate DB column using the NOW() mysql function inside the INSERT query.

But now that it has passed new years... I've noticed that all new entires in 2007 are being placed at the bottom of the sort order when SELECT'ing all records from the DB and using ORDER BY now_col (which is the NOW() column). Why is this happening? Is NOW() not a good function to use to record the date and time of a DB record insert? What can I do to fix this or is there another solution?

Add-In: The column type is DATETIME

Selecting And Ordering By AVG?

each image has it's own entry in my "images" table
ie. images.images_id, images.title, images.description, images.filepath

every time a user votes on an image it creates an entry into the "vote" table
ie. vote.vote_id, vote.images_id, vote.vote_given (the rating given in 1-10 format)

What i'd like to do at the end of the voting session is grab the images from the "images" table, in order of the average vote given to them from the "vote" table.
Thanks in advance!

Changing The Ordering
Sadly, this isn't one that can be answered simply by changing ORDER BY field DESC to ORDER BY field ASC. What I need to do is to be able to display a list of items in an arbitrary order that can be changed, e.g.,
A
B
C
D
might need to change to
C
B
D
A
I'm thinking that the best way to approach this would be to add another column sort_order that contains integer values which could then be used in an ORDER BY clause on my SELECT statements. Anyone else have any better ideas? I've done it this way before and it does work, I'm just wonder if there's some utterly brilliant solution out there that doesn't require additional metadata.

Fulltext Ordering
SELECT * FROM article WHERE MATCH(name) AGAINST('$myvar' IN BOOLEAN MODE) ORDER BY datestamp DESC LIMIT 1
The problem I am having is that it's not ordering the results by datestamp whatsoever, it's still ordering them by the fulltext score.

Ordering Both ASC And DESC
What would be the SQL to order column 'a' as ASC but then column 'b' as DESC?

Example:

Ordering Of Data.
In the following query

"SELECT * FROM table_name WHERE account_num>100 AND account_num<500 AND sales_num>1000 AND sales_num<5000"

What order does the query return the records in?

Also is there a way to set it to return all account_num that are between 100 and 500 for sales_num 1000 and then return all account_num that are between 100 and 500 for sales_num 1001 and so forth all the way up to sales_num 4999?

Ordering Values
How do i order numbers retrieved by there value, such as: 1,2,3,4,5...12,13...22 so its like that instead of 1,11,12,13,14,2,21,22,23,24,31,4,5,6...

Grouping And Ordering
SELECT q.q_id, q.user_id, IF (e.firstname IS NULL , q.name, e.firstname) AS name, t.town, e.type, e.trading_name, e.posts
FROM qa_uk q
  LEFT JOIN ayn_experts e ON e.unique_id = q.user_id AND e.active = 1
  LEFT JOIN ayn_towns t ON t.id = q.town_id
  WHERE q.active =1 AND t.town IS NOT NULL
  GROUP BY q.user_id, q.name, q.town_id
  ORDER  BY q.datetime  DESC

This query runs, however the field that comes back from the aggregate functions for the q fields is the first field in the database matching all others, how do i make it the last? --BB

Ordering By With More Specifics
Order by subname will order Chapter 1, Chapter 10, then Chapter 2. Is there a way to order them in a more appropriate fashion such as Chapter 1, Chapter 2, then Chapter 10? ORDER BY subname;

Complex Ordering
I would like to order query results by a numeric column containing positive numbers and zeros at a descending order, except that results with a zero 0 should appear last (e.g.: 2, 10, 15, 16, 0).

Can I do that in a single query, or do I need to use to successive queries or order my results in the programs that receives them before displaying them?

Ordering By Value List?
I want to use a list of values for a field as the basis for a query (so far, so good - that's "WHERE col IN (comma-delimited list)"), but I want to get the results in the same order as the list of values. Is there a way?

Ordering By Company Name
I have the following PHP/MySQL code I found on a website for sorting company names alphabetically and those in the past 30 days:

$result = mysql_query("SELECT quotes_needed.company FROM quotes, quotes_needed WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= quotes.date_initiated ORDER BY SUBSTRING_INDEX(quotes_needed.company, ' ', 1) ASC", $sql);

while($row = mysql_fetch_assoc($result))
echo $row['company']."<br />";

It partially works. The sorting works fine but the issue is that it is dumping out 14 duplicates of each quote entry. For example, 'Acme Corporated' would be listed 15 times, then 'Bowman Industries' would be listed 15 times, then 'Chef Chu' would be listed 15 times, and so on. It should only list each quote entry once.

The only thing I can figure is that it has something to do with the MySQL query.

Ordering Queries
I am running a MySQL query using product ids like this:

SELECT * FROM products WHERE product_id=510 OR product_id=15 OR product_id=76

Sometimes there can be a string of up to 100 product IDs in 1 query. The problem is, I want the results to be displayed in the order that I am listing the product_ids. In other words, I want the results to be displayed in the order 510, 15, 76.

However, when I run the query, the results are displayed in a different order such as 15,76,510. How can I get the results to be displayed in the order that I have passed the IDs in my query?

Ordering Results In Php
The code for displaying such holidays is:

<a href="availableproduct.php?region=fiji">

Region is one of the fields in our database. Now here comes my problem. We have been asked to order the holidays for one region - i.e. Fiji, so that when they are displayed anything to do with Mana Island comes up first. Mana Island is not a region on its own and therefore is not a field. It will only be text in the title and/or description fields. Is this at all possible or no because it is not a region.

Ordering/grouping?
Say I have two fields of consequence in the same row for this operation, GUEST (varchar) and DATE (timestamp).

I want to make a select where all of the identical GUEST entries are grouped together, and have their DATE be the secondary ordering criteria. Additionally, I want each set of GUEST entries to have considered their highest number DATE entry to be the criteria by which to judge how high in the returned array it should appear.

little example - Note that i want the name with the highest timestamp to be first, followed by that same name with lesser timestamps (in descending order.)

sam 20041005000030
sam 20041005000025
sam 20041005000020
sam 20041005000010
dan 20041005000020
dan 20041005000015
dan 20041005000010
dan 20041005000005
ian 20041005000010
ian 20041005000005
ian 20041005000002
ian 20041005000001

I realize this might take a dash of php to get done, but I know some of you guys are pretty slick at cookin up the queries, so I figure I'll let you have a go at it. :thumbsup:

Ordering A Select
When I issue a SELECT statement without ORDER BY clause, MySQL returns the rows, from the first one to the last one.
Is it possible to reverse this result, that is returning the rows from the last one to the first one?

Ordering By One Collum
I want to make a MySQL query and have the results ordered by breed and THEN by regname. So basically I want it to put dogs of the same breed together and order them within their breeds alphabetically by their regname.

Ordering WHERE's Clausules
I got something like:

select fields from table1 where (
(something1 like '%somethin%') OR
(something2 like '%somethin%') OR
(something3 like '%somethin%') OR
(something4 like '%somethin%') )

I want to have the results ORDERED by
first, the results of first clasule (something1)
then about results of something2...and so on