Creating An A To Z List From Query
I am creating an a to z list - basically a count of all results that
start with the letter "A", "B", "C" .... and so on.
I am pretty poor at SQL so I am sure some brains out there can do
better than I have here. What I have is working, I just want to make
sure that it is optomized.
So let's assume I have some query "$query" that I want to run and get
an A..Z list based on column "$column".
Let's further assume that '$query" produces the following results, and
that $column is equal to "last_name".
last_name
---------------
Anderson
Bitmore
brown
Bogus
My AZlist query would look like this:
select * from
(SELECT count(alist.$column) as a from ($query) as alist where
alist.$column like 'a%' or alist.$column like 'A%' ) as a_result,
(SELECT count(blist.$column) as b from ($query) as blist where
blist.$column like 'b%' or blist.$column like 'B%' ) as b_result,
....
(SELECT count(zlist.$column) as z from ($query) as zlist where
zlist.$column like 'z%' or zlist.$column like 'Z%' ) as z_result;
And this retuns the following result:
a | b |...| z
--------------
1 | 3 |...| 0
Meaning that $query has 1 result where the first letter in $column is
"A" or "a", 3 results where the first letter is "B" or "b" and 0
results where the first letter is "Z" or "z".
What I am afraid of here is that "$query" is being executed 26 times
(once for each letter of the alphabet) . Is there a way to refine
this, or is MySQL (4.x and 5.x) smart enough to optomize this on its
own?
View Complete Forum Thread with Replies
Related Forum Messages:
Creating List Of People Not On List
I am using the following query to try to get a list of people not in a specific list. I can further restrict it by company name but I am getting duplicate entries if the person is listed on multiple lists. (It is further reduced by a clause to restrict by company, but that is not causing errors AFAIK) SELECT people.`First Name`, people.`Last Name`, people.PersonID, Company FROM people LEFT JOIN listtrack ON people.PersonID=listtrack.Person_ID WHERE (listtrack.List_ID<>4 Or listtrack.List_ID Is Null) If I remove the List_ID<>4 clause then I get people who are not on any list., but I want to be able to add one person to multiple lists, without running the risk of data duplication.
View Replies !
Creating A List Of Repeated Search Words, Advice?
I store the search terms people have been using for the last year in a mysql database. (migrated to Mysql 5 recently). Now I want to try and find out which terms have been used the most, in order that I can try and understand what users might be looking for. I have no clue what word describes this kind of self indexing, or how to start doing this... Can anyone give me a pointer on where to start looking? More: I also stored a datestamp, so would like to know month by month what were the search terms used. Heres a snap of the meta: Table : search_terms search_ID int(11) auto_increment daydate int(11) term varchar(40) Only has 135k rows.
View Replies !
Top List Query
Currently im trying to query a data which will only display the top rows. For example: to display top 10 rows from the recent entered data.
View Replies !
Top 10 List Query Question?
I need a little help with my top 10 best rated query. Right now I have: $sql = "SELECT * FROM tblPix, tblVoting WHERE tblVoting.picID = tblPix.picID ORDER BY picRating DESC LIMIT 10"; This one works but not as I want to. I want to take into account the num of votes. The tblVoting has a field called numOfVotes. So a rating of 5 with 10 votes will be above a rating of 5 with 6 votes.
View Replies !
Query To Get List Of Cocktails Given Ingredients
I'm trying to write a query to get a list of drink recipes which have one or more of the selected ingredients.... so, in a search form I want to be able to choose 1 or more ingredients and have sql return all drinks with that combination of ingredients. I have 3 tables (only relevant fields shown here): tDrinks -drinkid -drinkname -drinkdesc tIngredients (not needed in the query but used elsewhere) -ingredientid -ingredientname tDrinkIngredients -drinkid -ingredientid -qty so, I can get a list of drinks given one ingredient: Expand|Select|Wrap|Line Numbers
View Replies !
Getting Rows Multiple By List Query
I have a list of values (constants) that I use to find matching rows in a single table as follows: "select id, name from articles where id in(1,2,3,2,3,2,2)" seems very simple, but my problem is, the query delivers only 3 lines, of course, because the last four numbers are redundant. But my need is, to get a row for each number in the list, wheather it is doubled or not.
View Replies !
Query Command For Linked List
I am working on a data base and using mysql. The following three tables are being used and want find the Name of the Domain from the Domain List which are equivalent. Like to inform you that a linked list conception is being used in the Table DomainEquivalentList. Could you please give some clue for the query command....
View Replies !
List The Number Of Records In Query
I have a very simple query. I want to list a bunch of names & how old they are. (two columns) I want to add a third column that basically counts how many records there are. Example: 1. Bill Jones - 55 2. Bob Smith - 56 3. Steve Gates - 25 Can I do this in mysql or should I add the code in PHP, and how do I do it?
View Replies !
Batch Query Or Large List Of IN() Arguments?
I'm receiving a potentially large list of productID numbers from an external data source. Each productID has a number of partID's associated with it and those partID's are stored in my database. I would like to create a list of products sorted by the number of parts each has. If I only have one table called "part" which stores a partID and its associated productID, which of the following methods would be more efficient assuming the application and DB are on the same server. Option A) SELECT productID, Count(*) counter FROM part WHERE productID IN(large list of productID's received, maybe 1 or >10000) GROUP BY productID ORDER BY counter DESC LIMIT ?, 10 Option B) Execute a batch query of prepared statements (SELECT COUNT(*) FROM part WHERE productID=?) for each product in the received product list then sort the returned results in the Java app that is making the query. Option C) ???
View Replies !
Creating An Optimal Query
What I am doing at present is displaying a list of topics such as on a forum main page. That data is in a table say "my_topics" for example The list I am displaying also references data in another table say "my_images". Now each item in my_topics could reference zero or more items from my_images and they would need to be displayed alongside that topic. So each item in my_images has an id which is an index into the my_topics table. At present I am dumping the entire my_images table into an array so I can access it without having to do multiple sql queries. I can't use "left join" as far as I know because the relationship between my_topics and my_images is one-to-many rather than one-to-one.Obvious downside is that as my_images table gets larger - dumping it to an array increases the memory usage of the script which eventually leads to it not working.
View Replies !
Creating Complex Query
its a booking form. user selects start date, finish date, and room type. on my table i have roomInfo(roomNo, type, NoOfBeds); occupancy(roomNo,DateStart,DateFinish,GuestNumber); i am trying to do something like: select roomNo from roomInfo where roomNO = single and roomNO not IN ... cant do subselect its mysql 4.x version... would i have to do an outerjoin/innerjoin?
View Replies !
Creating A Query From A Form
I'm struggling building a SQL query from the output of a form, i.e. the user inputs into a form which in turn decides the query. I have never done this before and was just wondering if anyone had any links tutorials of something like this!? I have searched but haven't found anything too useful yet. Basically all I want to do is for the user to pick from a drop down menu how they want a leaderboard displayed, i.e. top 50 results, bottom 50, 50 to 100 results, etc. Do I just tie a complete SQL statement with the corresponding LIMIT info inside to a variable. The variable being the value of the chosen item in the drop down menu. Pseudocode as follows:
View Replies !
Creating Table From A Query
I have just put a forum up on my web site. The database tables were readymade via phpbb. Because I want to send newsletters I need a table that just contains usernames and email addresses. The table for users in the phpbb forum is huge so i did sql query: SELECT user_id, username, user_email FROM USERS; Which gave me all the information I need to send the emails on mass when this table is linked to a newsletter application. however I am aware this is just a query. Can I create a new table from this query? So i just have a table of users and emails. If so how do I do it? I am using phpMyadmin.
View Replies !
Creating A Query Based On Dates
I am trying to write a query (in PHP) which selects from a database all of the items which are in the future. My query is as follows SELECT * FROM news WHERE ((news.date)>$today ORDER BY date where news is my database, news.date is the the field which holds the date for the item and $today will be replaced my current date. At the moment it seems to display all values, which suggest its not functioning properly.
View Replies !
Query For Month Data Based On Occurance Against Master List
I have a query running nicely. Now I'm trying to expand it. It pulls the data from a table based on matching the id with another table and part of the grouping is by month and 2 other criteria. Now I'm trying to get the data pulled by month to pull only the info where the id matches the master file AND the FRANID's pulled are the same for each month. I'm trying to get a comparisson across times, but not every month has all FRANID's, depending on when they were entered and I ony want data that exists with matches over set months, starting for now with 12, perhaps also for 6 and 3 next time. I've simplified what I'm doing above, but it is detailed below.....
View Replies !
Creating A Query That Will Only Return Records With Matching Counterparts
I'm using the table below as an example. I want to create a MySQL query that will return only records that have matching counter-parts where 'col1' = 'ABC'. Notice the 'ABC / GHI' record does not have a counter-matching 'GHI / ABC' record. This record should not be returned because there is no matching counter-part. With this table, the 'ABC / GHI' record should be the only one returned in the query. How can I create a query that will do this? id | col1 | col2 -------------------- 1 | ABC | DEF 2 | DEF | ABC 3 | ABC | GHI 4 | DEF | GHI 5 | GHI | DEF
View Replies !
Using A Field, Which Is A Comma Separated List, As The List In An IN Comparison Test.
The following query only reads the first character of a comma separated list stored in a field of the records being queried. The varchar field in table d named "display" holds 1,4 The query " select * from d where 4 IN (display) " tests false. If I change the field "display" to 4,1 Then the query tests true. It is either testing only the first character, or is stopping at the comma. I need this to test true if the value is anywhere in the list, what am I doing wrong here?
View Replies !
Full List Of MyTable21 And Some List Of MyTable22
data in myTable21 (id) country (1) Canada (2) France (3) Egypt (4) America data in myTable22 (id) city name (1) Montreol Jane (2) Paris Mary (3) Cairo Tom (4) New York Jane (4) Chicago Tom I have data in myTables like the above. The following code produces the following result. but I like to produce my target result. code1 select myTable21.id, name, country, city from myTable21 left join myTable22 on myTable21.id=myTable22.id where name='Tom' result1 (3) Egypt Cairo (4) America Chicago target result1 (1) Canada (2) France (3) Egypt Cairo (4) America Chicago code2 select myTable21.id, name, country, city from myTable21 left join myTable22 on myTable21.id=myTable22.id where name='Jane' result2 (1) Canada Montreol (4) America New York target result2 (1) Canada Montreol (2) France (3) Egypt (4) America Chicago code3 select myTable21.id, name, country, city from myTable21 left join myTable22 on myTable21.id=myTable22.id where name='Mary' result3 (2) France Paris target result2 (1) Canada (2) France Paris (3) Egypt (4) America How can I get the full list of myTable21 and selected values from myTable22?
View Replies !
Does "Show Process List" Show Full SQL Query?
I want to find out the queries that hang in my db, I have been running show processlist and using process list in PHPMyAdmin, but does both of these show the full/entire SQL query or just part of it? If it shows only part of it, how can I view the full SQL query in the process list?
View Replies !
List Of Value
Imagine a community website. A user can be part of none, one or several "groups". Let's identify groups by some "GroupID". What would be the best way in the database to save the list of groups of which a user is in. For example, UserA is part of GroupID 3,5,7 and 8. I'm looking for a good way so that that list can be saved, edited and searched fast. Meaning I would often need to display all people that are in GroupID number 8.
View Replies !
List Of Ids
I'm trying to figure out if there is a better way of doing what I am trying to accomplish. using perl, dbi and mysql 12.22. I have a list of ids and I want to get some information out of each row. Is this the best way to do it? Code: select id,this,that from table where id eq ? or id eq ? or id eq ?...etc
View Replies !
Mailing List
I'm looking at undertaking a project for a group of charities. It's basically to enable them to send out newsletters and updates on what's going on and fundraising etc. The idea is, a webpage whereby e-mail addressess are entered by the user, and preferences about what they want to receive are checked. Then, as a list is created, it can be sent out to only those with the options checked. We've a copy of SQL Server 2000 and MS Exchange Enterprise Server 2003 obtained through registered charity status quite cheaply. Does anyone know of any scripts that could possibly handle this data? Or if not, possibly a way of doing this. I'm familiar with SQL commands to enter a new e-mail address, but am not sure of a way to extract the data from a table into an e-mail list.
View Replies !
List Of Dates
i need a jsp program code for date generation.(mssql-back end) Fields are fromdate and todate both field are datetime datatype. Button:-save,cancel,exit both dates are given by the user how to generate the date between these two date. ie.lists of date between the fromdate and todate with datatype datetime
View Replies !
Array Or List
I need to make a list of all the products a client has ever ordered or that they are interested in so that later I can see which clients might be interested in a specific product. What's the best way for me to do this? Probably with JOINS, but I'm not sure how. Doesn't seem right to create a new field for eachof 200+ products.
View Replies !
Birthday List
I am trying to create a birthday list that starts from the next coming birthday to the last. Ex: select last_name, first_name, birthday, room from Children order by ...........
View Replies !
How To List SQL Commnads Used Before
often i need to refer to SQL commands used before. I know previous SQL commands can be brought back by pressing up- and down-arrow key on the mysql command line. However, I want to list the commands used previously. How to do this.
View Replies !
List All Users
I im trying to make a activity list that list all users who havent logged in the last 60 days. I have the following code but it wont only list the last 60 days activity. SELECT * FROM hq_user WHERE user_currentvisit < (CURRENT_TIMESTAMP() - INTERVAL 60 DAY); ORDER BY user_currentvisit ASC;
View Replies !
List Counties
I have the following counties: Suffolk County Hadden County Jockman County I want to display Jockman County first and then the other 2? I cant seem to find a statement to do this nor have I ever tried to do this?
View Replies !
Number In A List
how do you find somebody's rank in a db. SELECT ALL FROM users ORDER BY viportalsscore DESC how do i get a particular user's rank in that list
View Replies !
List Of Posts
I've got 350,000+ posts in a message board. I want to get the most recent 50 posts by a list of users. This is the way I'm currently doing the query: sELECT FROM post_table WHERE (username = 'user1' OR username = 'user2' OR username = 'user3' OR username = 'user4') ORDER BY postDate DESC LIMIT 50; his query can take 15-30 seconds to perform for the first time. Is there a quicker way to do this? There's an index on the username column.
View Replies !
SIC Code List
I'm working on an app that needs to reference SIC codes. They basically map numbers to industry categories, making it easy to categorize businesses. I've spent plenty of time googling, and have only come up with SIC lookups, not full lists. The one list I did find only went out to 6 digits, and I need a list containing 8-digit codes. Does anybody here have a list of SIC codes in either a mysql database or csv file? Or have any idea where I can find one?
View Replies !
Element Of List
suppose I have a field with a comma separated list like this: ,7,100,209,231,336,368,369,370, And I want my query results to be limited to rows where any element in that last appears in another comma separated list. Is there a WHERE command I can use to determine do that? I'm thinking there might be something analogous to the way I would go about constraining a query to rows where a single number is in a llist: Code: WHERE test_column IN list_column
View Replies !
Top Hate List
I have a table hatelist with fields id, userid, hateid. It works like A inserted B to his hate list than userid will be A and hateid will be B. Now i want the top most hated guys like B can be there 5-6 time so i need them according to the number of times they exists.
View Replies !
Sort List By Day
ive got a list of days i.e. monday, wednesday, friday etc. in random order. The problem is i want to sort the list by the day of the week, so monday first then tuesday, wednesday etc. is it possible to do this?
View Replies !
Insert A List Of 800
I currently have a file within mysql with 10 subscribers, but trying to add a txt file with all subscribers to the same file on the database within the schemata. Currently I am only able to add one at a time. Is there a way I can import/insert all these email address at once. There are not different colums just email address.
View Replies !
List Of Keywords
I have a application that creates a large database (1 Million plus rows) one of the fields of which contains about 100-1000 chracters or so of text. I have used a full text index on this field. I want to search the whole of the database for any records that contain one of a list of keywords (the list could be 100+ entries long - although I may be able to limit this but not by a great deal) Is there an easy way to search a table for occurences of records that match one or more of a list of terms?
View Replies !
User List
I'm trying to show all the databases that exist, as well as the users that belong to each of them. Do you have any idea of how can it be the query to obtain this.
View Replies !
Sort A List
how can I sort my database by the age of the people that I have in the database and the place they live.
View Replies !
Installing Php-list
I put in the path which is supposed to be: p50mysql101.secureserver.net dbname is list_datebase username is List_database password has been entered in config-php file. I created a table in the database and named it 'Main' Can you see anything obvious I've done wrong?
View Replies !
List Of Results
I have a table with data User | Value ------------- A | 1 A | 21 A | 33 B | 41 B | 56 C | 68 and I want to produce a query that will return as the result. User | Values -------------- A | 1,21,33 B | 41,56 C | 68 Is this possible? How so?
View Replies !
Find The List Of X For Which There Is Only One Y?
I am trying to find an SQL query that will return a list of items in column X that only appear once, or more precisly only have 1 corresponding Y (X is key). For example: Table (R): X | Y ----- 0 a 1 a 1 b 2 c 2 d 2 e In this case, I would expect to only get back "0" because it only has one corresponding Y value (a), whereas "1" has two (a & b) and "2" has 3 (c & d & e) ... etc....
View Replies !
Friend List
What's the best way to store friend relationsships between users. I have a table with my users and I want to store the user's friends (other users). I'd add a "friends" column to the users table and store the ID numbers of the friend's separated by a certain character. Are there any better solutions?
View Replies !
Command List
Is there a simple way to display _ALL_ user-created variables at once via a single command? Using 5.0.18-nt. In a connection session I create lots of user variables: @siteloc, @itemtyp, @amtset, and so on.They are used in different places during the connection. Show [Global | Session] variables does NOT show the user variables. It is so annoying and time consuming to have to go backwards thru the command list to find them. How to show them all at once?
View Replies !
|
TRACKER
