How To Get Distinct Hour(s) In Query Results?
I need to write a query that will count the number of records that have been written for each hour of the day. This must be done entirely in SQL, and I am not an SQL expert. I know how to break the hour out of a timestamp using the hour() function, but I'm unclear on how to get the breakdown like I described above.
View Complete Forum Thread with Replies
Related Forum Messages:
Running A 'Distinct' Query Returns Some Results TWICE !?
I've spent hours trying to find out why a perfectly simple query that uses the 'distinct' keyword such as: Select distinct(field1) From tablename; works fine based on eg. field1, but when running it on eg. field2 in the very same table, it returns 2 results (yet, only on some words!). Pls see example of result below: Eg of a 'faulty' result: .....
View Replies !
Limiting To Distinct Results
I want to created a list of all threads in which a specific member has posted. I've gotten as far as combining everything to show all posts by that member (given certain security measures so that only posts the searcher can see are displayed)--Which is good. But I want a scaled down version that shows distinct threads. Here is what I have: ....
View Replies !
Join 3 Tables - Distinct Results
I've got a database that keeps track of sales of widgets. Each company that belongs to my organiztion is to report their widget sales or no sales every month. There are several different types of widgets. Not all companies sell or report all types of widgets. We want to report how many companies have reported or not reported their sales (ie. x companies of a possible y companies have reported sales this month - y will always be the same - lets say 5). Because of the way that sales are input, "big widgets" are reported in 2 different tables called widgets_a and widgets_b. If they don't have any sales to report, they still report and it goes into a table called no_reports. Each table has a couple of common fields - ManufacturerID and OrderDate. I can search all of the tables individually to find if a manufacturer has reported - SELECT DISTINCT ManufacturerID FROM widgets_a WHERE OrderDate >='2003-06-01' AND OrderDate <= '2003-06-30'; but I want to search through the 3 tables and find how many distinct manufacturers have reported in the given month.
View Replies !
Selecting Distinct Results In A Join
SELECT p.post_title , p.post_name , c.cat_name , DATE_FORMAT(p.post_date, '%M %D, %Y') AS dateadded FROM wp_posts AS p LEFT JOIN wp_post2cat AS pc ON p.ID = pc.post_id LEFT JOIN wp_categories AS c ON pc.category_id = c.cat_ID WHERE MATCH(p.post_content, p.post_title) AGAINST(TRIM('merchant') IN BOOLEAN MODE) ORDER BY p.post_date DESC I am doing a fulltext search on a Wordpress database. Some posts are in multiple categories. When using the above query I will get a result for every category a post is in. I don't want that. I have tried using distinct with no effect. The wp_categories has a row for each category a post is in and how I am joining it seems to be the culprit.
View Replies !
Many To Many Select Using DISTINCT Or Not Return The Same Results
I was on MySQL website looking for an answer, but still need assistance please. I want to do the opposite of the following: SELECT * FROM myTable WHERE id IN(1,2,3,4,5) ; I want to use my array(1,2,3,4,5) to exclude items in the result set, not include them. So, I want to do something like this: SELECT * FROM myTable WHERE id NOT_IN(1,2,3,4,5) ;
View Replies !
Select Distinct Results Without Throwing Away Data?
id series_id image date 6 2 1191956895.jpg 2007-10-09 5 3 1191956773.jpg 2007-10-09 4 2 1191451083.jpg 2007-10-03 3 1 1191451026.jpg 2007-10-03 2 1 1191451012.jpg 2007-10-03 1 1 1191450488.jpg 2007-10-03 if you take a close look at the code above you might be able to see that i have these images assoicated with one of 3 series. id like to be able to select the most recent from each one and discard the rest. however a command link distinct on the series_id throws away all the other data around it that i need. the results would look more like seires_id image 2 1191956895.jpg 3 1191956773.jpg 1 1191451026.jpg i imagine this would be much like a forum sorting the most recent topics posted in a forum by finding the most recent posts for each distinct topic.
View Replies !
SELECT DISTINCT On A Single Field - Full Row Results
I've been trying to figure out how to do this and can't seem to wrap my thoughts around how to do it. Basically I want to select every row that is distinct based on a single field, like: Author | Book Name | Book Num Bob | ThisBook | 1 George | ThatBook | 2 Fred | HisBook | 3 Bob | HerBook | 4 How would I select each author only once, but return the entire row for that unique result? Basically I'd want Bob, George, and Fred to all appear, but Bob to only appear once, not twice. And this is assuming I can't change the table to be better structured, I don't have direct access to it. I realize how it SHOULD be set up, but unfortunately it is not.
View Replies !
SELECT DISTINCT Unexpected Results With Multiple Columns
SELECT DISTINCT (query) FROM `data` WHERE 1 LIMIT 0 , 30 and it works fine. When I start selecting more than one column, though, it returns all the rows weather the query is distinct or not. Here is the query that doesn't work. SELECT DISTINCT (query), data. * FROM `data` WHERE 1 LIMIT 0 , 30 It seems like DISTINCT() is looking for a completly distinct row. How can I get it to just look for a single distinct column?
View Replies !
Distinct And Not Distinct In One Query
i have two table trans and plgn select distinct trans.id, trans.nama_trans,trans.jenis_trans,plgn.id,plgn.site_asal,plgn.site_tujuan from trans, plgn where trans.jenis_trans like '%$jenis_trans%' and plgn.site_asal like '$site_asal' and plgn.site_asal not like '' and plgn.site_tujuan like '$site_tujuan' and plgn.site_tujuan not like '' and trans.id=plgn.id but it makes duplicate site_asal and site_tujuan cannot appear... so how to take site_asal and site_tujuan out from distinct area.... i've tried this select distinct (trans.id), (trans.nama_trans),(trans.jenis_trans),(plgn.id),plgn.site_asal,plgn.site_tujuan from trans, plgn where trans.jenis_trans like '%$jenis_trans%' and plgn.site_asal like '$site_asal' and plgn.site_asal not like '' and plgn.site_tujuan like '$site_tujuan' and plgn.site_tujuan not like '' and trans.id=plgn.id
View Replies !
Distinct Query
Here is the problem, I have a database which may have data that looks like this: keyword1 title1 desc1 keyword2 title1 desc1 keyword3 title1 desc1 keyword4 title2 desc2 keyword5 title2 desc2 keyword6 title3 desc3 In other words, I have one table with a bunch of listings. I am trying to get a distinct result set, cycle through those results, then get a set of the keywords which meets each distinct result. However, when I wrote a script to do this, it obviously left out a lot of valid results.
View Replies !
Using DISTINCT In A Query
if you take a look at http://www.beechlawnrovers.com/chat.php I'm trying to get the forum to list meeages by the most recently replied to but its listing some meesages multiple times, I want to list each message just once. I thought using DISTINCT in the mySQL query would do this but it wouldnt work. Please help! Here's my code: SELECT DISTINCT messagetitle, replyid, messageid, messageuser, replyuser, replydate FROM beechlawnrovers_forummessages, beechlawnrovers_forumreplies WHERE messageid = replymessageid ORDER BY replyid DESC
View Replies !
Trying To Query The NON Distinct
I have a phpBB 3.0 forum. There is a way in the forum to view users with matching ips, but you have to go into every user and view individualy. I want to write a query to do this for me. The related info follows: table: phpbb_posts field 1: poster_ip field 2: poster_id <----- foreign key to phpbb_users table "user_id" PK I have tried things like: Select poster_ip, poster_id from phpbb_posts where poster_id IN ('14','15') AND poster_ip = poster_ip which obviously does not work. I also tried doing a COUNT(*) and then a where clause that looks for rows that have a count(*) >1 but I cannot get that right.
View Replies !
Query With Distinct
SELECT DISTINCT alt, galID FROM cfm_gallery LIMIT 12 Which works great, but I also want to add another field to the query photo But I don't want that field to be distinct,
View Replies !
Select Distinct Query
I have a table in my db with two fields, one called link and the other shop. I have another table called link_partners. This latter table has an id column that matches the link column in the former table. I am trying to run a sql statement on the two tables and am able to do so until I try to just show the records in the link_partners table with distinct category column value. Here is my sql Code: mySQL="select DISTINCT category from link_partners inner join link_partners on link_partners.id = links.link where links.shop = 77"
View Replies !
Query Distinct Selection
What I want 2 do is to select from 2 different columns that could have the same values e.g.My_Table { I_pk input1 input2 X_value } in the input1 field I have several names that could be or not in the input2 field so I need that my query shows every word that's in the input1 & the input2 but without repeating any of this words like a distinct but with the 2 fields
View Replies !
Selecting A Distinct Query
I want to: select name, address, city, state, zip from client; However I want to query a distinct address(some of the people work at the same address, and I only want one letter to go to each address.). Is it something like: select name, distinct[address], city, state, zip from client; I know this is not right, but does anyone know this? Thx.
View Replies !
Simple Not Distinct Query?
I have a very large product table and believe that some of the fields contain duplicate values when they shouldn't. So I'm trying to create a simple query to identify those that do.. ie something like Select modelnumber from tbl_product where modelnumber is not distinct OR Select NOT Distinct modelnumber from tbl_product. These obviously don't work (Otherwise I wouldn't be asking for help!), and was wondering if there is a not distinct function that im missing or misusing?
View Replies !
DISTINCT/GROUP Query.
I have some serious issues with a DISTINCT Query. The table is for logging users. Let's say the table looks like this userID | average | timestamp 1 | 20 | 2008-01-16 00:00:00 1 | 35 | 2008-01-17 00:00:00 2 | 12 | 2008-01-18 00:00:00 1 | 16 | 2008-01-20 00:00:00 What i want to go get is the the DISTINCT userID (every user, but only one time) and i want to get the latest value of the avarage field (so sorted by timestamp desc). I tried many ways to get this but i can't seem to get it to work, DISTINCT is using all the fields i add, and if i GROUP i either have to say like, MAX/MIN and since there is nothing that makes the average increase/decrease by every new post in the database. My third try was to order by the timestamp, but i doesnt seem to work in all cases, only sometimes.
View Replies !
Distinct Query Question
Person(personid, name); Table2(id, name, personid); I want to pull out all unique personid's from table2, and get their associated name based upon a search parameter. For example I might want to find out all those who are called "Peter", who have made an entry in "Table2" e.g. John Smith with id1 might have 2 rows in 'Table2', but I just want to pull his ID and name once The same with any other people.
View Replies !
Distinct And Join Query In MySQL
I have a query question, it may be really easy but somehow I cannot get it What I have is a table with columns, two of which I will name Item1 and Item2, I would like to get a distict set of pairs Item1-Item2 from this table, more specifically if table looks like this ....
View Replies !
Distinct Values In Mysql Query
I have mysql query but i am not sure about it,bcoz it return many rows.. I want like distinct values from three tables with ic number. Which is posted by user... "SELECT distinct loguser.icnumber,access.acccode,dealerdtl.name,loguser.fullname,access.period,loguser.staffcode, DATE_FORMAT(access.actdate, '%d-%m-%Y ')As formatteddate1, DATE_FORMAT(loguser.creationdate, '%d-%m-%Y %H:%i:%s')As formatteddate FROM dealerdtl,loguser,access where access.icnumber=loguser.icnumber AND loguser.icnumber = '$custic'"; This query return many rows i dont know why, i want only one row which is inserted by the user as customer id.
View Replies !
Getting Two Distinct Fields From One Tbl From A Join Query Of Two Tables
Im trying to get distinct (the same row returned only once) user_id and forename from a tbl of users (they are always distinct) where user_id in that tbl equals friend_id in another table friend_id, which 'should' be unique ie there 'should' only be one row of a 'friend connection', but in the case of bugs etc this might not be true, what i want to do is select only the row of tblusers with the user details that are matched by join condition below, but only ONCE for each, ie not in as many times as there are the same connection in tblfriends. For some reason the result i am getting is a repitition of the 2 fields in tblusers (that i am selecting) for as many rows as there are in tblfriends that match the join condition. How can i make it only return a row once?
View Replies !
Select One Distinct And Several Random Values In One Query
I'm wondering if it's possible to select an id (let's say its 13) and several random id's between 1 and 100 in one query, excluding for example the id's 14 and 15. I've tried different ways with UNION - but when I include 'order by rand()' in my query, I'll never get the id 13 back. Could anybody help me please? Here's a query which did not work, because i will always get the the same result, without random values: SELECT * FROM table WHERE id = '13' UNION DISTINCT SELECT * FROM table WHERE id != 14 AND id != 15 AND (id BETWEEN 0 AND 100) LIMIT 3
View Replies !
Distinct Column In Multi-Table Query
I want one article from each 'sub topic'. Each sub topic is given a 'sub_id'. PHP $garticle = mysql_query("SELECT DISTINCT(feeds.sub_id), articles.title, articles.feed_id, articles.abstract, articles.link, articles.date, site.articles, $formula AS importance FROM articles,feeds,site WHERE articles.date > DATE_SUB( now() , INTERVAL 1 day ) AND articles.feed_id=feeds.feed_id AND feeds.site_id = site.site_id ORDER BY articles.article_id DESC LIMIT 16", $connection) or die(mysql_error()); The article has a 'feedIid', this JOINS the feeds table. The feeds have a 'sub_id'. I only want the 16 most recent articles from DISTINCT 'sub_id's
View Replies !
Save Query Results
In other words, how do I build a new table from the results of a query, so I can access pieces of this data later without performing the same query (a very heavy one) over and over again?
View Replies !
Filter Results Of One Query
I'm looking for the syntax that would allow me to filter one querys results using another query i.e. QUERYA = SELECT * FROM table WHERE id < 100 QUERYB = SELECT * FROM QUERYA WHERE id <> 49 I understand that I can do the above example in one query - I'm just using that to simplify matters.
View Replies !
Order Query Results
Regarding a dummy set of data below, ID Status 1 Open 2 Closed 3 Expired 4 Closed 5 Open 6 Cancelled 7 Expired 8 Cancelled I want to view the results but order them according to the status. i.e. i want to view in order of Open, Closed, Expired, Cancelled I can only think of a long drawn out way by which i would create the four seperate queries.
View Replies !
Query Results By Pages
I would like to display query results in pages of say 20 results each. e.g. a table with two columns, serial# and descrition, with a few thousand entries. What would be the SQL query to find a specific serial number, and display all 20 results within the "page" that row happens to be in? e.g say serial#=3211 is in row 64, display rows 60->79.
View Replies !
Formatting Query Results?
I have a shell script (sh) that queries a MySQL database and then formats the results to an html page. I realize shell is probably not the best choice for this but it's all I know. My problem is that I need to do a mysql query for every field in every record in order to put it into the html page. This is obviously very slow. This is an example of what I'm doing now: ProductLine=`mysql -u www -s support <<EOF SELECT ProductLine FROM Documents WHERE ID = '$ID'; EOF` DocType=`mysql -u www -s support <<EOF SELECT DocType FROM Documents WHERE ID = '$ID'; EOF` DocName=`mysql -u www -s support <<EOF SELECT DocName FROM Documents WHERE ID = '$ID'; EOF` FileName=`mysql -u www -s support <<EOF SELECT FileName FROM Documents WHERE ID = '$ID'; EOF` Like I said, I am doing this for every field in every record. It works but it is VERY slow. I thought if there was a way to tell the query to insert a special charater between each field then I could use awk to pick out the fields and only do one query per record... something like this: SELECT ProductLine, DocType, DocName, FileName FROM Documents WHERE ID = '$ID'; I don't know how to take that output and put it into my shell variables.
View Replies !
Saving Query Results
Is there any way to save the results of a query without the quote marks? When i try to import the text file that I saved the query to, I have to go thru and delete all of the quote marks.
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 !
Eliminating Duplicate Results From Query
I'm trying to dynamically build pull down menus based on the contents of a column (in order to build a search query). But the column may have the same data entered multiple times. This results in a pull down that has the same option listed several times. I wonder if I can eliminate duplicates from that array? So that it only appears once in the array.
View Replies !
Update Query Results To Another Table
So I'm learning, bear with me, this is probably pretty easy(but I am a bit perplexed) but I'm writing a php page for a cron. I am selecting a user name from a table basically selecting all row entries in that table with a certain username, and adding them together. $query=mysql_query(SELECT UserName, SUM(AcctOutputOctets) FROM radacct GROUP BY UserName) The output is this(when I run a query in mysql) UserName..............SUM(AcctOutputOctets) test..................... 345566 Basically i'm adding up all the users entries and totaling their Usage. Now the question. I want to write that to another table named mtotacct. format like this. Username..............TotalOctets. I'm just a little uncertain how to properly set this up to pass the results from the query to the UPDATE statement.
View Replies !
Showing Query Results In HTML
In mysql I can find the minimum data from a particular column by doing as follows: SELECT MIN(Freq_Min) FROM datatable; and mysql will display the results. Now I want to do the same thing but I want to do it in html, so I wrote the following code. When I open up the webpage stats.php it says that it couldn't execute the query. Can someone help get me on track? .....
View Replies !
Use Query-results As Delayed Insert?
Can I use the results of one query like: -> SELECT user_id FROM tbl_customers And use the results to fill the VALUES-statement in another table like: -> INSERT INTO tbl_postal ('user-id') VALUES (<previous query results>) If so, could someone complete my second SQL-statement, since I'm staring at my CRT for over 2 evenings and I cannot get it to work.
View Replies !
Query Is Not Returning Desired Results.
My query is not returning desired results. I want to structure my query to return the Post Subject, Post Text, and Poster. Instead, it is returning the correct Post Subject, correct Post Text, but the Poster is just repeating the username from the '$_POST[username]' variable. How can I alter the query to return the correct name? the query PHP <?php$conn = mysql_connect( $domain, $user, $password ) or die("Err:Conn");$rs = mysql_select_db($db, $conn) or die("Err:Db");$sql = "SELECT `table_posts_text`.`post_subject`, `table_posts_text`.`post_text`, `table_users`.`username` FROM table_categories, table_forums, table_topics, table_posts, table_posts_text, table_users WHERE username='$_POST[student]' ANDcat_title='$_POST[category]' AND`table_categories`.`cat_id`=`table_forums`.`cat_id` AND `table_forums`.`forum_id`=`table_topics`.`forum_id` AND `table_topics`.`topic_id`=`table_posts`.`topic_id` AND `table_posts`.`post_id`=`table_posts_text`.`post_id`AND `table_topics`.`topic_poster`=`table_users`.`user_id`AND`table_posts`.`poster_id`=`table_users`.`user_id`ORDER BY `table_topics`.`topic_id`, `table_posts`.`post_id`"; $rs=mysql_query($sql,$conn);while($row=mysql_fetch_array($rs)){ echo($row['post_subject'] . $row['post_text'] . $row['username']);}?> There are six tables involved: 1. table_categories (cat_id medint, cat_title varchar) 2. table_topics (topic_id medint, forum_id smallint topic_title varchar topic_poster medint) 3. table_forums (forum_id smallint, cat_id medint, forum_name varchar) 4. table_posts (post_id medint, topic_id medint, forum_id smallint, poster_id medint) 5. table_posts_text (post_id medint, post_subject varchar, post_text text) 6. table_users (user_id medint, username varchar)
View Replies !
Reversing Query Results (not The Same As ORDER BY)
I am trying to reverse the order in which the results of my query are given. I am tweaking a gallery. I need to get the previous 3 pics based on the pic I am looking at. The following code is a simplified version of what I have. The problem is, when I echo the results, the thumbnails are displayed in DESC order (i need them to display in ASC order) but if I order the query using ASC, the query gets the wrong images. SELECT pic_id WHERE pic_id < $current_pic_id ORDER BY pic_id DESC LIMIT 3 I need the results to show like this: oldest pic | older pic | old pic | current pic | new pic | newer pic | newest pic I've got the new pics sorted out but the old pics are causing a little problem... So basically, is there a way that I can reverse the results of my query?
View Replies !
Mixed ORDER BY Query Results
Is there a way to order a query result a specific way? For instance, I'm querying using WHERE id IN(23, 25, 19) I would like the results to stay in that order instead of by ASC or DESC. Is this possible?
View Replies !
Case Insensitive Query Results?
I've been noticing that the MySQL 4.1.19 server on a client's Web server returns case-insensitive results for SELECT statements when using PHP's PDO extension. I didn't notice this happening on my XP dev server with MySQL 5.0, and have never seen it happen on any other server. Does anyone know why this happens and what can be done about it?
View Replies !
|
TRACKER
