Order By Giving Two Groups
I have a new database table which contains name and url field. When I order
by name I get two groups, the first being those without an entry in the url
field, the second with an entry in the url field, each grou seperately
ordered correctly.
In a possibly related problem with the same table displaying in a web
browser through php, there are again two groups diplaying when ordered by
name. If a member of the first group is displayed and resubmitted without
change, it then appears in the second group.
I tried doing an export / import to refresh the data, but it didn't help.
Any ideas for causes and or solutions?
View Complete Forum Thread with Replies
Related Forum Messages:
Order Per "Group By" Groups
a table example: Code: ID | USER | VALUE | 1 | mike | 8 2 | mike | 10 3 |mike | 12 4 | john | 10 5 | john | 12 6 | john | 15 Desired result grouped by USER: ID | USER | VALUE | 6 | john | 15 3 | mike | 12 as you can see, I'm ordering the group result by the last value of ID column for each user, then order by user name. In other words, I need to retrieve the max ID value for each user. also order the result by user name. How can I do that ? Myabe there's no need of grouping,
View Replies !
Groups
I have: col1col2 item13 item15 item27 item23 item14 item34 I'm looking for the result: col1col2 item15 item27 item34 --the highest col2 value for data paired with col1 --no duplicates in col1 --order does not matter for either col1 or col2 in the result The SQL I have now is: select col1, col2 from table1 group by col1 I'm not sure how to force it to group col1 using the highest value from col2. Also, I am on MySQL 4.0 so subqueries are out. I'm not sure if this is relevant, but table1 is made by joining two other tables together (one of which is a heap).
View Replies !
Group Of Groups
Basically, I have a simple group query... SELECT `StudentID`, COUNT(*) FROM `StudentHistoryT` GROUP BY `StudentID` The query returns the number of records for each student. What I want is a query that produces a count of the number of students with a distinct number of records, that is, I want to group by COUNT(*) and return the COUNT(*) of the new group. My nieve atmysqlt... SELECT `StudentID`, COUNT(*) FROM `StudentHistoryT` GROUP BY `StudentID` GROUP BY COUNT(*)
View Replies !
Groups For Members
My client wants GROUPS for his members, what I am thinking is creating a table for each group - since each group has a leader ID number and member list. Would this be a good way to do it?.
View Replies !
Two Servers On Windows And My.cnf/my.ini Groups
I run two MySQL servers on Windows with the two services. I read in the docs that it exists a way to identify the servers in my.cnf by groups and about a tool from Linux to manage the groups. How can I start a server on Windows through the my.cnf/my.ini 's groups?
View Replies !
Return Groups For Each Month
I would like to return monthly reports with a single query.For example: January 15 Purchases 5 Refunds 6 Exchanges February 20 Purchases 4 Refunds 2 Exchanges The above data has about 60 records. The query has to group by month and then again by transaction type. Here is what the record would look like TransactionID - primary key TransactionDate - Date Transactiontype - integer
View Replies !
Counting And Ranking Within Groups
(commas are in place to show data seperation and are not needed in the result set) Name Won Julio, 2 julio, 3 julio, 0 Ron, 4 Ron, 2 and the results should be based upon wins sorted descending Name Won Rank Julio, 3, 1 julio, 2, 2 julio, 0, 3 Ron, 4, 1 Ron, 2, 2
View Replies !
Select First X -groups- Of Rows
What I've got is 3 tables: news, category, newscategory. newscategory is just a linker table, so the relationship is like this: [news.newsid] <--> [newscategory.newsid][newscategory.categoryid] <--> [category.categoryid] One entry in 'news' can be attached to many different categories via 'newscategory'. What I want to do is return the first X news posts from 'news', along with all the categories that each post belongs to. I can't figure out how to do this: using an INNER JOIN there will be multiple rows for each news post as there will be multiple categories associated with each post, (one row per category per post) but the LIMIT 0, X clause will apply to all the rows,
View Replies !
Using A Single Sql Statement To Group And Count The Groups
I'm trying to count the total number of items in a table that are grouped e.g. my table is a shopping basket and is like this: basketID||orderID||productID||quantity 1||1||2||1 2||1||3||1 3||1||4||1 4||2||2||1 5||2||4||1 6||3||4||1 so product 4 appears 3 times, product 2 appears twice and product 3 appears just once how do use an sql statement (if it is possible) to group the products then list them in the order of which appears most so i could say SELECT * FROM basket GROUP BY productID; and that would group them for me, but i want them listed like productID 4 2 3 (as 4 has the most occurances, then 2, then 3)
View Replies !
Mysqldump Giving Error
im currently using mysql Ver 11.18 Distrib 3.23.58, for redhat-linux-gnu (i386), for some reason i have problems running mysqldump program, i have tried the followings and they all gave me error, could someone point me out? thanks. mysqldump --opt --user="g8" --password="xxx" --databases="g8" mysqldump --opt --user="g8" --password="xxx" --database="g8" mysqldump --opt --user="g8" --password="xxx" --database g8 g8 is my username and also the database name im using, and xxx is my password.
View Replies !
Mysql Giving Error With '
I have built a php script that inserts data into a mysql table but everytime I try and insert data with an ' it returns an error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's', 'step')' at line 2 in this particular case I was trying to insert the word "dave's"
View Replies !
FULLTEXT Search Giving Me Nothing?
I'm new to MySQL FULLTEXT searching, so I'm hopping this'll be an easy fix. $query = "SELECT * FROM `downloads` WHERE MATCH (`title`,`desc`) AGAINST ('$name') AND level = 1 ORDER BY $orderby"; $query = mysql_query($query); The above query will find nothing when $name = 'ipod', even though I know 'ipod' is listed MANY times throughout the table. I've tried adding "IN BOOLEAN MODE" after '$name', but that gives me an error when trying to run mysql_fetch_array... What gives? On a side note, $query outputs as it should: SELECT * FROM `downloads` WHERE MATCH (`title`,`desc`) AGAINST ('ipod') AND level = 1 ORDER BY os ASC Edit: I found that if I run the following, ALL database rows are returned no matter what the search term: $querytxt = "SELECT *, MATCH (`title`,`desc`) AGAINST ('$name') FROM `downloads` WHERE level = 1 ORDER BY $orderby";
View Replies !
Query Giving Error
when I try to run the following query,I get the error: #1064 - You have an error in your SQL syntax near 'select min(dt_startdate) from tbl_bookings)>��-11-09' GROUP BY p.id_property ' at line 1 query: SELECT p. * , f. * , ph.nme_photo, ph.nme_caption, p.id_property FROM tbl_bookings b, tbl_propertyfeature f, tbl_propertyphoto ph, tbl_property p WHERE b.id_property = p.id_property AND f.id_property = p.id_property AND p.id_property = ph.id_property AND ph.flg_main =1 AND p.flg_active =1 AND p.blnSpecial = 'Y' AND b.dt_startdate IN ( SELECT min( dt_startdate ) FROM tbl_bookings ) > ��-11-09' GROUP BY p.id_property I am using MySql 3.23.58
View Replies !
Query Giving Me Results I Don't Want.
query: SELECT sales_reps.sr_id, sales_reps.order_id, sales_reps.name, sales_reps.job_number, UNIX_TIMESTAMP( shop_orders.date ) AS date, sales_reps.stage, sales_reps.status, pi.fname AS pfname, pi.lname AS plname, sales_reps.date_to_shop, users.builder, users.division, shop_assignments.editor_id, shopper.fname AS sfname, shopper.lname AS slname, ei.shop_result, ei.good_tape FROM sales_reps LEFT JOIN shop_orders ON shop_orders.order_id = sales_reps.order_id LEFT JOIN users ON users.user_id = shop_orders.builder_id LEFT JOIN shop_assignments ON shop_assignments.sr_id = sales_reps.sr_id LEFT JOIN users AS shopper ON shopper.user_id = shop_assignments.shopper_id LEFT JOIN users AS pi ON pi.user_id = shop_assignments.pi_id LEFT JOIN exit_interviews AS ei ON ei.shop_id = shop_assignments.shop_id WHERE sales_reps.status = 'Rejected' AND ( ei.shop_result != 'Trip x1' OR ei.shop_result != 'Trip x2' OR ei.shop_result != 'Non-Reg Trip x1' OR ei.shop_result != 'Non-Reg Trip x2' OR ei.shop_result != 'Office Closed' ) ORDER BY users.builder, users.division, shop_orders.date DESC , sales_reps.stage ASC It's not filtering out the records with the shop_result field matching what I've listed.
View Replies !
UPDATE Two Fields Giving Error
I'm having problem with an UPDATE syntax. I want to update 2 fields from a certain record in a table. The following syntax works: UPDATE items SET price='30' WHERE num='1' BUT When I try to update 2 fields using the following syntax I get an error saying that my syntax is wrong. UPDATE items SET desc='cheese',price='30' WHERE num='1' What's wrong with it?
View Replies !
Mysql_config --cflags Giving The -g Parameter
I've installed mysql using binaries and yum. I'm trying to compile our server when using mysql_config --cflags says I should use the -g option. I do NOT want to use it our server application is much more slow if it's compiled with debug options. I've tried to get infos on why it returns this and it's probably because I don't know the subject too well to know what keywords to look for but I really can't find anything on why it returns which flags and how to configure it.
View Replies !
Giving Permissions To Hosts On Subnet
I would like to give permissions to a user that comes only from hosts on our subnet. So any host that is coming from 24.xx.xx.209/28. so, for instance(from the MYSQL docs): GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' -> IDENTIFIED BY 'some_pass' WITH GRANT OPTION; I would like monty to have access only when he logs into a db from the network above. Is this possible or do I have to create an entry for every machine that I want this for?
View Replies !
AutoIncrement Giving Duplicate Numbers
Has anyone ever heard of problems with an auto-increment field giving duplicate numbers to 2 threads that might hit the DB at exactly the same time? The reason why I ask is because during our transition from MySQL to Oracle, the Oracle guys here said that we had to create a SEQUENCE object in Oracle to ensure that this didn't happen. Now that we're back to MySQL, I just want to make sure this isn't an issue with an AUTO_INCREMENT field.
View Replies !
Update And Select Giving Error
I have an address database "address", and I need to update the field "street" row by row for some reason, then when I use update (select street from address limit 0,1) set street="High Road"; I have a error nor update address set street="High Road" where Street= (Select Street from address limit 0,1).
View Replies !
Subtracting Two Columns Not Giving Negative Result
I now have a caching table which has club_id | date | rank so by left joining on the rank for last week to the rank for this week I can show name of club | current rank | last weeks rank now I want an extra column which is the move between this week and last week. I am using this SQL: SELECT *, (dt.lastweek - dt.thisweek) AS move FROM (...) AS dt But it returns the following result: ...
View Replies !
LINES TERMINATED BY '' Giving Lower Case N
I’m trying to write a table to a delimited file readable by excel. My query is: SELECT * INTO OUTFILE 'C:/path/filename.txt' FIELDS TERMINATED BY '=' ESCAPED BY '' LINES TERMINATED BY ' ' FROM tablename However, instead of a new line where expected, I get one giant line with a lower case n where I expect the line breaks to be.
View Replies !
Fulltext Search Not Giving Exact Results
How do i get fulltext boolean search to locate results based on partial words. For example i am searching for +protein +bar but i also want results for anything that contains the word bar such as bars PHP $keywords = $_GET['query']; // add + to the first keyword $keywords = '+' . $keywords; // replace ' ' with ' +' so you add a + to every keyword $keywords = str_replace(' ', ' +', $keywords); $query = "SELECT products_id as prodid, products_name as name, manufacturers_id as manufacturers_id, products_price as price, products_msrp as msrp, products_date_added as pda, products_status as status, products_sku as sku, products_weight as weight, MATCH(products_name, products_description, products_keywords, products_notes) AGAINST ('$keywords' IN BOOLEAN MODE) AS score FROM products WHERE MATCH(products_name, products_description, products_keywords, products_notes) AGAINST ('$keywords' IN BOOLEAN MODE) ORDER BY score DESC"; $result = mysql_query($query) or die("Error in $query!"); OUTPUT: SELECT products_id as prodid, products_name as name, manufacturers_id as manufacturers_id, products_price as price, products_msrp as msrp, products_date_added as pda, products_status as status, products_sku as sku, products_weight as weight, MATCH(products_name) AGAINST ('+protein +bar' IN BOOLEAN MODE) AS score FROM products WHERE MATCH(products_name) AGAINST ('+protein +bar' IN BOOLEAN MODE) ORDER BY score ASC
View Replies !
Giving Database User INDEX Permissions
Just setting a few database users in cpanel, I see that one option I can grant is INDEX. And I found this in the cpanel documentation: INDEX (Allows USERNAME to create index on table column in the database. This can speed up a database.) I won't be granting alter permissions, which is what I thought would be needed to add a permanent index? I'm wondering what exactly is meant by this? Does it mean, that they have access to an index key that already exists? Presumably mysql allows the word INDEX in the query that maybe replicates the above? In which case, giving a user INDEX permission means letting them create a "temporary index" in the query?
View Replies !
Trigger Giving Error 1442: Can't Update Table
I've made a table: create table testneed( aID int(10) not null auto_increment primary key, a int(10), b int(10) default 0 ); then I made a trigger: delimiter // create trigger needtest before update on testneed for each row begin update testneed set b=round(a/23); end; // delimiter ; insert into testneed(a) values(0); update testneed set a=350 where aID=1; and then I recieve the error: ERROR 1442 (HY000): Can't update table 'testneed' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. I've tried seting be in the trigger statement as update testneed set new.b=a/23; and I've tried make the trigger an insert as well as excluding begin and end from the trigger, but each time I recieve the same error.
View Replies !
DEFAULT CHARSET = Latin1 Giving Error On Upload
I have to import the database from local server file to remote server Issue #1: - DEFAULT CHARSET = latin1 database from local server when uploading to remote server through phpmyadmin I get this error MySQL said: Documentation #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=3' at line 37.
View Replies !
INSERT Into Table2 SELECT Fields From Table2 Giving Inconsistent Data
I have two tables ia_raw_mo and ia_intermediate_mo.The ia_raw_mo table has extensive data , so I use the select group by on this table and insert the data into the ia_intermediate_mo table using "insert into select table" clause The query is : insert into ia_intermediate_mo SELECT days, hours, bannerid, zoneid, ifnull(channel,'') as channel,ifnull(os,'') as os, ifnull(browser,'') as browser, ifnull(countryid,'') as countryid, ifnull(cityid,'') as cityid , ifnull(bandwidth,'') as bandwidth,count(*) as mouseovers FROM ia_raw_mo where days='2007-12-14' and hours >= 8 and hours < 9 group by bannerid,zoneid,channel,os,browser,countryid,cityid,bandwidth,days,hours; the ia_raw_intermediate has 9825 records and after correct execution of select and group by on this table, I should get 45 records in ia_intermediate_mo table. But the above command is giving me 9825 records sometimes(around once in 100 times).So in that one particular case,there is no error message thrown but the group by is not properly executed and the query returns exact numbers of records , as if no group by was there What I am thinking is,that there may be some problem in query plan which mysql creates, because the same query is also returning corrent result most of the time.But I am not sure about this or is there any possible flaw in the above query. Even the mysql query log is also showing the same query been executed and returning 9825 records.
View Replies !
Mysql Is Giving The Previous Month, Not This Month, Strange!
PHP Code: $myquery = @mysql_query("select year(sendon) as yr, month(sendon) as mth, count(*) as hits from ebook_user group by yr, mth"); while ($myrow = mysql_fetch_array($myquery)) { $mymonth = $myrow['mth']; $myyear = $myrow['yr']; $myhits = $myrow['hits']; print '<td bgcolor="#E7E7E5">'.date("M", mktime(0, 0, 0, $mymonth, 0, $myyear)).' '.$myyear.' </td><td bgcolor="#E7E7E5"> '.$myhits.'</td>' } The above code is printing the previous month instead of current month ... can any one help?
View Replies !
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 !
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 !
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 !
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 !
|
TRACKER
