Selecting And Sorting
I have a query:
mysql_query("SELECT * FROM table
WHERE code IN ('G1','D36','M17')")
I want to be able to sort the results in the same order as they are specified in the above, i.e G1-D36-M17. However, since I entered them in the database in the order D36-G1-M17, that is the order in which they are displayed by default. Is there any way I can specify which rows I want to pull from the database AND the order in which I want them displayed, at the same time? If not, is there any other way I can get them to sort in this order without adding an index to the database?
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Selecting And Sorting With Different Columns.
I am creating a small search engine for a site that reviews other sites. My DB structure is: TABLE `sites` ( `ID` int(11) NOT NULL auto_increment, `site_name` varchar(100) default '', `site_description` varchar(255) default '', `type1` varchar(100) default '', `type2` varchar(100) default '', `type3` varchar(100) default '', `type1_rating` int(4) default '0', `type2_rating` int(4) default '0', `type3_rating` int(4) default '0', PRIMARY KEY (`ID`), FULLTEXT KEY `forsearch` (`site_name`,`site_description`,`type1`,`type2`,`type3`) ) TYPE=MyISAM AUTO_INCREMENT=483 ; Assuming that I have 2 rows on my DB with the following data: SITE1 site_name='mysite' type1='car' type2='aircraft' type3='construction' type1_rating='23' type2_rating='40' type3_rating='34' SITE2 site_name='coolsite' type1='jet' type2='bus' type3='car' type1_rating='35' type2_rating='22' type3_rating='66' When I search for "car" it will find that the two sites match the query. That's fine. But how do I sort the results (based on the 'type_ratingX') if the value found is in different fields? On SITE1 I will use type1_rating and on SITE2 I will use type3_rating to sort!
View Replies !
View Related
Selecting And Sorting The Last 5 Entries.
I have a table with songs, for each song i have ID,title,duration The ID column is auto_increment and is unique. So when i want to select the last 5 entries i do a "SELECT * FROM songs WHERE 1 ORDER BY ID DESC LIMIT 5" But what if i want to sort those last 5 entries by song title ? I was able to do that with subqueries, but on my current server i have older version of mysql that does not support subqueries.
View Replies !
View Related
Selecting Records, Then Summing Parts Of A Column, Then Sorting By That Column...
I’m keeping track of baseball stats, and each row represents one line of stats (from a box score) for one player of a single game. Because of this, a single player may have multiple rows in the table. I want to cumulate each player’s stats (so they’ll be one row per player) and display as output, which isn’t a problem. Then I want sort by a certain stat, but by now I’ve already looped through the table, so I can’t sort using a mySQL query at this point. I tried first putting values into an array in a previous project, but that became extremely complicated. What’s the best way to approach this?
View Replies !
View Related
Sorting By Value
How can I fix this? Using the following to select and sort: $query_land = "SELECT id, type, estate, parcel, asking, zoning, `size`, selling FROM sold2005 WHERE type = 'land' ORDER BY estate ASC, selling ASC";
View Replies !
View Related
Not Sorting
some how my query is not sorting ASC or DESC, what am doing wrong? Code: SELECT DATE_FORMAT(jdate, '%a. %M-%d-%Y') as jdate, jid, jtitle FROM hrjobs WHERE jstatus ='on' ORDER BY jdate ASC
View Replies !
View Related
Multiple Sorting ?
In my table i've got a column subject, a column content and a date. I'd like to sort the data first by date, then by topic, and then by date gain, if you see what i mean. I would like to group entries that have the same subject together, sort them by date, and inside each group sort by date as well.
View Replies !
View Related
Multiple Sorting
I'm pretty new to SQL and databases, and i've come up with a problem to sort my data. I'm trying to do a basic forum thing. In my table i've got a column subject, a column content and a date. I'd like to sort the data first by date, then by topic, and then by date gain, if you see what i mean. I would like to group entries that have the same subject together, sort them by date, and inside each group sort by date as well. Am i making sense ? Is it possible to do that or am i thinking the whole thing in a wrong way?
View Replies !
View Related
Sorting And Merging
I have a table in the following style : Artnr Detail ----- ------ 110391 grey and 100391 blue color 100392 Green 100393 black 100393 monitor .... So sometimes one article has more details. My problem is, i have to get rid of that and sort the table in this way : Artnr Detail ----- ------ 110391 grey and blue color 100392 Green 100393 black monitor Is there a way to manage this with mysql or does it need php scripting.
View Replies !
View Related
Sorting Numbers
I got a list of movies and would like to select alle the movies starting with a numer 0-10 and another symbol (., #, !, etc.) but I don't know how to get them all in the same query. How do I do that? My query should look like this, but I don't know what to put instead of the ?? "SELECT * FROM movies WHERE title = ?? ORDER BY created"
View Replies !
View Related
Alphanumeric Sorting
I need to sort a column that may contain any combination of letters, spaces, or numbers ... including all letters or all numbers, but will never be empty. "ORDER BY code" doesn't cut it for numbers because "10" would be listed before "2". "ORDER BY (code+0)" works for most options unless the "code" is all letters or contains something like "1A" and "1B" where "1B" would come before "1A".
View Replies !
View Related
Problem In Sorting
I have a table 'PHONE' with column 'price'. I declared it as varchar type. When I select the table sorted by price. It only follows the order as string. For example, when there are price 20, 100, 35. It displays the result as 100, 20, 35, which is incorrect. I want it to show as 20, 35, 100. Does someone know a easy to correct it? (I prefer not to change the data type in the table)
View Replies !
View Related
Sorting Date
Code: SELECT date_format(date, '%b %D, %Y')as Date, time_format(time, '%l: %i %p')as Time, description, prerequisite, availability, location FROM course WHERE 'date' >= current_date ORDER BY date DESC When this is displayed it shows April 14th, April 1st, then April 7th. I imagine this has something to do with the formats?
View Replies !
View Related
Date And Sorting
I made a script that loads info from a Excel sheet into our mySQL database, however, I am running into a problem in the new year. From Excel it saves the date as 01/01/05, and can not be saved any other way from the software it is generated from. So, I made the field it is inserted in the dB a VARCHAR field. Now when I try to sort by date in ASC or DESC, it pulls all of the dates like this: 01/01/04 01/02/04 01/04/04 01/04/05 01/05/04 01/05/04 01/05/05
View Replies !
View Related
Sorting In Order
I would like to know if it is possible to order the results by the quotient of to numbers.For exampel: $sarray=mysql_query("SELECT * FROM table ORDER BY (rating/votes) DESC"); anyone have a clue how to get it to work?
View Replies !
View Related
Sorting By Last Inserted
Is there a way I can sort the rows in a table by last inserted if i don't have an autoincrement id or timestamp. Currently when I do a query on the table its returning the older rows first. I'm using this table for a log, so I took off the autoincrement id since this log is going to be ongoing and will be truncated from time to time to keep it small. When I say truncated, I don't mean the actual TRUNCATE term in mysql. I mean shortened.
View Replies !
View Related
Sorting By Quarter
Got a question about sorting into quarters. I have a news app that displays the quarters current news. I then want to show the previous quarters news in another part of the site (kinda like an archive). I have a date field that I can pull from to sort with just not sure where to start.
View Replies !
View Related
Reverse Sorting
Is there anyway to sort in a different order in mysql than it usually does. Here is an example. m100 m20 Mysql will order them in that order but the problem is that m20 should come before m100.
View Replies !
View Related
Sorting Records
I have a table that holds the monthly returns for different portfolios. The fields in that table are: PORTFOLIOID, DATE and RETURN. A typical record would be 1----31 Jan 2003----2.3%. Another record could be 1----28 Feb 2003----1.5% Another record could be 32----31 July 2002---3.5% etc I need a query that gives me the top 10 portfolios ID order by the compounded return between two dates (these dates should be variants) so that I can ask for the 10 best performing portfolios between say March 2001 and Jun 2004
View Replies !
View Related
Sorting Rows
i have two tables, posts and comments. posts has an id column, and comments has a postid column which refers to the post's id. both have timestamp columns, which are unix style timestamps(time() from php). i want to select * from posts and have that ordered by the highest (most recent) timestamp from either itself or it's comment(s?). basicly, i want the comments to bump the posts. i've been messing arround with left joins and max() with not much luck. if possible, i'd like any extraneous columns needed (if any) for sorting not returned. a spanglish of mysql: select * from posts order by (fuzzy part: most recent timestamp of comment with corresponding postid or , if there are no comments, it's own timestamp) i think i repeated my self, but i'm trying to get the point across. it's post bumping code for a forum that i'm writing. currently i do it in a god awful way using php and lots of loops, and i was hoping there was a faster way to do it in sql.
View Replies !
View Related
Two Types Of Sorting
I have a database which has these colums: id, weith and date f. I want to select some of my datas but in this way. If the diffrecence between today and the date that is stored into database is less then 30 days, then it must sort first by weith and after that by date. If the diffrences is more that 30 days, then it must sort first by date and after that by weith.
View Replies !
View Related
Sorting Is Wrong
I have some products in the database and trying to sort them ascend. by their prices. But for example, the price $14.00 comes before than $8.00. How can I correct the problem? As I understood, mysql only checks for the first digit. Not the whole.
View Replies !
View Related
Sorting Dates
I have a database which I am using to store written articles of text or a Website. I want to be able to offer the user the ability to search for articles in any given month, let's say July 2006 or August 2005. At present my tables consist of fields for - day - month and - year ...each of data type int. I then run a basic php function to find the number of days since 1/1/1970, given these 3 variables. It is this figure which I sort on. Now I know that this is not the best method, so which data type should I be using to make my life easier and my code more efficient.
View Replies !
View Related
Sorting (ORDER BY)
in MySQL are rows with these values: 995 25120 991 99 1132 86 I would like to sort them. But when I tried to use this SELECT * FROM table WHERE something='$something2' ORDER BY numbers DESC result was this: 995 991 99 86 25120 1132 and that's absolutely wrong! Result I want is something like that: 25120 1132 995 991 99 86
View Replies !
View Related
Sorting On Two Columns At Once
I have a table with 2 columns called "lastname" and "company" that I would like to sort. Sometimes the lastname column is null, and in that case I would like to use the company column for the sort. Is this possible? E.g. this data: [lastname, company] smith, amway jones, IBM <null>, microsoft should output the rows in this order: jones microsoft smith
View Replies !
View Related
Grouping / Sorting
However the data is brought back and sorted by month and I was wondering if it's possible to bring back the data the has franid,barid in every month of the year. So right now, as you can see showing the results for the first 3 months, Month 2 has data in the second row I don't want returned and in any calcultions. The franid, barid combo has to be represented in each and every month, in this particular case between Month 1 and Month 12. 1234, try, 7894.89, 1 1456, tre, 7842.78, 1 3526, gft, 5690.00, 1 1234, try, 6794.89, 2 6789, ght, 7842.78, 2 3526, gft, 5340.00, 2 1234, try, 2394.89, 3 1456, tre, 4542.78, 3 3526, gft, 8990.00, 3
View Replies !
View Related
Sorting By Timestamp
I need to sort and echo out the 5 most recently either Added or Modified entries in my table, and am a bit confused as to how to do that. I know the statement goes something like: SELECT * FROM table WHERE timestamp > .... LIMIT 5 Well, I think I know that it goes like that! *scratches head* Also, I know this is not a PHP board, but would you know how I would store and echo those results out into different variables as well?
View Replies !
View Related
DAYOFWEEK Sorting
This is the scenario. I have a radio schedule calendar that is not based on specific dates, but by day of the week. This could either be a numeric value of 0-6 (1-7) to represent the days or it could be varchar fields with the full days spelled out like Sunday, Monday, etc. What I need to do is sort this so if today is Sunday, then Sunday is at the top of the list followed by the rest of the weekdays in correct order. If today was Thursday then it will automatically have Thursday at the top of the list followed by Friday, Saturday, and so on. Is this possible and could anyone provide a solution for such automated sorting?
View Replies !
View Related
Sorting On Date
I want to show a list that is ordered by birhdays, where the person who have birthday soonest will be at the top of the list. Can anyone please give me the syntax for this request.
View Replies !
View Related
Sorting Addresses
I am working with MySQL to output a list of addresses. What I'm trying to do is sort the output by the address, but instead of sorting by the leading numeric values, I want to first use the alpha values and then the leading numeric values. For example, say I have the following records in a table called USERS in the column Address1: 100 Fifth Avenue 48 Fifth Avenue 19 Seventh Street 123 Ninth Street 20 Eleventh Street If I use the command "SELECT Address1 FROM USERS ORDER BY ORDER BY Address1 + 0", the result will be this (since MySQL would sort by the leading numbers): 19 Seventh Street 20 Eleventh Street 48 Fifth Avenue 100 Fifth Avenue 123 Ninth Street What I am instead trying to accomplish would be output that looks like this: 20 Eleventh Street 48 Fifth Avenue 100 Fifth Avenue 123 Ninth Street 19 Seventh Street Essentially, I need an ORDER BY clause that allows me to first sort by the street name and then will sort each individual house number per street name in increasing order. What can I do to accomplish this without creating a separate column for each street address and street name?
View Replies !
View Related
Sorting By Count(*)
how to sort records. I am trying to count the number of occurrences of an item in a column and then sort my recordset using that column. For some reason it works when I try it in phpMyAdmin but not when I send it through PHP. Any idea on what I might be doing wrong?Here is the example; SELECT COUNT(id) AS count, name, [other fields] FROM table GROUP BY name ORDER BY count DESC
View Replies !
View Related
Sorting Question
Is there a quick way to find an item's position in a set of fields using pure mysql instead of php array manipulation. For instance: I have a high score list composed of x number of entries. When a new score comes in I would like to find it's position within the list, and report back something like "you placed 4th out of 100,000 high scores" I can do this using PHP, but it seems like there could be a way to set up a schema to handle this more elegantly and place less strain on the server.
View Replies !
View Related
Repair By Sorting
I have an application which uses MySQL to manipulate large amounts of data for short periods of time. The data set contains 13 columns each of which is indexed and typically about 30 Million rows. I create a new table, load the data in to the table using load local infile from a CSV and then add indexes to each of the columns; the columns are a combination of integer, date and text. The text columns have a simple index and a full text index. I need to be able to sort on each column (in a grid - I use DevExpress) and search on the textual fields. I appreciate that getting the data into MySQL and creating the indexes is never going to be quick but I need to optimise this process as much as possible. Typically a data set would be loaded and viewed for a few hours and then a new dataset loaded and the process repeated. When the index process is underway I get the above state displayed by show processlist and wondered if this is a problem. The whole load/index process takes about an hour and I would like to improve on this if I can. I am also looking for suggestions as to how I may be able to best optimise this. I use a dedicated server and the MyISAM engine.
View Replies !
View Related
Sorting My Results
This is the sql Im using in a recordset using dreamweaver: SELECT racer_name,SUM(points) FROM race_results WHERE `class` = 'gas' GROUP BY racer_name ORDER BY race_results.points DESC Im really new to this but the "SUM" is working fine, but im having a problem with ORDER By function,...its order by "points" which in my DB is a samllint ---- It just needs to sort by that number (points) by largest to smallest,
View Replies !
View Related
Sorting Syntax
This is what I've got so far: SELECT title, date, start_time, yearly FROM calendar WHERE ( ((DATE_FORMAT(date,'%c') >= 4) AND (DATE_FORMAT(date,'%e') >= 19) AND (DATE_FORMAT(date,'%Y') >= 2006) ) OR (yearly = 1)) ORDER BY ????? DESC, start_time DESC LIMIT 10 As may be surmised by my excerpt, I'm using a database to host calendar. In this case I'm trying to retrieve calendar events in order of date, however I have some events within the calendar that occur yearly (yearly=1) that can essentially be a date with any year appended to it. I would like the ordering to be such that my results would occur in chronological order when they are not yearly (yearly=0) and have the yearly events ordered within the result such that they would appear in an order assuming that such events have yet to happen previous to the given date (4/19/06 in this example) and are scheduled within the next appropriate re-occurrence.
View Replies !
View Related
Sorting With Umlauts
I've spent most of today trying to figure out how to get my query to sort correctly. I've read the CHARACTER SET and COLLATE documentation without any luck. I want my data to be sorted like this (it is the correct way to sort in Swedish): a b c z å ä ö With my current settings, however, I get: a å ä ö b c z I have changed the database and tables to the following configuration: mysql> show variables like '%char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ mysql> show variables like '%coll%'; +----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +----------------------+-------------------+ I have also tried to query with collate, like this: SELECT title FROM papers ORDER BY title COLLATE latin1_swedish_ci desc Still, the data is not sorted as I want it.
View Replies !
View Related
Sorting By Two Columns?
Is it possible to ORDER BY one column ASC and then by another within the first order? You know like when you do a data sort in excel, you enter your first column and then your "then by" column and that second sort only takes place within the first? PS: I tried using the search function for this, but was unable to find anything.
View Replies !
View Related
Advanced Sorting
SELECT a.asset_id, CONCAT( a.subcategory_id, '-', a.asset_type_id) AS sort_key, SUM( av.vote ) as rank FROM asset a LEFT OUTER JOIN asset_vote av ON av.asset_id = a.asset_id WHERE a.status = 1 AND a.school_id = " . $SchoolID . " GROUP BY a.asset_id ORDER BY sort_key, rank LIMIT 100 I am working on a query and we would like to pick 100 assets. I would like one of each asset_type for each subcategory sorted by the highest rank. If there are only 3 subcategories and 3 asset types, that means I have 9 assets. If we have picked the "top" asset already it should go through the query again and pick the second one. I need to put 100 assets through, so if we run out, I would like to move to pull more from a different category. Any help from here? I am thinking of trying a group by on my sort_key?
View Replies !
View Related
Query On Sorting
I have a query SELECT atype, id, login, branch_name, contact_person, address1, address2, city, state, country, zip, telephone, fax, url, logo, branch_description, county FROM vendor WHERE id IN (?',��',?',?',��',?',��',��',��',��') If the ORDER BY clause is not present mysql sorts by the PRIMARY ID (id in this case) I want it to be sorted by the ids present in IN (?',��',?',?',��',?',��',��',��',��') any solution.
View Replies !
View Related
Sorting Time Values
I have been trying and trying to properly sort records through a mySQL query. I have hour, minute and AM/PM values stored separately in a database as varchars. I'm trying to concat those, cast them to a time value and sort them. I have found that it sorts some properly but other it does not. The following query: CAST(CONCAT(hour, ':', minute, ' ', ampm) AS TIME) AS myTime ORDER BY myTime Sorts some results like this: 10:30 PM 8:30 PM 9:30 PM But other are perfect: 10:00 AM 11:00 AM 12:00 PM 1:00 PM 2:00 PM 3:00 PM 4:00 PM 5:00 PM These exact results we part of the same recordset. Can anyone help me with this?
View Replies !
View Related
Date Range Sorting
how to return data by date ranges, where I return all data made: Today, (Every individual day of the week up to 1 week ago - eg. Everything for last Monday, last Tuesday etc) Two/Three weeks ago Anything over three weeks ago Also, is this something best done in PHP after a full query or during SQL?
View Replies !
View Related
Unnecssarily Sorting Whole Table
This simple command always takes 0.30 seconds or longer: mysql> select foo from footable order by -foo limit 20; (get the 20 most recent foo's) This however, always takes 0.0 seconds: mysql> select foo from footable where foo>'2004-01-10' order by -foo limit 20; It looks like in the first example, MySQL is sorting the whole table. But the column in question is indexed (confirmed by explain select...). The manual says it should stop at 20 rows because I am using a LIMIT. foo is of type datetime. Any ideas how I can get more 0.00 second queries? Giving a start date is not eloquent, nor can I necessarily give one. It doesn't seem like I should need to, either.
View Replies !
View Related
Sorting Entries And Categorizing
What I am trying to do is make a list however the list has categories for example. Category 1 List 1 List 2 List 3 List 4 Category 2 List 1 List 2 List 3 List 4 Would someone be able to help me with the code? I know it's possible to do this because I've done it before it's just that it was a long time ago.
View Replies !
View Related
Semi-noob At SQL Sorting
I am a php programmer working on a project, and i know some SQL, basically just enough to save data into one and access said data via SELECT commands. I have a table (newsTech) and it has fields "news_id" (type = int, autoincrementing id number/ primary key), "date" (type = timestamp), "Title" (type = text), and "refid" (type = int). As stories are added to the news table the end up at the bottom of the list. I want to sort the table in decending order by timestamp. Like i said, i know very little sql, so if somebody could please help me with the querry, that would help me out so much.
View Replies !
View Related
|
TRACKER
