Order Then Order Again But From Colums Of Different Tables
I'm a noob to sql and I've been using a query thats worked really well for me for some time, but now I need to optimise it a little further and things seem to be coming un-done
its used for a search query: .....
View Complete Forum Thread with Replies
Related Forum Messages:
Re-order The Natural Order Of All Of All The Rows Of A Table
Basically I want to re-order the rows of my entire database. It has many thousands of rows, and people are constantly running the same query against it. The problem is its quite slow (using a shared host). I know you can use an sql query to order by column, however I need to re-order twice and it really seems to be slow due to this. Since the query is always the same, if the rows were already ordered in the database , then the ordering query wouldn’t need to be done. Is there an easy way to do this, without deleting each and every row, then inserting them again? basically in the database I have: The natural order when you do a select * from this table (without using an order by query): ________________________ |cola |col.. |coln | |dataA |dataB |dataC | |dataX |dataY |dataZ | |datal |datam |datan | (obviously a lot bigger than this) and I want reorder the entire table , eg The natural order when you do a select * from this table: ________________________ |cola |col.. |coln | |dataA |dataB |dataC | |datal |datam |datan | |dataX |dataY |dataZ | So is there an easy way to do this, am I just being dumb? or do I need to delete the entire table and insert the rows one at a time in order?
View Replies !
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.
View Replies !
Order By Two Tables
Is it possible to ORDER BY two tables? Like ORDER BY object ASC AND price ASC (this did not work, but maybe I have to write it differently)? My aim is that it will list the datas orderd by object like all the "house-objects" orderd by price and after that list all the "flat-objects" orderd by price.
View Replies !
Order By :: Two Tables
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.
View Replies !
How To ORDER BY The Order Requested In The Query?
Here's my query: SELECT * FROM myTable WHERE id=14 OR id=3 OR id=8 Simple stuff, I know. The result of the query is three rows that are all sorted by their 'id' in ascending order. I don't want this. What I want returned are rows sorted by the order in which I requested them. I need the query to return row #14, #3 and then #8 in that order.
View Replies !
Does It Matter What Order My Tables Are In?
Does it matter what order your tables are in within a database and if it does how do I reorder them? Reordering columns and fields within tables is easy enough, yet I can't seem to find anything regarding the order of tables; which seems to suggest it's not important. Enlightenment please.
View Replies !
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.
View Replies !
Displaying From 2 Tables And Order By 1 Row
I have two tables: table One: UserID | mainTitle | time table Two: ID | xTitle | time Time is in datetime format. I would like to select rows mainTitles and time from table one and rows xTitles and time from table two. Then I would like to order all results by time. I tried the following code, but it doesn't work: PHP Code: $result = mysql_query("('SELECT * FROM One WHERE UserID=$ID') UNION ('SELECT * FROM Two WHERE ID=$ID') ORDER BY time"); while($row = mysql_fetch_assoc($result)) { ... }
View Replies !
ORDER BY Clause On Multiple Tables
I want to add an exception to the ORDER BY: Code: SELECT * FROM `download` WHERE `category` LIKE 'action' AND `dlt` != 1 ORDER BY `featured` ASC, `tag` DESC LIMIT 0,30 In plain English, I want to order by featured ascending if featured > 0. If featured = 0, defer to ordering by tag descending.
View Replies !
New To Mysql5 And Can't Order By When Joining Tables?
I just got a second dedicated server (fedora) and this one is running mysql 5 whereas the other servers run 4. For some reason, I don't know if it's perms or just the way the server was setup?.. I can't run order by statements when I join 2 tables and also I can't view the structure of a table? I can create tables and query a single table and also join two tables but I can't order by the joined tables... I just get: #1 - Can't create/write to file '/var/lib/mysql/#sql_7693_0.MYI' (Errcode: 13) is this some new mysql 5 feature?
View Replies !
ORDER By TIMESTAMP From Multiple Tables
things a going but i got thought one ... (to me at least) to simplify ( i actually have 9 tables): Let's say I have 3 tables representing 3 different events (like user registration, user login-in, user upgrading ...) .....
View Replies !
Select Data From Two Tables And Order By One Of Them
I have two table book and book_images. book contains a list of books and book_images is a table that lists the books and the assigned image (there can be more than one image assigned to more than one book); The process to assign a image to books is this. User selects the image. then from the list of books selects which one/ones they want to assign it to. As there are over 7000 books i want to add a search. so when someone types in "Vellum" then the book Vellum will be returned. But the problem is i would also like the books that are in the book_image table.
View Replies !
Get Data From Multiple Tables And Then Order By Field
$QueryHIST_QUOTES = " SELECT TIMEDIFF(TIMESTAMP, $starttime) AS diff, t.* FROM $currentTable t WHERE TIMESTAMP BETWEEN $starttime AND $endtime ORDER BY TIMESTAMP"; Originally I had the variable $currentTable = "PRICES_2008" but I now want to get rows from multiple tables, so I tried: $currentTable = "XAUUSDOZ_2003 XAUUSDOZ_2004 XAUUSDOZ_2005 XAUUSDOZ_2006 XAUUSDOZ_2007";
View Replies !
List All The Tables In A Schema In Order Of Dependency
Can we write a query to find out the tables which are linked to a particular table. For example i have a table named customer and i just wanted to know to which tables it is linked to or to which table it acts as a primary key. In my database customer table is linked to order table and location table. so i should write a query about the dependency in such a way that the query should return all the dependency tables on customer table then the result should be like order and location table. So that i can easily know the dependency between two tables.
View Replies !
Distinct, Order By And Limit From Multiple Tables
(I am slowly going insane over this problem) I want to fetch the "items" that received the latest 5 comments, without showing the same item multiple times (when it has, for example, 3 comments that fall into the "latest 5 overall" category) When I run the SQL it uses the first comment on each distinct items.id (while it should show the last/latest), and the results are therefore rather useless. I'm using MySql 4.1.9 (I did search and see some similarish threads, but I'm too braindead right now to extract a solution on my own) SELECT items.id, items.name, comments.id AS comment_id, comments.created AS thetime FROM comments, items WHERE comments.parent_id = items.id AND items.status = 0 GROUP BY items.id ORDER BY thetime DESC LIMIT 5 CREATE TABLE comments ( id INTEGER(11) NOT NULL AUTO_INCREMENT, title VARCHAR(255) DEFAULT NULL, created DATETIME DEFAULT NULL, parent_id INTEGER(11) DEFAULT NULL, UNIQUE KEY id (id) )TYPE=ISAM ; CREATE TABLE items ( id INTEGER(11) NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, status INTEGER(11) DEFAULT Ɔ', UNIQUE KEY id (id) )TYPE=ISAM ; --
View Replies !
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....
View Replies !
ORDER BY :: Sequence Order
Is it possible to ORDER BY based on the sequence order, for instance I use this Query: SELECT * FROM Showroom WHERE Artnumber IN (52900, 52536, 52730) the result is this: Artnumber Price 52536 25,80 52730 1,90 52900 31,10
View Replies !
Order By Unconventional Order
How can I have letter "z" comes before letter "a" when I select my items from a table. I need my list to show (z, a, b, c, d, ...). Also, how can I have numbers come after letters, say, if I want my list to be ( z, a, b, c, ... 1, 2, 3, ...)
View Replies !
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:
View Replies !
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?
View Replies !
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??
View Replies !
Order By Ascending 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
View Replies !
Speed Up Order Table Table-name Order By Col-name For Large Table?
I have a table with 2 column: id and name. Column name is of type varchar(100). The table has about 0.1 billion rows, taking 11G disk space. Now what I want to do is alter the table by the name column. I ran the command: "order table table-name order by name" 1).Firstly, it create tmp table,which takes 19G disk space. 2).After that,the 19G space seems to be released(I see the free disk space increase by "df"). 3).Then I see that in the mysql directory, the size of the mysql file #sql-14f7-2.MYD" increases 2M every time, but very slow. I guess this mysql file is the new table after order. But how can it be so slow? It only increase 2M every 2 minutes or so, which would takes weeks to finish the command. So could any one tell me how can I speed up this command? Are there any variables I can adjust to make this faster?
View Replies !
Specify A Value For At Least One Of The Colums
i have created a database for a form to hold peoples details who come to my marquee site. i have done most of the entrys as varchar and set null to not null and dont know what to put int he default so left it blank! i have connected to teh db in dreamweaver thts all fine but when i try to insert record i get please specify a value for at least one column.
View Replies !
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.
View Replies !
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'
View Replies !
Order By 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.
View Replies !
Row Order
If I run a simple SELECT on a single table and it returns a small sized data set of say a hundred rows or so, and I don't set any ordering constrints (such as order by date or ID or something), if I run this same query over and over, will the row order be the same assuming no changes in the table?
View Replies !
Specify Order
Is it possible to specify order before grouping? I want to group a list of user actions by user id with the most recent at the top. For example, the query would be: SELECT * FROM UserActions ORDER BY Time DESC GROUP BY UserID; That doesn't work because you have to specify GROUP BY before ORDER BY. However, the output of specifying them in the correct order is not what I want. It seems to pick the first record in the database as the GROUP instead of the most recent one.What I'm actually trying to do is get a list of all the Users in order of the most recent UserAction, so as to be able to view the latest acitivity on my web site.
View Replies !
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???
View Replies !
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
View Replies !
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?
View Replies !
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.
View Replies !
'Order By'
I'm pulling a list of links out of a DB and ordering by their 'order id'. instead of getting (for example) '1,4,7,12,22', I'm getting '1,12,22,4,7'.
View Replies !
Order Within Order
I have this query: Code: $sql = "SELECT p.id, p.name, c.name FROM projects p, category c WHERE c.id=p.category_id ORDER BY c.name" that sorts my projects per category. Now within one category, i need to sort the projects per name
View Replies !
Order By And Count
I have been trying to do a similar thing for a number of different tables but have given up as i could not find a way to do it but it seems an obvious thing many pople would want to do so i thought i would ask once more to see if it is possible. I have a mysql table like this Id01 Maidstone KENT Id02 Ashford KENT Id03 Bristol AVON Id04 Westminster LONDON Id05 Bath AVON Id06 Chatham KENT Is there a way to order the table by the county and get total for the number of rows fro meach county (for output to a coldfusion page) e.g. KENT (3) Chatham Maidstone Ashford AVON (2) Bristol Bath LONDON (1) Westminster Though formating does not matter
View Replies !
Order By With Group By
If I have a GROUP BY statement, which groups my multiple fields, e.g.: SELECT ......GROUP BY date, store can I have multiple ORDER BY statements with that? e.g.: SELECT ......GROUP BY date, store ORDER BY date asc, store, desc Is this an acceptable syntax?
View Replies !
Order By With Variable
I have listings with coordinates, i would like to do ORDER BY and display listings with coords first. If they don't have coords the value is blank and the coords go both positive and negative values. So i tried an "ORDER by l_xcoord HAVING l_xcoord<>0" but this is acting like a WHERE statement removing all results with no coords. I tried GROUP BY as well but same results.So the question is how can i display all results with the ones that have coords first? It seems like it should be simple but I'm just not seeing it.
View Replies !
Order By Primary Key
I'm building an Ajax table which fetches its contents from mySQL. The idea is that the table displays x results on each page, and when you click 'next page' it downloads the next x results and displays them. The table uses an arbitrary SQL string to fetch results, and I'm stuck on how to access just a small result set. Since the SQL is arbitrary I can't use WHERE myfield = somevalue, because I don't know what 'myfield' is. I'd like to substitue 'myfield' for the primary key of whatever table I'm accessing. So "select * from table where myfield < x and myfield y" becomes "select * from table where table_primary_key < x and table_primary_key
View Replies !
ORDER BY Regex?
I have a list like this: Reckless Engineer St Bonaventures The Blue Mountain The Croft The Cube Club The Folk house I'd like to reorder that ignoring the leading "The".
View Replies !
SQL Sort (Order By)
I have sql statement below , SELECT serial_no,host_name,chasis_model,chasis_flash_size ,chasis_dram_size, country,city,building,other,chasis_sw_version,stat us,chasis_eos,chasis_eol,chasis_user_field_1,chasi s_user_field_2,chasis_user_field_3 FROM tbl_chassis ORDER BY country = '', country However , my user would like to sort by country,city,building and other columns . Anybody have ideas how to do that ? I do order by country,city,building,other gave me the result which i not expected. For example below data before sort Country City Building Other Thailand Bangkok 208 Wireless RD Thailand Bangkok 208 Wireless RD Thailand Bangkok 208 Wireless RD Thailand Bangkok 208 Wireless RD Thailand Bangkok 83 Aber rd Thailand Bangkok Thailand Bangkok 12 Godieon Rd Thailand Bangkok 900 Telepark After sort Country City Building Other Thailand Bangkok 83 Aber rd Thailand Bangkok 12 Godieon Rd Thailand Bangkok 900 Telepark Thailand Bangkok 208 Wireless RD Thailand Bangkok 208 Wireless RD Thailand Bangkok 208 Wireless RD Thailand Bangkok 208 Wireless RD Thailand Bangkok
View Replies !
Order By :: Using IN Clause
SELECT * FROM kf_gallery WHERE gallery_id IN ( 3, 1, 2 ) ORDER BY ? What I'm trying to do is get the results in the order specified in the "IN" clause, i.e. (3, 2, 1). Is this possible? (I'm having trouble searching the mail archives).
View Replies !
Force Order
I'm working on a web page in php with a mysql DB. I'm trying to list some products with some other information but I'm having trouble tying them together. Is there a way to force a very specific order to be returned? What I need to be able to do is have a query like "select * from products where prodID = 'thing1' or prodID = 'thing2' or prodID = 'thing3'... but I need it to come back in that EXACT order, product matching thing1 first thing2 second and so on.
View Replies !
MySQL Order
I would like to order the results from a query in a non standard way. I would like to order them by a field called season and return the results in the order LOW, MID, HIGH. I obviously can't order the results alphabetically but wondered whether you can define the order in the query.
View Replies !
ORDER BY With Accents
When I sort texts with accents the accents end up in the end instead of near the same text without accent. For example, the 3 composers Händel, Haydn and Holst will appear in the order Haydn, Holst, Händel. Is it possible to instruct MySql to ignore the accents so that Händel comes in first instead of last?
View Replies !
ORDER BY :: Accents
When I sort texts with accents the accents end up in the end instead of near the same text without accent. For example, the 3 composers Händel, Haydn and Holst will appear in the order Haydn, Holst, Händel. Is it possible to instruct MySql to ignore the accents so that Händel comes in first instead of last?
View Replies !
|
TRACKER
