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 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 Query
We recently changed servers for a bunch of clients and this new server is running MySQL 4.1.22. A bunch, if not all, of all queries which consist of ORDER BY statements are failing to order as they should. Below is a sample of our code that worked fine on our previous server but now is giving us problems. $query = "SELECT * FROM jobs WHERE status = 'active' ORDER BY 'date_full' DESC"; $result = mysql_query($query) or die (mysql_error()); I believe I have pinpointed the issue to the 'date_full' DESC part as if I remove the single quotes, the query works properly. My problem is that we have alot of clients and we've used this type of query for a ton of sites. Before I go through each and every site to remove the single quotes, is there any other solution?
View Replies !
Query Order By
I haven't done this sort of query before so looking for a bit of guidance! I have a table call members and a table call event_bookings. This is for an event management system i am developing. I want to be able to display a listing of all members who have booked into an event, but display them by surname. The database structure is MEMBERS TABLE member_id firstname surname EVENT_BOOKINGS TABLE booking_id member_id event_id The query without ordering it would be some "SELECT member_id FROM EVENT_BOOKINGS WHERE event_id = '$e'" How would I add an order by to sort this acording to the members surname...
View Replies !
Order Query Results
Regarding a dummy set of data below, ID Status 1 Open 2 Closed 3 Expired 4 Closed 5 Open 6 Cancelled 7 Expired 8 Cancelled I want to view the results but order them according to the status. i.e. i want to view in order of Open, Closed, Expired, Cancelled I can only think of a long drawn out way by which i would create the four seperate queries.
View Replies !
Query: Order By Price?
I have been wracking my brain for too long on this. Usually when you are thinking about something too long you miss the easy answer. Essentially I need to order a query by the price of the items: SELECT * FROM items ORDER BY itemprice Great but... when there is a sale... the itemprice is not correct you need the sale price. I have a digit on/off field for this: itemprice decimal(9,2) itemsale tinyint(1) itemsaleprice decimal(9,2) When the item is on sale itemsale is 1 and itemsaleprice is used instead of itemprice. Question is how do I order by all the current prices whether the item is on sale or not (and still include the sale prices where the item is on sale)?
View Replies !
Sub Query With ORDER BY Id DESC LIMIT 1
I am trying to make a query with some sub queries, and i am having some syntax errors. I have a table with the active users: which contains: id(auto_increment) | user_name | now_active the now_active field is inserted with value = '1', when the user logs in. And is updated to '0' when the user logs out. Here is the code: - At login: it inserts "INSERT INTO users_active (user_name, now_active) VALUES ('$user_name', $user_ip, '1');" - At logout: it updates the last UPDATE users_active SET now_active = '0' WHERE id =(SELECT id IN (SELECT id WHERE user_name = '$user_name') ORDER BY id DESC LIMIT 1); I need mysql to do what is below in one single query: 0. $user_name = John_example 1. $user_ids = "SELECT id FROM users_active WHERE user_name = $username"; 2. $user_max_id = "SELECT id WHERE id = $user_ids ORDER BY id DESC LIMIT 1" 3. $update = "UPDATE users_active SET now_active = '0' WHERE id = $user_max_id;
View Replies !
Reversing Query Results (not The Same As ORDER BY)
I am trying to reverse the order in which the results of my query are given. I am tweaking a gallery. I need to get the previous 3 pics based on the pic I am looking at. The following code is a simplified version of what I have. The problem is, when I echo the results, the thumbnails are displayed in DESC order (i need them to display in ASC order) but if I order the query using ASC, the query gets the wrong images. SELECT pic_id WHERE pic_id < $current_pic_id ORDER BY pic_id DESC LIMIT 3 I need the results to show like this: oldest pic | older pic | old pic | current pic | new pic | newer pic | newest pic I've got the new pics sorted out but the old pics are causing a little problem... So basically, is there a way that I can reverse the results of my query?
View Replies !
Help With Query: Order By Association Count
Allright, here are my tables: Posts: id | title | text Comments id | post_id | name | comment Basically one post may have many comments. Now, how would I select the 10 posts with most comments, and if only 5 posts contained comments (but there are in fact 10 posts) how could I make this query still return a total of 10 posts?
View Replies !
Mixed ORDER BY Query Results
Is there a way to order a query result a specific way? For instance, I'm querying using WHERE id IN(23, 25, 19) I would like the results to stay in that order instead of by ASC or DESC. Is this possible?
View Replies !
This Query Freezes When Adding ORDER BY
I have this table join statement and it works great, except when I try to order the results. When I add the ORDER BY call to the equation it freezes the browser. Alone without the ORDER BY it works fine SELECT u.id , u.username , p.user_id , p.report_id , r.id , r.company , r.description , r.market1 , r.market2 , r.market3 , r.market4 , r.market5 , r.market6 , r.location , r.date_year , r.date_month , r.source , r.video , r.audio , r.pp , r.profile , r.execsum , r.report_url , r.exec_url , r.keywords FROM user as u INNER JOIN user_reports as p ON p.user_id = u.username INNER JOIN emt_report as r ON r.id = p.report_id WHERE username = '$username' AND MATCH (r.company, r.description, r.keywords) AGAINST (''$P_search'' IN BOOLEAN MODE) ORDER BY date_year DESC, date_month DESC, company ASC // this ORDER BY when added freezes the search.
View Replies !
Select Query Sort Order
Example DB: Orders Table Order ID, Date, WeekID, Amount 1, 5/10/08, 0, 100.00 2, 9/3/08, 0, 200.00 3, 0/0/00, 1, 150.00 Weeks Table WeekID, DueDate 1, 7/2/08 I need the output to be in the following order base off of date ASC OrderID Date Amount 1 5/10/08 100.00 3 7/2/08 150.00 2 9/3/08 200.00
View Replies !
Reverse Order Of Rows In A LIMIT Query
I have the following query: SELECT * FROM stocks WHERE ticker = 'CBU' AND `date` < '2009-01-31' ORDER BY `date` DESC LIMIT 3 This returns the 3 most recent dates out of a total of 20 rows. I would like to use the 3 most recent dates BUT IN REVERSE order. if I use the ASC command, it will not work as it will return the 3 oldest dates in the 20 rows, not the 3 most recent in reverse order. How can I reverse the order of the 3 most recent dates returned with the LIMIT command in the query above?
View Replies !
Speed Up Query, Order By Column Is Too Slow!
I have a query that takes ages: SELECT tbl1.a, tbl2.b MATCH (tbl1.a) AGAINST ( 'someValue'IN BOOLEAN MODE ) AS score FROM `a` , `b` WHERE tbl2.b = 'someRestriction' ORDER BY score DESC, b.tbl1 DESC LIMIT 20 but because there are thousands of rows it takes ages to do both of the orders, however just : ORDER BY score LIMIT 20 -- is really quick and : ORDER BY b.tbl1 LIMIT 20 -- is really slow is it possible rearrange a constantly updated database so that its naturally ordered by b.tbl1 (so there will be no need to do the ORDER BY b.tbl1 query each time), or are there any other methods to speed this up (putting it all in one table instead of 2, is that significant?)
View Replies !
Random Query, How To Keep Sort Order Locked?
PHP Version 5.1.6 - mySQL 5.0.22 Let's pretend we have 100.000 rows in a table (all containing images with details), and I want to display 100 images at a single frontend page, but randomly retrieved and keeping the sort order once initiated to a user session. With the normal RAND function, you have a chance to stumble on the same images when you navigate to the next page with again 100 items on. But even worse, going back to page 1, will return totally different results, and will freak out the viewer How to get a (fake) random listing on a fairly large database, where every requested row is unique, and the sort order is being locked (until you press a randomize button ie, or start a new session) while keeping speed in mind (so trying to avoid reading out all rows up front).
View Replies !
Order By Not Working Correctly, Query Problem?
I am using the following query to get a list of anime names and related info from my mysql db. As anime are sometimes known by several names (the actual japanese kantakana/kanji, the romanji translation and the english word). So the info_anime table has all the info about the anime and the info_animename just has the relevent animeid and the name. The query below is ment to check all the names in info_animename and join them to info_anime. SQL SELECT n.*, a.* FROM info_animename AS n, info_anime AS a WHERE a.animeid = n.animeid ORDER BY ".$_GET[orderby]." ".$_GET[order]." Now all the names are displayed properly with the various info but if you look here: Live Anime - Anime List You will see that the names are not in alphabetical order, for the most part they are just anime with several names are not working correctly if you look at the bottom of the page you will see: Yakusoku no Basho Kumo no Mukou followed by Beyond The Clouds, The Promised Place Which are the same anime under different names.
View Replies !
Truncated Time Values Using TIMEDIFF With ORDER BY Query
I'm using the following query: SELECT glider, timestamp, TIMEDIFF(timestamp, UTC_TIMESTAMP()) AS last_contact FROM surfacings INNER JOIN (SELECT MAX(timestamp) AS most_recent FROM surfacings GROUP BY glider) AS tmp WHERE surfacings.timestamp = tmp.most_recent; to calculate the amount of time that has elapsed since the last inserted timestamp for each glider. Everything works fine: ....
View Replies !
Grouping For A Query And Limiting Returned Data In Order Of IN()
I'm trying to pull some data from the database in which it's supposed to return 3 rows from each f.forumid in the order in which i have the forumids, however it seems to be returning all the data from the table, what am i doing wrong? PHP Code: SELECT t.threadid, t.title, t.lastpost, t.forumid, t.open, t.replycount, t.postusername, t.postuserid, t.lastposter, t.dateline, t.views, t.firstpostid, f.title AS forumtitle, p.pagetext AS preview FROM thread t INNER JOIN forum f ON (t.forumid = f.forumid) AND t.visible = 1 LEFT JOIN post p ON(p.postid = t.firstpostid) WHERE t.forumid IN(200,250,2,30,15,60,70,90) GROUP BY f.title, t.title HAVING COUNT(*) <= 3 ORDER BY f.forumid, t.dateline DESC
View Replies !
Order By Count Of Occurrences In 2nd Table Without Nested Query (MySQL 4.0.27 Workaround)
My ISP is using an old version of MySQL, 4.0.27-max-log. I am using a query that runs fine on my test-server (MySQL 5.0.70-log). The main point of this query is to return an ordered list of volunteers for job assignment. One of the metrics is the number appearances in an activity table. I am using a nested query to COUNT the number of appearances during a specific time window to obtain this metric, but this approach does not appear to work in 4.0.27. Is there a simpler or alternative query that will make 4.0.27 happy? SELECT job10.person_id, people.history, job10.history, ( SELECT COUNT( * ) FROM activity WHERE activity.person_id = people.person_id AND (activity.day = '2009-2-1' OR activity.day = DATE_ADD( '2009-2-1', INTERVAL 3 DAY)) ) AS appearances, CONCAT( first_name, ' ', last_name ) AS name FROM duty_sing_am INNER JOIN duty_people USING ( person_id ) WHERE people.active AND job10.willingness > 0 ORDER BY appearances, job10.history, people.history, job10.person_id
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 !
Table/Query Guidance Requested
I want to build two tables from which I will pull information. One table (roster_table) contains names of employees. One table (groups_table) contains names of groups within a department roster_table: staffID (unique identifier for an employee) staffLast (last name) staffFirst (first name) groupID (a unique groupID from the groups_table) groups_table groupID (unique identifier for a group) groupName (the name of the group) One employee may belong to many groups, but I don't know how to structure my roster table such that I can show that in a single field (in other words, not have multiple groupID fields in the roster table). How would one go about structuring the roster_table so that this can be accomplished? I ultimately want to pull a roster of employees, and show all groups to which the employee may belong. Below is an example I tried, but it did not work. I'm guessing it is because I only place a single groupID value in the roster.groupID field as noted above. SELECT roster.staffID, roster.staffFirst, groups.groupName FROM roster_table roster, groups_table groups WHERE roster.groupID = groups.groupID ORDER BY groups.groupName, roster.staffLast (To be honest I cannot even say if this is a design problem or a query problem.)
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 !
How To Query Records That Have Same Words But Maybe Different Words Order?
I am trying to find a way to do a select query that can find records that have same words in any orders. e.g. "hello good morning", "good hello morning", "morning good hello"... they should all returned by the select query. Please don't tell me that I can use "or" inside select query. It's because what if the string has 10 words? There are tons of permutations.
View Replies !
Dynamic LIKE And Dynamic ORDER BY (was "need Help With Mysql Query")
there are two tables, download, and download_cat Im using one query to select something from two tables, PHP Code: select download.*, download_cat.* from download left join donwload_cat on download_cat.autoid='$_GET[cat]' and download.cat=download_cat.autoid where donwload.name like '%$_GET[key]%' order by download.'$_GET[order]' $_GET[desc]
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 !
|
TRACKER
