MYSQL :: Order By

ORDER BY WHERE + Include Rest Of Table Or ORDER? Possible?
I need to create some SQL that when run returns all the rows which a column is equal to number that has been specified, but then the query needs to return the rest of the rows in the table. How could this be done? is it even possible? Is their order syntax that could do this better?

Multiple ORDER & SUB ORDER On The Same MySQL Table
I am trying to do multiple ORDERS or SUB ORDERS on the same MySQL table, and I'm loosing my mind trying to fathom the logic and SQL statement to use, I'm no MySQL genius! more a newbie.

*** SEE ATTACHED IMAGE PLEASE

I have tried all sorts of SQL statements, e.g:

SELECT * FROM categories GROUP BY parent ORDER BY order, parent DESC

Nothing seems to work.

I think my only solution is to do a bubble-sort after putting the whole table in a PHP ARRAY ?

I'd also like the menu to act like the + pop-open sub-menu boxes on the Forum left column menu.

How To ORDER BY DESC Properly. It Makes 150 Before 50 If ASC Order!?
I have this list:
50
150
200
250
300
350
400
450
500

If I order it by ASC order then mysql results this:
150
200
250
300
350
400
450
50
500

What the hell ?? How do I order it properly?

How Does MySQL Order Records With No ORDER BY?
If you don't specify an ORDER BY clause, how does MySQL order the records? Is it just the order in which they found in the database?

Selection Order Without Order By!?
I have a table with this structure

trans
-------
t_no (int auto_increment)
t_timestamp (datetime)
t_price (double)
t_vol (int)

I've inserted two record into trans table with same t_timestamp value. When i do a select * from trans it doesn't always return ascending order based on t_no? But sometimes it does?

I would like to know how MySql order selection result wihtout the order by command based on this scenario.

Order By - Order Of Precedence?
I know how to use the order by, but wondered if there is an order of
precedence in using it.

Facts: hcounty is a county that someone puts in a home county in which they live. In the comments they are also allowed to put text, that might contain a county listing also. I want to have the results put the records that have hcounty matches first, then the records that have a county match in the comments field after the first set of records. I have tried all sorts of order by and group by and can't get the result I'm looking for....

Order By - Order Of Precedence?
I know how to use the order by, but wondered if there is an order of
precedence in using it.

Facts: hcounty is a county that someone puts in a home county in which they
live. In the comments they are also
allowed to put text, that might contain a county listing also. I want to
have the results put the records
that have hcounty matches first, then the records that have a county match
in the comments field after the first set of records. I have tried all sorts
of order by and group by and can't get the result I'm looking for. Any hint
or assistance would be greatly appreciated.

$query = "SELECT * FROM contacts WHERE hcounty ='$searchcounty' or comments
like '%$searchcounty%' order by state, hcounty, dtestart ";

Force Order By Order??
I have 6 rows of data... each has a name, eg: c1, c2, mrgs1, totalfte.
they are all under the one column "field". Is there a way I can force their order??

at the mo, I use "ORDER BY field DESC" and its coming out lile

mrgs1, totalfte, c1, c2

I need

totalfte, mrgs1, c1, c2

is it possible to sort like this??

Custom Order For ORDER BY
I'm grabbing six specific records, like so:

$result = mysql_query("SELECT * FROM casestudies WHERE id=1 OR id=2 OR id=3 OR id=4 OR id=5 OR id=6 ORDER BY ___________",$db);

I'd like to post them in a custom order, specifically: 1,3,4,5,6,2. Can't figure out how to do this with ORDER BY, if that's even possible. Anything I'm missing?

ORDER BY Out Of Order - Fixed
I have a query that uses two tables that I want to produce output like the following:

+-------------+-------------+-----------+
| LOCATION | GROSS SALES | NET SALES |
+-------------+-------------+-----------+
| Location 1 | 11,860,735 | 2,907,552 |
| Location 4 | 4,814,029 | 1,077,003 |
| Location 3 | 2,711,795 | 710,804 |
| Location 5 | 2,660,040 | 666,255 |
| Location 2 | 2,049,470 | 563,830 |
| Location 8 | 2,227,730 | 543,220 |
| Location 7 | 1,766,880 | 425,483 |
| Location 6 | 1,721,681 | 367,252 |
| Location 10 | 13,424 | 2,253 |
+-------------+-------------+-----------+

However, I cannot get the "order by" part of the statement to work. I have tried adding a zero to the order by alias. And when I attempt to specify the field in the order by with the calculation and not the alias I get an error.

The following examples are queries without the zero and with a zero added to the alias in the order by line. I have also added the details for the table below for reference. Code:

Order By A Sum?
Ok this is really hard to explain. Table1 has userid's that correspond to another table along with 2 other fields num1 and num2. I need to get the 15 userids from Table1 with the highest SUM(num1) / SUM(num2). I can do this with PHP but would like to do it solely using SQL.

Help With ORDER BY
I have this query:

PHP

SELECT p.*, u.points, c.Title AS category, c.CategoryID AS cid, CONCAT(u.FirstName,' ',u.LastName) AS user,
u.UserID AS uid, COUNT(b.UserID) AS referrals
FROM products AS p, categories AS c, ltd_user AS u
LEFT JOIN ltd_user b on p.UserID=b.referrer
WHERE p.section=c.CategoryID AND u.UserID=p.UserID AND p.pppicture != '' AND b.ConfirmS = 1 AND b.UniqueIP = 1 and p.power=1
AND u.points>=10 AND p.section IN (1,2,3,4,5,8,9,10,11,12,13)
GROUP BY p.productid
ORDER BY p.bid DESC, referrals DESC LIMIT 0,10

problem is that query order only by p.bid while I need to sort by both

Order By
I'm trying to modify a mysql search and am coming up against a brick wall due to my lack of mysql knowlwedge.

Basically the search is for an online dating site. It references a table containing the basic member details and also a table containing member profile details. The member profile table includes a lastlogin column so I'm currently ordering search results by the most recent member login. However, I'd like members who have bothered to upload photos as well to be found above those without photos. The photos are saved into a separate table which includes a field called member_id (the same field can be found in the other 2 tables mentioned aswell).

Currently the order by part of the query is:

ORDER BY p.lastlogin DESC
What do I need to add to this to ensure members with photos are also listed before those without?

Order By?
The following mysql statement produces a buch of unordered results:
SELECT Approve, ID, Name, Image1 FROM PROVIDER WHERE Approve = Ƈ' LIMIT $from, $max_results;
3
1
5
7
4
6
2
Id like to place an ORDER BY thing in there (ORDER BY ID(asc))
so it appears like this;
1
2
3
4
5
6
7
How do I do that?

Order By
If i have a table with say 50 rows,
is it possible to display the data of the first 20 rows
by, as an example...ORDER BY age
and the remaining 30 rows
by, as an example...ORDER BY name

ORDER BY
The following query does not seem to order by "bid_amount". It will simply take the first entry in the database.

Any idea how to make it order by "bid_amount" (DESC)? ......

ORDER BY A Set
I want to order a result set by a specific order, rather than just ascending or descending alphabetically/numerically. For example if I have a table called region

idName
1London
2Scotland
3Wales
4Midlands
..
..
..

but I want them ordered so that the result is Scotland, London, Wales,
Midlands,... can I use some sort of set in the order by clause?

Something like:
select name from region order by id {2,1,3,4};

I haven't used mysql SETs before so not sure if they can be applied here.

ORDER BY
I have dates recorded in my database for my website, in a var char in this format: 4/8/07, 4/21/07, 4/22/07, 4/10/07, etc. When I use order by to try to order by the most recent date, it doesn't work.

I even know why, and it's because it'll put 4/8/07 before 4/21/07 because there is no 0 before the 8, so it sorts it as 8 larger than 2, which it is. Is there any way to get around this, so it will order correctly?

Order By
I'm doing an ORDER BY date DESC and I want 10:00:00 to be higher that 4:00:00, so why isn't it?

Order
I have a mysql table with a column with a few zero values and an ordered list of numbers.. eg 0,0,0,1,2,3,4,5

is there a way to order the table in ascending order for the numbers 1-5 then display the ones with zero afterwards in one sql query..

so it would turn out like 1,2,3,4,5,0,0,0

ORDER BY
The column duration in the reports table is type = time. The following query produces the results below it. My question is why does the 9th entry appear where it does? Or better yet, How can I get it to sort "correctly?" That's "correctly" by my understanding, not necessarily SQL's. :)

SELECT a.pilot_num AS Pilot,
sec_to_time(sum(time_to_sec(b.duration))) AS Duration
FROM pilots AS a, reports AS b
WHERE a.pilot_id = b.pilot_id AND month(now())=month(b.date) AND year(now()) = year(b.date)
GROUP BY Pilot
ORDER BY Duration DESC
LIMIT 10

Pilot Duration
CAA165 79:27:00
CAA148 65:53:00
CAA001 31:07:00
CAA170 19:13:00
CAA131 16:29:00
CAA142 14:56:00
CAA183 14:37:00
CAA156 12:06:00
CAA161 115:06:00
CAA071 10:50:00

Order By
I have a "news" database and I only want to get out the last post, not the whole database.

idnews
headline
date
content

I use the following: $sql = "Select * from news order by idnews desc";

Anyone know a command to only get the last post?

Order By
I am trying to figure out in the easiest manner.

Have a list of values
S00500
123456
156789
205000
S00067

and using this string, how do I modify the following:

$qstr .= " order by $order_by";

so it will sort to the following (number first, then letters ordered):

123456
156789
205000
S00067
S00500

Sql Order By
I have just started re-writing my forum, which is going to take a long time. While I am doing this I have a bug to solve on version 1.3, which is to do with the SQL order by.

Each post has a number, 1, 2, ect. Every time a post is added the post number increases.

When I get to 10, the ORDER BY section of my script ( I assume ) uses the first number before any others. So 10 is considered less than 2. Ruining the whole order by system.
Is there a way that this can be solved???

Order By
I have a colum that has three possible numbers

0
1
2

How would I order these records with

1 being first
2 being the second
0 being the last

ORDER BY
I have table X with UNSIGNED INT column C. I want to order by C ASC,
but have the 0 value last. So, I thought that this should work:

SELECT *, (C-1) AS o from X ORDER BY o;

but it doesn't. Doing a SELECT on (C-1) shows an extremely large
POSITIVE integer value where C=0, as I expected from an UNSIGNED column.
But it is ordered first (as -1). Is this intended behavior? Am I
doing something wrong? I worked around it for now with:

....ORDER BY C>0 DESC, C ASC;

but my curiosity is aroused... And which is faster, anyway?

Order By
in the mysql query, how do u have it order the results from newest to oldest instead of alphabetically?

Order By
I've been getting problems with the way data is extracted from a table. The table contains a column called 'name' which is a varchar(255). Each row contains strings of text... Code:

ORDER BY
This is my current MySQL query:

$query = "SELECT p.post, p.topic_id, p.pid, t.tid, t.title, t.posts, t.start_date, t.starter_name, t.forum_id, t.topic_hasattach, t.rating, t.total_votes, a.attach_id, a.attach_is_image, a.attach_hits, a.attach_pid, a2.attach_thumb_location, a2.attach_pid, r.member, r.file

FROM gf_posts p
LEFT JOIN gf_topics t ON ( p.topic_id=t.tid )
LEFT JOIN gf_attachments a ON ( p.pid=a.attach_pid )
LEFT JOIN gf_attachments a2 ON (a.attach_pid=a2.attach_pid )
LEFT JOIN gf_rated r ON ( t.tid=r.file AND $memnum=r.member )
WHERE t.forum_id=80 AND t.topic_hasattach=2 AND a.attach_is_image=0 AND a2.attach_thumb_location!=''
ORDER BY ".$orderby.", a.attach_hits desc
LIMIT $page, $limit";

Which works perfectly fine. However, I was wondering if I could have it so rows whose t.start_date>$yesterday would always display before those rows whose start_dates are earlier? I don't want to order by start_date, I just want those rows who were made in the last day always go to the top of the list, with the rows who don't fit that criteria being sorted by their rating.

I read into it a little, and it appears I can do this by GROUP BY. I tried GROUP BY(t.startdate>$yesterday) but it didn't work, so obviously I don't quite grasp the usage yet.

I also did this earlier by using two separate queries. However, this fudged up my scripting when I tried to spread the results over multiple pages.

Order By
I need to be able to order results based on several criteria.

I need to be able select all records where field:group = 'value given'
and display results sorted by these criteria:

1) if field:company is not empty then display field:company asc

2) if field:company is empty then display field:fullname asc if not empty

3) if field:company and field:fullname are empty then display field:lastname asc if not empty

4)if field:company and field:fullname and field:lastname are empty display field:firstname if not empty

these results would be easy if I could just sort all by say company but if some fields in the group have an empty company field then it doesn't get included how can I sort by all these criteria. how could I get all the records in group sorted if some fields will be empty and others not.

Order By
I have two tables:

Cont, with columns: Cod and CodMot.

Mot, with columns: Cod and Name.

I want order the Cont table for the name of the table mot.

The field CodMot of the Cont is the same Cod of table Mot.

ORDER BY
I am trying to sort my shirts by size (S,M,L,XL). I've tried the line
below but I can't get it to sort the way I need. Any suggestions?

Order By
Is there any way that I can optimse this query so that I return the order passed in by the mysql query.

The reason I am trying to do this is because I have already defined my order in a previous array function and I am passing in a string as a list of ID's For example

SELECT xID FROM X1 x WHERE x.xID in(481,1,373,3,429,480,427,187,2,464,604,224,567,219,484,577,190,546) limit 0, 5

Can this be done within the same query or do I need to make a seperate temporary table and then use the order by function?

Order By
I have a database query that retuens the results correctly, but i need to do an order by clause. When i do this it displays numbers before letters,

Jobcard No Page No.
455101 page 1
455101 page 2
455101 page 3
567070 page 1
PRE001 page 1
PRE002 page 1
POS001 page 1
POS001 page 2
POS002 page 1 etc, etc, etc,

whereas i need to display letters, number then letter again e.g

Jobcard No Page No.
PRE001 page 1
PRE002 page 1
POS001 page 1
POS001 page 2
POS002 page 1
455101 page 1
455101 page 2
455101 page 3
567070 page 1
POS001 page 1
POS001 page 2
POS002 page 1 etc, etc, etc,

the field Jobcard No. is a Varchar i think i need to changed to asci and Order By that way. I'm not sure though, any ideas?

ORDER BY
I have made a little spanish-croatian dictionary and all te words are stored in MySQL db.
I populate the database with INSERT INTO statement and all works fine. But, when I try to view all words sorted ascending there is a problem:

For now I have 193 words in my DB. I first inserted 121 words, and after that the rest 72.
I use php to connect to db.

When I try to sort them they are sorted in 2 groups. First group of 72 from a-z, and second group of 121 from a to z. Why is that, can somenone help me with this.

The statement I use is:
SELECT * FROM $table ORDER BY hrv ASC Code:

ORDER BY
I'm trying have a query where I can set the list a specific order of a players games.

I know the below is wrong, I'd like to something like the following:

$order = "inactive, trading, attacking, waiting";
sql = "SELECT player, state FROM game WHERE player = user ORDER BY {$order} ";

ORDER BY
I'm building a website where choir-members register themsselves. One of the fields contains info about the voice they sing (soprano, alto, tenor, bass), indicated by a letter (S, A, T, B). When I order by voice in a query ofcourse the altos come first, then the basses etc. I need the order to be s-a-t-b, instead.

I figured out two solutions but I hope you know a third, better one.
First: say s=1, a=2, put the number in the table instead of the letter and reintroduce the letter where nescesarely(?). This requires a lot of editing because the existing system is huge.

Second: Make four query's and use 'WHERE voice=s', 'resp. WHERE voice=a' and so on instead of 'order by'. Combine the four query's and there you are.

ORDER BY
I want to do a SELECT on a table, returning just 1 column, but sorted
according to another column in the origianl table.

Whats the general way to do this ?

I have tried:

SELECT DISTINCT buid FROM bookingtable ORDER BY user

which does not seam to work.

The other way I can think of to do it :-

A 2 stage approach,

first make a selection that also includes the column on which
I want to order by,

then make a selection from that with just the single column
that I am really interested in.

But I'm not sure how to do this.

Can you run a second query on the results of the first ?

Or do I have to create a new table (from the first query) to do that ?

ORDER
I need to be able to use order by on a column that can contain html. I don't want mysql to look at an item that begins with <i> as starting with <. Any ideas? Is it possible?

ORDER BY
in my database i have a table for 'lastname' which I am using to ORDER BY now in ASC

But some of the names dont have a last name and only have a firstname which is stored in the 'firstname' table.
so they show up on the top of the list ( the ones w/o last names)

Is there a way I can write it to say something like...
$namequery = mysql_query("SELECT * FROM teg_names WHERE letter = '$letterofarray' AND updated = 2 ORDER BY lastname, firstname ASC")or die(mysql_error());

ORDER BY
I have this list that i'm ordering by units_unit_id and is there way I can get
a natural order so that D4 and D5 comes before D12

+---------------+
| units_unit_id |
+---------------+
| D1 |
| D12 |
| D14 |
| D15 |
| D16 |
| D18 |
| D19 |
| D4 |
| D5 |
+---------------+

Order By
got data in a table which looks a bit like

id | subID | value
0 | 1 | home
0 | 2 | home>about us
1 | 1 | products
1 | 2 | products>order
2 | 1 | contact
2 | 2 | contact>accounts
etc. etc.

if I do a SELECT statement like:
SELECT DISTINCT id,subID,valueFROM info order by ID ASC

I get something like:
0 | 2 | home>about us
0 | 1 | home
1 | 2 | products>order
1 | 1 | products
2 | 1 | contact
2 | 2 | contact>accounts

i.e. the ID is in order, but the subID isn't....

is it possible to use ORDER BY to get the data to come out in the proper order?

ORDER BY
moving from MSSQL to MySQL and having a problem getting my ORDER BY working.

Here is the my latest simplyfied (failing) attempt :

DECLARE v_SortOrder INT;

SELECT column1,
column2,
column3

FROM tablename

ORDER BY
(CASE WHEN v_SortOrder = 1 THEN column1
WHEN v_SortOrder = 2 THEN column2
ELSE column3
END CASE)

Order By Umm OR
Lets say I have a query similar to:

SELECT * FROM company WHERE id='3' OR id='1' OR id='7' ;

I want to be able to get the results to come out ordered by the order
the ids were searched (the sequence in the WHERE OR; 3 first then 1,
then 7...), so the results would be like:

Row | id| name
-------------------
0| 3| Joe's Co
1| 1| Buymart
2| 7| The big evil

As it is the results returns it like id 1, then 3, and 7.

Order By Str To Int
Hi jave a database and I want to oder my result by a colum containing
string bit order them like they where int

ex Select * form members order by level

level is string containing data like '12'

I want to oder like the level is Int 12 and not string '12'

Order By
I have a imported data that has date format as
2/2/2003
12/1/2004
1/25/2004
I need to sort data by the date column. What is the best way to do it?

ORDER BY
I am trying to sort the contents of a DATE formatted row in ascending order. Currently, the following query sorts the dates in alphabetical order; versus numeric:

$query2 = "SELECT DATE_FORMAT(date_date,'%M %D %Y') AS date_date,date_info from imp_dates ORDER BY date_date ASC";

Displays as:

February 13th 2006
June 1st 2006
March 15th 2006
May 23rd 2006

NEED to Display in numeric order:

February 13th 2006
March 15th 2006
May 23rd 2006
June 1st 2006

My Structure:

Database Name: data
Table Name: imp_dates
Row 1 Name: date_date DATE not null
Row 2 Name: date_info VARCHAR(250) not null.

Order By
Lets say I run a simple SELECT query with an ORDER BY DATE_ENTERED ASC clause. Is it possible to instruct the query to place a particular row first and then ORDER BY DATE_ENTERED ASC. So the query would look like this......
Code:

SELECT * FROM CUSTOMER_ORDERS ORDER BY CUSTOMER_ORDERS = '112234' , DATE_ENTERED ASC