Quesion On Ordering In A GROUPED Query
Got a question about grouping and ordering. What I am trying to do is:
a) Group by a criteria by pick which result I am getting as part of that group
b) Order the list of groups.
Lets say I have a list of emails in the form:
| id | timestamp | author | subject
Now, I am trying to generate a list of the latest email subjects per author, ordered by timestamp.
So I want to do:
SELECT * FROM table GROUP BY author ORDER BY timestamp
The problem with that, is it dosen't allow me to choose which result I am getting as part of the group, so how do I do that?
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Ordering Grouped Results
I'm trying to get a result ordered and then grouped together but it is coming out in the same (not in alphabetical) order that it comes out with without the order. Am I doing this wrong or is it not possible? mysql> select GROUP_CONCAT("::ID",item.ID,"::NAME",item.Name SEPARATOR '') from item inner join item as Player where item.Container=Player.Container A ND Player.ID=76 AND item.ID!=Player.ID order by item.Name ;
Using Limit On Grouped Query With Large Number Or Records
I have a query that use a "group by" clause that returns 600+ queries however if I add a LIMIT 500,50 It returns 0 records. I tested LIMIT 450,50 and it returned only 34 records. Is there something limiting the limit? Is there an option in mysql that can increase this limit?
Ordering Query
i have a query were i need to select the top 250 by a number but i want the result to be ordered alphabetically by a different column. i can't figure out how to do this because i need to order by the numerical value to select the top 250. for example: Code: SELECT the_tag, count(distinct(article_id)) as cnum FROM tags GROUP BY the_tag ORDER BY cnum desc limit 250 in the above example i get the top 250 but they are order by cnum for the purpose of selecting the top 250. how can i modify this query to still select the top 250 and also have the results ordered alphabetically by 'the_tag'?
Stop Query Ordering
select ... FROM ... WHERE ... IN ('2', '1', '7', '3', '17') I have a query in the same format as above, where I want to select only certain items from from a table based on there ID. The problem I have is that MySQL then orders the results based on this ID (I presume this is because it is the index). What I want to happen is the results to be returned in the same order as the query... is this possible?
Slow Query W/ Join & Ordering
I am trying to figure out why I have a hugely slow query (~2 seconds in my testing environment). Details are below: It involves two tables, products and vendors. Products is a huge table, so I will only include the (ostensibly!) relevant fields in its description: CREATE TABLE `products` ( `id` int(11) NOT NULL auto_increment, `vendor_id` smallint(6) NOT NULL default Ɔ', `product_code` varchar(255) NOT NULL default '', `internal_name` varchar(255) NOT NULL default '', `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `product_code` (`product_code`), KEY `vendor_id` (`vendor_id`) ) ENGINE=MyISAM; Vendors are much more straightforward: CREATE TABLE `vendors` ( `id` smallint(6) NOT NULL auto_increment, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM; The following query executes in no MORE than 0.01 seconds: SELECT DISTINCT p.id , p.product_code , unix_timestamp(p.lastmodified) as lastmodified , p.internal_name FROM products as p ORDER BY p.product_code ASC LIMIT 0, 30; And has the following attributes: +----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+ | 1 | SIMPLE | p | index | NULL | product_code | 257 | NULL | 25124 | Using temporary | +----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+ When I join with the vendors table, so that I can fetch the vendor's name for each product, I use the following query, which takes about 1.88 seconds: SELECT DISTINCT p.id , p.product_code , unix_timestamp(p.lastmodified) as lastmodified , p.internal_name , v.name as vendor_name FROM products as p LEFT JOIN vendors as v ON v.id=p.vendor_id ORDER BY p.product_code ASC LIMIT 0, 30; It has the following characteristics: +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+ | 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary; Using filesort | | 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+ Note the addition of the filesort. I'm unhappy enough about the temporary, which I don't really understand, but the filesort is, I'm fairly sure, killing me. Closer investigation (or maybe just common sense if you aren't a MySQL newbie like me) shows that the ORDER BY clause is responsible, for when I join without the ORDER BY, my query time goes back down to 0.01 seconds or so: mysql> explain SELECT DISTINCT p.id -> , p.product_code -> , unix_timestamp(p.lastmodified) as lastmodified -> , p.internal_name -> , v.name as vendor_name -> FROM products as p -> LEFT JOIN vendors as v ON v.id=p.vendor_id -> LIMIT 0,30; +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+ | 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary | | 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+ Any clues on how I can get the execution time to go down when I am sorting? I'm also curious why MySQL is using a temporary table,
AVG Across Grouped Column...
I have a MySQL 3.x table where each record is a visitor "page-view" on my site. Let me state, i do NOT want to place the values into temp tables or anything like that, i want a straight-forward query which calculates what i need. By doing a SUM and grouping on the visitor_id, i get each person's total number of page-views on the site. if i combine that with a WHERE clause checking the request_timestamp, it narrows it down so that i have info only for one particular day, whichever one i specify in the query's WHERE clause. 1. what I want to do is find out the MAX() of all those hit-totals (calc'd by doing the SUM). I know that I could just do the above described select, and do a DESC order-by on the total-hits and take the top (first) record. But, is there a more straightforward way of doing this? 2. more over, i want to find the AVG() of all those hit-totals. I can't seem to figure out how to do that via one SQL statement.
Average Of Grouped Fields?
SYSTEM MOUNT KB_USED KB_TOTAL host_1 / 1000 30000 host_1 / 1500 30000 host_1 /apps 1092 234343 host_1 /apps 7266 234343 host_2 / 4598 553444 host_2 / 4545 553444 host_2 /opt 3435 435345 host_2 /opt 4534 435345 What i want is, something like this SYSTEM MOUNT AVG(KB_USED/KB_TOTAL)*100 host_1 / 4.166 host_1 /apps 1.78 host_2 / 0.82 host_2 /opt 0.91 How do I do this? Basically I want them grouped by mount per system.
Display Grouped Field Only Once??
If I use Select citycode,monthname, count(member) from members group by citycode,monthname I am getting following results Citycodemonthnamecount dlJan5 dlFeb10 dlMar15 muJan5 muFeb10 muMar15 is there any way so that I can get the result like this: Citycodemonthnamecount dlJan5 Feb10 Mar15 muJan5 Feb10 Mar15
Choosing A Particular Row From Grouped Rows
I have a table which contains two strings and a date. page_views ----------------- sessionId page timestamp This is added to each time someone views a page on our site. I want to select the last page each user looked at, so I need to group on the sessionId whilst getting it to select the one with the highest date timestamp.
Grouped Columns From Multiple Tables
I am trying to write a query to get the total records per user id from 2 different tables. The tables are posts and topics. Each table has a numeric user_id and a numeric forum_id that these records relate to. I'm trying to write a forum usage report with output like this: Code:
News Archive Grouped By Date.
My website has suddenly started to generate a huge amount of traffic so I have begun to post alot more news articles. At the moment only the first ten articles are showing so what I need is a news archive. I need a method to query the database to show each Month-Year which is linked to a page that SELECTS only the articles from that specific month/year. Example January 06 February 06 March 06 .. Then each month would be hyperlinked month?=$month. What I would like your suggestions on is how I would go about SELECTing all the news archive dates (Month/Year) from the database but only once. So when we star the month of June, and I post an Article with the date of June, June 06 would then be in the page. Here is my table: id-date-title-article-image_caption-image_name:
Ordering
I've got a few 'course' records with titles and dates. The result set should be ordered like this: title4|29.10.2007 title2|03.11.2007 title2|07.11.2007 title3|04.11.2007 title1|20.12.2008 So, the records should be grouped by title and *within* the same group they should be ordered descending by date (title2 in example). However, overall the first record should be the one with the closest date (i.e. date descending). The fact that title3 comes third, is obvious then (I think) because its date is "higher" than the closest title2 record.
Ordering A NOW() Column?
I have built a web application that allows users to input data into the database, and each entry is marked with the date and time in its seperate DB column using the NOW() mysql function inside the INSERT query. But now that it has passed new years... I've noticed that all new entires in 2007 are being placed at the bottom of the sort order when SELECT'ing all records from the DB and using ORDER BY now_col (which is the NOW() column). Why is this happening? Is NOW() not a good function to use to record the date and time of a DB record insert? What can I do to fix this or is there another solution? Add-In: The column type is DATETIME
Selecting And Ordering By AVG?
each image has it's own entry in my "images" table ie. images.images_id, images.title, images.description, images.filepath every time a user votes on an image it creates an entry into the "vote" table ie. vote.vote_id, vote.images_id, vote.vote_given (the rating given in 1-10 format) What i'd like to do at the end of the voting session is grab the images from the "images" table, in order of the average vote given to them from the "vote" table. Thanks in advance!
Changing The Ordering
Sadly, this isn't one that can be answered simply by changing ORDER BY field DESC to ORDER BY field ASC. What I need to do is to be able to display a list of items in an arbitrary order that can be changed, e.g., A B C D might need to change to C B D A I'm thinking that the best way to approach this would be to add another column sort_order that contains integer values which could then be used in an ORDER BY clause on my SELECT statements. Anyone else have any better ideas? I've done it this way before and it does work, I'm just wonder if there's some utterly brilliant solution out there that doesn't require additional metadata.
Fulltext Ordering
SELECT * FROM article WHERE MATCH(name) AGAINST('$myvar' IN BOOLEAN MODE) ORDER BY datestamp DESC LIMIT 1 The problem I am having is that it's not ordering the results by datestamp whatsoever, it's still ordering them by the fulltext score.
Ordering Of Data.
In the following query "SELECT * FROM table_name WHERE account_num>100 AND account_num<500 AND sales_num>1000 AND sales_num<5000" What order does the query return the records in? Also is there a way to set it to return all account_num that are between 100 and 500 for sales_num 1000 and then return all account_num that are between 100 and 500 for sales_num 1001 and so forth all the way up to sales_num 4999?
Ordering Values
How do i order numbers retrieved by there value, such as: 1,2,3,4,5...12,13...22 so its like that instead of 1,11,12,13,14,2,21,22,23,24,31,4,5,6...
Grouping And Ordering
SELECT q.q_id, q.user_id, IF (e.firstname IS NULL , q.name, e.firstname) AS name, t.town, e.type, e.trading_name, e.posts FROM qa_uk q LEFT JOIN ayn_experts e ON e.unique_id = q.user_id AND e.active = 1 LEFT JOIN ayn_towns t ON t.id = q.town_id WHERE q.active =1 AND t.town IS NOT NULL GROUP BY q.user_id, q.name, q.town_id ORDER BY q.datetime DESC This query runs, however the field that comes back from the aggregate functions for the q fields is the first field in the database matching all others, how do i make it the last? --BB
Ordering By With More Specifics
Order by subname will order Chapter 1, Chapter 10, then Chapter 2. Is there a way to order them in a more appropriate fashion such as Chapter 1, Chapter 2, then Chapter 10? ORDER BY subname;
Complex Ordering
I would like to order query results by a numeric column containing positive numbers and zeros at a descending order, except that results with a zero 0 should appear last (e.g.: 2, 10, 15, 16, 0). Can I do that in a single query, or do I need to use to successive queries or order my results in the programs that receives them before displaying them?
Ordering By Value List?
I want to use a list of values for a field as the basis for a query (so far, so good - that's "WHERE col IN (comma-delimited list)"), but I want to get the results in the same order as the list of values. Is there a way?
Ordering By Company Name
I have the following PHP/MySQL code I found on a website for sorting company names alphabetically and those in the past 30 days: $result = mysql_query("SELECT quotes_needed.company FROM quotes, quotes_needed WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= quotes.date_initiated ORDER BY SUBSTRING_INDEX(quotes_needed.company, ' ', 1) ASC", $sql); while($row = mysql_fetch_assoc($result)) echo $row['company']."<br />"; It partially works. The sorting works fine but the issue is that it is dumping out 14 duplicates of each quote entry. For example, 'Acme Corporated' would be listed 15 times, then 'Bowman Industries' would be listed 15 times, then 'Chef Chu' would be listed 15 times, and so on. It should only list each quote entry once. The only thing I can figure is that it has something to do with the MySQL query.
Ordering Queries
I am running a MySQL query using product ids like this: SELECT * FROM products WHERE product_id=510 OR product_id=15 OR product_id=76 Sometimes there can be a string of up to 100 product IDs in 1 query. The problem is, I want the results to be displayed in the order that I am listing the product_ids. In other words, I want the results to be displayed in the order 510, 15, 76. However, when I run the query, the results are displayed in a different order such as 15,76,510. How can I get the results to be displayed in the order that I have passed the IDs in my query?
Ordering Results In Php
The code for displaying such holidays is: <a href="availableproduct.php?region=fiji"> Region is one of the fields in our database. Now here comes my problem. We have been asked to order the holidays for one region - i.e. Fiji, so that when they are displayed anything to do with Mana Island comes up first. Mana Island is not a region on its own and therefore is not a field. It will only be text in the title and/or description fields. Is this at all possible or no because it is not a region.
Ordering/grouping?
Say I have two fields of consequence in the same row for this operation, GUEST (varchar) and DATE (timestamp). I want to make a select where all of the identical GUEST entries are grouped together, and have their DATE be the secondary ordering criteria. Additionally, I want each set of GUEST entries to have considered their highest number DATE entry to be the criteria by which to judge how high in the returned array it should appear. little example - Note that i want the name with the highest timestamp to be first, followed by that same name with lesser timestamps (in descending order.) sam 20041005000030 sam 20041005000025 sam 20041005000020 sam 20041005000010 dan 20041005000020 dan 20041005000015 dan 20041005000010 dan 20041005000005 ian 20041005000010 ian 20041005000005 ian 20041005000002 ian 20041005000001 I realize this might take a dash of php to get done, but I know some of you guys are pretty slick at cookin up the queries, so I figure I'll let you have a go at it. :thumbsup:
Ordering A Select
When I issue a SELECT statement without ORDER BY clause, MySQL returns the rows, from the first one to the last one. Is it possible to reverse this result, that is returning the rows from the last one to the first one?
Ordering By One Collum
I want to make a MySQL query and have the results ordered by breed and THEN by regname. So basically I want it to put dogs of the same breed together and order them within their breeds alphabetically by their regname.
Ordering WHERE's Clausules
I got something like: select fields from table1 where ( (something1 like '%somethin%') OR (something2 like '%somethin%') OR (something3 like '%somethin%') OR (something4 like '%somethin%') ) I want to have the results ORDERED by first, the results of first clasule (something1) then about results of something2...and so on
Mysql Ordering
I am trying to order a plist column ASC Order But Im getting this: Plist: Plist: Plist:12 Plist:2 Plist:21 Plist:3 Plist:31 Now I was thinking this might be because my column is Varchar(255) SO I ran this: ALTER TABLE `moulds` CHANGE `plist` `plist` INT( 11 ) NOT NULL Now by some odd reason my Default Value gets set to "0" When I check My structure : plist int(11) No 0 I cannot edit that 0 out with phpmyadmin? Even if I try update. Even if I do how can I get the empty plist entries to go at the end of the result?
Ordering Of The Result
I have a table which stores information on people One of the fields that I store for each person is his nickname which is varchar(255) I notice that I have many records in the database where the nickname has a value that starts with '!'I would like to be able to sort the data in such way that entries that start with '!' will be displayed last and all the other entries will be displayed in their correct alphabetical order.Is that possible?
Ordering Certain Fields Last
I have a search query: select * from mgi WHERE activate='1' && mgi_name Like '%".$search."%' ORDER BY mgi_name ASC This list all the business names for a search. I have another column in the table called "placement" placement has either a Yes or No value. How can I change this query so that all the rows with placement "No" is listed last
Ordering By Most Results
In my database I have a table called imagebank and each row has field with the URL to a picture and I field that says what breed the picture is of. How can I make a query to my database that pulls all the info from that table, and displays them in order of what breed has the most pictures.
Ordering Output
I originally posted this in the PostgreSQL area but it didn't get a response so I thought I'd post it here since the MySQL area gets more traffic (and it's sort of a general SQL question anyway). I have a query: SELECT new_id FROM list WHERE old_id IN (132, 147, 339...lots and lots of id's...498, 938, 372); I would like the output (new_id) to be ordered in relation to the old_id's (as listed in parentheses), but instead the output just seems to be in some arbitrary order.
Ordering Problem
I have two tables, one containing account details and the second containing the account history. I'm trying to pull the date the account was last updated along with the account details in a single query; SELECT a.id, a.contact_name, a.username, a.status, b.timestamp FROM accounts a, history b WHERE (a.id = b.account_id) GROUP BY username; The only problem with this query is that it returns the timestamp of the first history record, rather than the last history record for that user. Is there a way to incorporate some sort of 'ORDER BY b.timestamp' prior to the 'GROUP BY' ?
Ordering Times
i have some vlaues in a database, they are 12 hour times, how would i go about sorting it so 12 comes before 1, as it is its going 1 2 3 4.... 12 etc etc, do i have to use a 24 hour time? then from there function it to show 12 hour when displayed
Ordering Results
If i have a list of titles (ex. movie titles). What would be the best way to order these results without the preceding word "the". Would I have to use a case statement or something. After reading the mysql manual some more..I came up with this. select * from titles order by IF(INSTR(title, 'the') = 1, substring(title from 5), title) Its wrong i Know, but am I even close?
Ordering Results
I got a problem with order by: I need to order alphabetically a list of strings, the problem is that many of them start with a number and I want to display first the ones beginning with letter How can I do?
Ordering A Count(*)
Code: "select country, count(*) as no from Customers group by country order by count; well i am trying to order by count so the result will be eg: Britain 1 Wales 2 ireland 3 etc
Ordering Hierarchical Data
I've got a "questions" table in my MySQL database. Users can ask questions about certain items and post "replies" to previous questions, so there can be unlimited nesting. Here's the current table set up: TABLE questions: question_id INT(11) AUTO_INCREMENT question_text TEXT question_parent INT(11) DEFAULT 0 question_level UNSIGNED TINYINT(2) DEFAULT 1 user_id INT(11) item_id INT(11) A root level question will have level 1, the first reply will be level 2, and so on. The "parent" will be the question to which is replied, or zero if it's a root-level question. I need to do this, except I don't know what should replace the question-marks: SELECT question_id, question_level, question_text FROM questions WHERE item_id = $item_id ORDER BY [???] I need it to grab in order of the questions, with nested replies in order. (I hope that makes sense.) In case it doesn't, here's some made-up sample data: question_id question_parent question_level 1 0 1 2 0 1 3 2 2 4 3 3 5 1 2 6 0 1 That should be ordered {1, 5, 2, 3, 4, 6}. To give you a visual diagram, it would be like this: +--- 1 | | | +--- 5 | +--- 2 | | | +--- 3 | | | +--- 4 | +--- 6
Ordering Based On Occurence?
I have this part of my query SELECT DISTINCT country FROM traffic_outgoing and I want to limit it to the top 10 countries that have the highest number of occurences. How would I do that?
Trouble Ordering By An Alias
Probably something silly here... why am I unable to specify my custom ordering? SELECT CASE WHEN temp.`time` < 11 THEN Ɔ-10 seconds' WHEN temp.`time` >= 11 AND temp.`time` < 31 THEN ཇ-30 seconds' WHEN temp.`time` >= 31 AND temp.`time` < 61 THEN ཛ-60 seconds' WHEN temp.`time` >= 61 AND temp.`time` < 181 THEN Ƈ-3 minutes' WHEN temp.`time` >= 181 AND temp.`time` < 601 THEN Ɖ-10 minutes' WHEN temp.`time` >= 601 AND temp.`time` < 1801 THEN ཆ-30 minutes' ELSE ཚ+ minutes' END AS `visitLength`, COUNT(*) AS `count` FROM (SELECT MAX(`time`) - MIN(`time`) AS `time` FROM test_log GROUP BY `visitorId`) temp GROUP BY `visitLength` ORDER BY FIELD(`visitLength`, Ɔ-10 seconds', ཇ-30 seconds', ཛ-60 seconds', Ƈ-3 minutes', Ɖ-10 minutes', ཆ-30 minutes', ཚ+ minutes'); ERROR 1054 (42S22): Unknown column 'visitLength' in 'order clause' Without an ORDER BY, I get the rows I want, without the ordering of course: +---------------+-------+ | visitLength | count | +---------------+-------+ | 0-10 seconds | 3823 | | 1-3 minutes | 796 | | 10-30 minutes | 394 | | 11-30 seconds | 430 | | 3-10 minutes | 682 | | 30+ minutes | 666 | | 31-60 seconds | 236 | +---------------+-------+ 7 rows in set (0.23 sec)
Pulling Count And Ordering
I would like to list the top 10 people with the most refferals. The amount of players somebody has reffered isn't actually stored but who each player is reffered by is. For example:
Ordering The Data In My Database
I've got a database with a list of teams that are part of a league. I want to be able to order the list by the number of points. I've currently got this set up, but unfortunately it orders it in ascending order i.e. 0 is higher than 12. Could someone tell me the code that i need to reorder the data in descending order?
Ordering Fulltext Results
I'm new to fulltext searches and have a question. I need to do a fulltext search against 2 fields, but order the results so that the results that match field A come before field B. Is there a way to do this? Will it just happen?
Ordering By Date Using UNION
I have a series of data that is entered for a given day with a date attached. I'd like to create a page that shows the sum of the data for a series of given periods (not specific to a single year.) I was able to get this working by using the UNION command. The problem is this. I was only able to get this to sort of work by doing this: SELECT SUM( statabase.gm_date ) AS Season, SUM( statabase.games ) AS Games_Played, SUM( sta... FROM players LEFT JOIN statabase ON statabase.player_id = players.player_id AND statabase.gm_date BETWEEN '2001-09-01' AND '2002-06-30' UNION and then a similar thing with the dates changing. The problem is by using SUM on the date I get some funky useless number. These numbers will never be consistent. What is the right way to do this? Without doing SUM on the gm_date, I got a list, which is nice but not what I want at the moment.
Ordering An ORDER BY Statement
in an ORDER BY column ASC is there any way to give higher priority to letters than numbers so instead of returning 01abc it would give abc01
|
TRACKER
