Get GROUP To Display Most Recent Entry?
I'm pulling in info and Grouping by my column that stores "project names".
The Grouping is successful, but rather then displaying the most recent entry for the GROUP, the first entry made is displayed.
I would like the most recent entry to represent the group.
I would guess that this needs a MAX function run on my column that keeps the entry_date but I can't seem to get this to work.
Each time I apply the MAX function I end up limiting my results to 1 entry...not what I want.
This is the query I'm starting with:
MySQL
SELECT d.entry_id as prj_id,d.field_id_1 as prj_name,d.field_id_5 as prj_thumb,d.field_id_15 as prj_image_type,d.weblog_id,t.entry_date,t.url_title as prj_url,t.status as prj_status,c.cat_url_titleFROM exp_weblog_data dLEFT JOIN exp_weblog_titles t ON d.entry_id = t.entry_idLEFT JOIN exp_category_posts cp ON t.entry_id = cp.entry_idLEFT JOIN exp_categories c ON cp.cat_id = c.cat_idWHERE t.status = 'open' AND c.cat_url_title = '{segment_4}' // variable grabbed FROM URLGROUP BY d.field_id_1ORDER BY t.entry_date ASCLIMIT 16
View Complete Forum Thread with Replies
Related Forum Messages:
Query To Display A Record By Recent Time
Many users uploaded their files to my mysql table through php script, my table having the details of uploading time, file name, & uploader name. i need to find the recent uploaded file for all uploaders. i tried with this query, select file_name,uploded_by,MAX(date_time) from upload group by uploded_by; it is giving the recent time, but it is not giving the latest file, it is showing first uploaded file.
View Replies !
SELECT Categories, SORT BY Most Recent Entry In Said Category...
My blog has multiple categories in one table, and each category has multiple associated blog entries in another table. Long story short, I want to SELECT (and display) each category title, but sort the categories by the most recent article posted to that category. The query below works, but I think it could be optimized: .....
View Replies !
Select Most Recent From A Group
Hello I am having troubles with a query I am trying to make. I have a table to keep notes on customers note_id - primary biz_id date_of_interest notes I want to grab the biz_id and notes of every record that has a date_of_interest before or on today's date. The problem I am having is if one customer has multiple notes such as: 3, 1233, 2006-05-02, note 8, 1233, 2006-06-28, note 13, 1233, 200-07-24, note he will get selected from my query since two of his previous notes have a date_of_interest before today but I really only want to care about the most recent of notes so he shouldn't get selected. Is there a way to do this in the query or should I add another field to the database to mark it as the most recent.
View Replies !
Group/Join - Wish To View Most Recent...
I've been trying to sort this out for over 2 hours and I can't get it to work! What I want is for the code at the base of this post to list each of the forums with the most recent topic details. My problem is that it doesn't choose the most recent post, it chooses the first. I've tried: - changing 'forums_topics.topic_time' to 'MAX(forums_topics.topic_time)' [returns the MAX value, but not the corresponding topic_id/title - it still returns the first topic_id/title!] - adding 'forums_topics.topic_time DESC' to 'ORDER BY' [no effect] - adding 'forums_topics.topic_time' to 'GROUP BY' [this lists them successfully, and with this the ORDER BY has an effect, but I don't want to list all topics, just the most recent] - adding subqueries (couldn't get to work) - adding 'HAVING topic_time = MAX(forums_topics.topic_time)' after 'GROUP BY forums_forums.forum_id' [which returns only the forums with a single post] .....
View Replies !
Return Most Recent Record For A Group Of IDs
How do I return only the 1 most recent record from one table given a list of customer IDs from another? We're unfortunately running just MySQL 4.0 Pseudo code: show a list of distinct customer IDs for those customers that have a currentbalance of > $0, who had an order marked canceled in the last 7 days. Given: orders - orders.customerID - orders.status - orders.updated (datetime) billing - billing.customerID - billing.currentbalance - billing.updated (datetime)
View Replies !
Display Newest Db Entry
i have tried the following and it comes up with this error: Queryproblem: 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 'BY date DESC' at line 1 --------------------- SELECT top1 FROM `cds` SORT BY date DESC --------------------- i need to display the newest entry in the mysql database.
View Replies !
COUNT And GROUP BY Display All Counted Rows
have a table as follows ID | ORG | Subject ----------------------- 100 | 14 | Some text 101 | 18 | Text1 102 | 18 | Text3 103 | 14 | Text4 104 | 18 | More text What query would I need to get the following output? (when I use COUNT(ID) and GROUP BY ORG it only returns 1 ID per ORG) ORG 14 - 2 results 100 103 ORG 18 - 3 results 101 102 104
View Replies !
If Entry Exists Update Otherwise Insert Entry
I want the user to be able to update what they are typing as they are doing that. If the entry exists it is suppose to update that entry not insert another, or overwrite another. This is the code that I have started with: PHP Code: // only add an entry if that entry doesn't exist     $query = mysql_query("SELECT * FROM `mediareleases` WHERE title = '$title'") or die ("Could not query because: ".mysql_error());     $num_rows = mysql_num_rows($query);     if ($num_rows < 1) {     $insert1 = "INSERT INTO `mediareleases` (`title`, `date_entered`, `text`)            VALUES ('$title', '$date', '$entrybody')";     if (mysql_query ($insert1)) {       print "<strong>Entry has been added</strong>.";     } else {       print "<p>Could not add the entry because 1: <b>" . mysql_error() . "</b>. The query was $insert1.</p>";     } // end of if (mysql_query ($insert1)) {     } else {       // if the entry exists in the database       // then update the database       $update1 = "UPDATE `mediareleases` SET title = '$title', text ...
View Replies !
Full Yearly Weekly Group By Group Report
I have table called tblTransactions: TblTransactions Group | Months | Year | Transaction A | 3 | 2007 | 45 B | 2 | 2007 | 89 A | 7 | 2007 | 50 A | 8 | 2007 | 34 B | 4 | 2007 | 55 A |12 | 2007 | 10 A | 1 | 2008 | 88 B | 3 | 2008 | 34 B | 5 | 2008 | 70 A | 5 | 2008 | 45 A | 9 | 2008 | 88 B | 7 | 2008 | 99 A |10 | 2008 | 77 A |11 | 2008 | 99 How is the T-SQL to make the result like this (the result will display all of the week (FULL), the target is I can compare week by week between years and group. A | 1 | 2007 | 0 B | 1 | 2007 | 0 A | 2 | 2007 | 0 B | 2 | 2007 | 89 A | 3 | 2007 | 45 B | 3 | 2007 | 0 A | 4 | 2007 | 0 B | 4 | 2007 | 55 A | 5 | 2007 | 0 B | 5 | 2007 | 0 A | 6 | 2007 | 0 B | 6 | 2007 | 0 A | 7 | 2007 | 50 B | 7 | 2007 | 0 A | 8 | 2007 | 34 B | 8 | 2007 | 0 A | 9 | 2007 | 0 B | 9 | 2007 | 0 A | 10| 2007 | 0 B | 10| 2007 | 0 A | 11| 2007 | 0 B | 11| 2007 | 0 A | 12| 2007 | 10 B | 12| 2007 | 0 A | 1 | 2008 | 88 B | 1 | 2008 | 0 A | 2 | 2008 | 0 B | 2 | 2008 | 0 A | 3 | 2008 | 0 B | 3 | 2008 | 34 A | 4 | 2008 | 0 B | 4 | 2008 | 0 A | 5 | 2008 | 45 B | 5 | 2008 | 70 A | 6 | 2008 | 0 B | 6 | 2008 | 0 A | 7 | 2008 | 0 B | 7 | 2008 | 99 A | 8 | 2008 | 0 B | 8 | 2008 | 0 A | 9 | 2008 | 88 B | 9 | 2008 | 0 A |10 | 2008 | 77 B |10 | 2008 | 0 A |11 | 2008 | 99 B |11 | 2008 | 0 A |12 | 2008 | 0 B |12 | 2008 | 0
View Replies !
Most Recent Value
I've become rather rusty with the old SQL statements, and would really appreciate your help with the following. Assume I have one table (tbldata): dataID | date | configID | value 1 | 3 | 1 | 0.75 2 | 5 | 3 | 0.15 3 | 5 | 4 | 0.60 4 | 6 | 1 | 0.87 5 | 9 | 3 | 0.29 6 | 10 | 1 | 0.52
View Replies !
Group By With Order BY, Or INSERT INTO (SELECT * GROUP BY)?
I seem to have a few duplicates entries in a very large database. My database has two identical tables. One is written to on a regular basis (every few seconds) and the other is used for queries, and updated from the write table on a daily basis. I'm trying to get rid of duplicate entries for the queries, and I thought the most effective way to do this would be to run the query as Expand|Select|Wrap|Line Numbers
View Replies !
Sql To Get Most Recent Records
How would I write the query for mySQL to get all the records in a table that were written in tha last 10 minutes? I have a timestamp field (fldTime) that automatically inserts the curent time when the record is inserted, but I'm unsure how to write a query that would only get records from the last 10 miniutes.
View Replies !
Sort Asc W/ Most Recent
I want to display the five most recent rows in my table (there is a timestamp), but sorted in ascending order. the problem is that when i call SELECT * FROM spam ORDER BY time ASC LIMIT 5 it shows the first five entries in ascending order, but i want the last five. needless to say, it works fine with descending order..
View Replies !
Most Recent Entries
I have a table containing a "time" and an "author" column. How is it possible to select the 10 most recent entries, but sorted by "author"?
View Replies !
Recent Entries
I have a table containing a "time" and an "author" column. How is it possible to select the 10 most recent entries, but sorted by "author"?
View Replies !
1 Table -> Get Most Recent Row For Each Id
I have a problem that can be summarized with a simple table having the following 3 columns: timestamp userid status The table is constantly being updated. I'd like to get the most recent row for each userid present in the table? What pray tell is the trick? I tried using GROUP BY, but it would only return the 1st occurance of each userid (I want the last).
View Replies !
Getting The Most Recent Record
Given a table that has the following fields: create table WAGE_RATES( EMPLOYEE_ID int unsigned references EMPLOYEES, DATE date, HOURLY float(5,2), WEEKLY float(5,2), OTHER float(5,2), index(DATE)
View Replies !
Recent Record
Basically what i want to do is create an SQL string that searches the table for a specific userID, THEN finds the most recent record of the userID (according to my datetime field which is called transdate). Im Using ASP to connect to a MySQL database. i've looked all over the net trying to find out how, also i dont really know which forum this should be in (either mysql or asp?).20
View Replies !
Recent Primary Key
I am trying to get my php page to select the most recent addition to a database under a certain username sorted by most recent primary key, but I am unsure on the SQL syntax to accomplish this. What I am trying to do is some thing like: Code: update table_name set title = new_title and content = new_content where username = username sort by primary key most recent; Is this possible and if now do you have another idea of how I can accomplish something similar?
View Replies !
Most Recent Date.
I have a date_added field in the database. But I always want to get the rows that have themost recent date with time ('clk0h4tski156pgfgo4kth21b6', '', 'Place_Card_personalised', 'Z/X', 2, ད.00', 'chris', ��-05-20 10:37:07'), ('clk0h4tski156pgfgo4kth21b6', '', 'Day_Invitation', 'M', 2, ྊ.00', 'chris', ��-05-20 10:37:07'), ('clk0h4tski156pgfgo4kth21b6', '', 'Day_Invitation', 'M', 2, ྊ.00', 'chris', ��-05-20 10:36:37'); Will the following query do what I want it to do? SELECT DISTINCT(date_added), SUM(price) FROM cartTemp WHERE `cart_id` = '$cart_id' AND `username` = '$username' GROUP BY price
View Replies !
Updating Most Recent/last Row
This is for a directory and the table Dir holds the selected plan of each member. The table contains a history of the plans (inserted on each selection) so what I need to do is update the last row of Dir.DirID. Every time 'Posting' value is echoed on the page with query 'Listings' (two seperate tables from Dir - one for runtime and one for the actual postings (a foreign/primary key) - then Dir.Dview is incremented as a basic hit counter with the query Listings higher on the page. What is the most reliable way of updating the last row of Dir.DView where DirID is primary? note: query 'Listings' uses SELECT max But experimenting with this before, I hessitate to use SELECT max for an update. <?php echo ($row_Listings['Posting']); $query_updateSQL = sprintf ("UPDATE Dir SET Dir.DView = (Dir.DView+1) WHERE DirID = %s", $colname_Listings); $result = mysql_query($query_updateSQL); ?>
View Replies !
Distinct, Most Recent Sort
Does anyone know how to do a sort to find the most recent distinct records. We have records with equipment, and activities the equipment was used for, but I need a distinct list of where the equipment currently is, if I do a distinct ...order by date, it will perform the distinct first, ahead of ordering.
View Replies !
Get Most Recent Version Of An Element
I'm new to mysql and I was wondering if I could trouble some of you for help. I have a table in my database that may contain multiple versions of the same element as different rows. I wanted to use the following query to group the rows by element with "group by" and then use the "having" to get the most recent version of each element, which would then be returned in "select" but this doesn't seem to be working. Any elements with multiple versions are simply not returned. I could swear that I have used a similar approach in the distant past with other databases and it worked, though, so I must be bollocksing this up pretty badly while attempting to work off of what I remember...
View Replies !
Retrieve Most Recent Rows
I have a simple table with four columns. ID (auto_increment), username, score, and date (unix timestamp). How could I, if possible, get the past 10 scores of each unique username? So if there were 15 different usernames, I would get 150 rows returned (assuming all of them had played 10+ games). Can this be done in a single query or do I need to run a query for each username?
View Replies !
ORDER BY :: Most Recent Date
I have dates recorded in my database for my website, in a var char in this format: 4/8/07, 4/21/07, 4/22/07, 4/10/07, etc. When I use order by to try to order by the most recent date, it doesn't work. I even know why, and it's because it'll put 4/8/07 before 4/21/07 because there is no 0 before the 8, so it sorts it as 8 larger than 2, which it is. Is there any way to get around this, so it will order correctly?
View Replies !
Select, Order By. Most Recent One Last
I have one more question which is best explained by example. My database table contains a (very large) table with books and for simplicity sake lets assume every record only contains 2 fields: Title and AuthorID ....
View Replies !
Way To View Recent Queries
Is there a way to view recent queries running on Mysql server (say last 3 minutes). In our application we faced, lock wait time out issue. I suspect some of the queries executed causes this issue.. If I check the queries that running on Mysql server, that may give a clue or may help to identify the issue.
View Replies !
Left Join To Get The 1 Most Recent
Firstly table structure - 1 user has multiple status records. When a new status record is created it gets the current datetime inserted into 'date_created'. user user_id site_id status status notes date_created I wish to display a list of all users and show beside them their current/most recent status record. Here is my code currently: SELECT user.*, status.date_created, status.status, status.notes, DATE_FORMAT(status.date_created,'%l:%i %p') as last_updated_time, DATE_FORMAT(status.date_created,'%e/%c') as last_updated_date FROM user LEFT JOIN status ON status.user_id = user.user_id WHERE user.site_id = 1 GROUP BY user.user_id ORDER BY CASE user.user_id WHEN 8 THEN 0 ELSE user.user_id END Currently via the LEFT JOIN and GROUP this does display a list of all users with 1 status record per user but it's simply the first status record in the table (ordering by id i guess), not the most recent. How can I get it to display the most recent instead, based on date_created. I've tried adding ordering to the left join but this returned an error.
View Replies !
Showing 5 Most Recent Entries
I'd like an area on my site where the 5 most recent entries into the database are displayed in a list, with the top result being the most recent of all. For this I was planning on doing something like querying the id field of the database to find the five highest numbers only and then echoing them out in descending order. This however is not something I have been able to find any information on in my searches.
View Replies !
Trying To Retrieve Most Recent Record Per Date
I have the following query which is retrieving a set of data it is almost what I want but I can not manage to get the result I desire. SELECT r1, r1_dev, r2, r2_dev, date, time FROM output WHERE output.id_modality = '1' AND output.id_linac='2' ORDER BY output.date DESC 'r1','r1_dev','r2','r2_dev','date','time' '37.500','0.334449476181','[NULL]','[NULL]','2006-07-27','15:00:00' '50.000','0.334449476181','37.470','0.0000364374', '2006-07-27','11:26:00' '50.000','0.334449476181','37.470','0.0000364374', '2006-07-27','11:26:00' '40.812','0.8538683','0.000','0.0000000000','2006-05-12','08:50:00' '41.580','2.7517359','40.370','-0.2383940000','2006-05-12','08:45:00' '40.756','0.8155671','0.000','0.0000000000','2006-05-12','15:00:00' '37.952','-0.472445','0.000','0.0000000000','2006-05-12','14:53:18' '38.010','-0.3203424','0.000','0.0000000000','2006-05-12','14:52:33' '39.488','3.3474615','37.916','-0.7667557000','2006-05-12','08:35:43' '39.650','3.7714458','38.020','-0.4945683000','2006-05-12','08:22:38' '40.330','-0.2382025','0.000','0.0000000000','2006-05-12','15:00:00' '41.330','2.2354728','0.000','0.0000000000','2006-05-05','00:00:00' '39.220','2.8528707','0.000','0.0000000000','2006-05-05','00:00:00' '40.696','0.8814325','0.000','0.0000000000','2006-04-28','15:56:00' '41.000','1.635019','0.000','0.0000000000','2006-04-28','15:55:00' '38.380','0.8642144','38.400','0.9167752000','2006 -04-28','15:56:10' '37.970','-0.2132824','38.510','1.2058597000','2006-04-28','15:49:23' From the set above I would like to retrieve the latest measurement per date but if I try to group by date (like below) I always get the first result instead of the latest result. SELECT r1, r1_dev, r2, r2_dev, date, time FROM output WHERE output.id_modality = '1' AND output.id_linac='2' GROUP BY output.date ASC ORDER BY output.date DESC 'r1','r1_dev','r2','r2_dev','date','time' '50.000','0.334449476181','37.470','0.0000364374', '2006-07-27','11:26:00' '39.650','3.7714458','38.020','-0.4945683000','2006-05-12','08:22:38' '39.220','2.8528707','0.000','0.0000000000','2006-05-05','00:00:00' '37.970','-0.2132824','38.510','1.2058597000','2006-04-28','15:49:23'
View Replies !
Grouping Fields And Getting Most Recent Date
I have a table like: table_id table_foreign table_datetime I'd like to select the highest datetime for each foreign. So far I've tried something like this... SELECT * FROM table GROUP BY table_foreign ORDER BY table_datetime DESC Unfortunately, the ordering only works on the grouped resultset, not that the values of the dates.
View Replies !
Join/Select Only Most Recent Record
I have two tables. The first is a list of customers and the second is a list of sales invoices. I want to link the 2 tables but only select the newest/most recent invoice. There can be many invoices for a single customer. Customer Table CustomerID integer (primary key); CustomerName char Sales Table SaleID integer (primary key) CustomerID integer (key from customer table) SalesDate Date SalesAmount integer Here is what I have so far: select customertable.customername, salestable.salesdate, salesamount from customertable inner join salestable on customertable.customerid = salestable.customerid How can I return only a single record for each customer (the most recent/newest sale)?
View Replies !
Max(date) Per KeyField - Get Most Recent Data
I have a large list of daily stats from various systems, i.e. Table Stats SystemID | tagID | dtStamp | Value S001 : T100 : 2006-03-23 : 50 S001 : T101 : 2006-03-23 : 2 S001 : T100 : 2006-03-24 : 53 S001 : T101 : 2006-03-24 : 5 S001 : T100 : 2006-03-25 : 60 S001 : T101 : 2006-03-25 : 8 S001 : T100 : 2006-03-26 : 65 S001 : T101 : 2006-03-26 : 12 S002 : T100 : 2006-03-23 : 10 S002 : T101 : 2006-03-23 : 1 S002 : T100 : 2006-03-24 : 12 S002 : T101 : 2006-03-24 : 2 I need the date of the last record of data for each site for each tag. (i.e. the most recent data for each site) I tried using max(dtStamp), but it found the biggest date in the table and tied it to every system/tag the same. i.e. it showed 2006-03-26 for S002/T101, even though I know I don't have data for that site on that day.
View Replies !
String Date, Selecting The Most Recent
Selecting the last 10 days subscriptions. $Posted = date("F/d/Y, g:i:s"); $sql  = mysql_query("SELECT id FROM Subscribers WHERE activated='1' AND DATE_SUB(CURDATE(),INTERVAL 10 DAY) <= Subscribers.Posted"); If I turned Posted into a timestamp,it works.But I want to keep the date format as it is and still be able to perform that query.
View Replies !
Deleting All Records Exept The Most Recent From Different Users
There are multiple users listed in the table and each user has multiple records. I would like to delete every record from each user with the exception of the most recent from each user. The SQL statment I use is this.. DELETE FROM `phpbb_adr_battle_list` WHERE `battle_id` != <users most recent battle ID> and `battle_challenger_id` = <user id> battle_id is an autoincrementing number so I know the highest number a user has is their most recent battle I manually look at each user, find the highest battle ID they have and use it in the statement. Is there a way to do this automatically? So it would search for the highest battle_id for each user and delete the rest?
View Replies !
Join 2 Tables And Select Only Most Recent Records
I have been wrestling with a way to select only the most recent records for a group of clients without using a subselect. I have two tables: cdp_clients and cdp_level_records related by 'admitid'. For each cdp_client there are multiple cdp_level_records, each with a different 'level_date'. I want to select only the most recent cdp_level_record for each client in cdp_clients.
View Replies !
Most Recent Results, Without Using Subqueries Or Temp Tables.
I have a table whose schema is essentially as follows: Results(name varchar(20), version varchar(20), configuration varchar(20), status varchar(20), datetime timestamp(14)) Essentially, what I would like to accomplish is to get the most recent record for name, and also for the combination of name and version. I'm using MySQL 4.0.12 and therefore do not have access to subqueries, and would like to avoid using a temp table, which I am doing right now with limited success and considerable overhead. I've also tried using a group by statement and using max(datetime) to get the most recent timestamp, but I end up getting the right timestamp, but the incorrect values in all of the other columns.
View Replies !
Delete Oldest Rows Keeping 10 Most Recent, How?
Example: There is a table (NEWS). In that table I want to automatically delete rows so I'll always have 10 rows with the most recent news. The oldest news should be deleted keeping the 10 most recent. TABLE NEWS is something like ID, news, date. I can't figure out how the query should look like. Any ideas?
View Replies !
GROUP BY With Order Inside Group
I have a problem when grouping records - I can't manipulate data inside group. For example, I have table `images` with fields `name` (name of image, not unique) and `dtadded` (date of image adding). Then, I need to get all images names with distinct names where each name must be latest added name. Sample: ------------------------------ id, name, dtadded ------------------------------ 1 name1 2007-10-15 00:00:00 2 name2 2007-10-15 00:00:00 3 name1 2007-10-16 00:00:00 ------------------------------ I need to receive 2 results (for each name) 3 - name1 - 2007-10-16 00:00:00 2 - name2 - 2007-10-15 00:00:00 If I use SQL code: SELECT images.id, images.dtadded, images.name, count(name) FROM images GROUP BY `name` ORDER BY dtadded DESC I get results 1 - name1 - 2007-10-15 00:00:00 2 - name2 - 2007-10-15 00:00:00 It groups records with first row in database, but I need last row in table for each name. Question: How can I order results in side group to get needed results as described above?
View Replies !
Retrieve Most Recent Records From Database Sorted By String Value
I'm having a problem with a simple select query. I've got users uploading data into a database. I want to do something that seems as if it should be really simple, but it isn't so far. Basically, I want to retrieve the latest 12 records from the table, sorted by the date-time field (uploaddate) then by a varchar field (name). Here's the SQL that I'm using: select * from tblcontent where filetypeid=1 and release='Y' order by uploaddate desc, name limit 12; And here's the table schema: Field,Type,Null,Key,Default,Extra ContentID,int(3),,PRI,NULL,auto_increment Name,varchar(50),,,, Filename,varchar(100),,,, Thumbnail,varchar(100),,,, FileTypeID,int(11),,,0, UploadDate,datetime,,,0000-00-00 00:00:00, Description,text,,,, ContributorID,int(10) unsigned,,,0, Release,enum('Y','N'),YES,,N, The problem I'm running into is that while I am retrieving the most recent 12 records, it's not sorting by the varchar field. Instead, the 'Name' field seems randomly sorted. If I change the sort order (name, uploaddate desc), I don't get the records that I want. This seems like it should be something very simple to do, but it's driving me nuts and I really have no idea how to resolve the problem. Code:
View Replies !
Recent Mysql Upgrade, Ignores NOT NULL Declarations?
Can anyone tell me why CREATE TABLE and ALTER TABLE ignore the NOT NULL clause on my mysql 5.0.19 on windows xp? This is problematic because I want to migrate some data to a older version who's NOT NULL fields are behaving as I would expect them to triggers errors when I try to import data with NULL fields. It can be fixed with a search and replace but I'd like to learn more about this problem. Any info?
View Replies !
Top Record From Each Group In GROUP BY
I never could figure out a good way to do this. I am doing a group by statement, and want the top record from each group to be returned only. My query is to find the current win/loss streak for each team. Here is the sql: SELECT id, team_name, result, MIN(date) AS startdate, MAX(date) AS enddate, COUNT(*) AS games FROM ( SELECT t.id, t.team_name, sched.date, CASE sched.draw WHEN 1 THEN 'draw' ELSE CASE sched.winner_id WHEN t.id THEN 'win' ELSE 'loss' END END AS result, ( SELECT COUNT(*) FROM fantasy_schedule sched2 WHERE ( CASE sched2.draw WHEN 1 THEN 'draw' ELSE CASE sched2.winner_id WHEN t.id THEN 'win' ELSE 'loss' END END ) <> result AND sched2.date >= ��-01-01' AND sched2.date < ��-01-01' AND sched2.date < sched.date AND sched2.fantasy_league_id = 6 AND (sched2.fantasy_team1_id = t.id OR sched2.fantasy_team2_id = t.id) ) AS rungroup FROM fantasy_schedule sched JOIN fantasy_team t ON t.id = sched.fantasy_team1_id OR t.id = sched.fantasy_team2_id WHERE sched.last_update < NOW() AND sched.date >= ��-01-01' AND sched.date < ��-01-01' AND sched.fantasy_league_id = 6 ) bighonker GROUP BY id, team_name, result, rungroup ORDER BY enddate DESC And a subset of what is returned: +----+----------------------------+--------+------------+------------+-------+ | id | team_name | result | startdate | enddate | games | +----+----------------------------+--------+------------+------------+-------+ | 1 | The Macho King | loss | 2005-12-17 | 2005-12-17 | 1 | | 1 | The Macho King | win | 2005-12-11 | 2005-12-11 | 1 | | 1 | The Macho King | loss | 2005-11-24 | 2005-12-04 | 2 | | 1 | The Macho King | win | 2005-11-20 | 2005-11-20 | 1 | | 1 | The Macho King | loss | 2005-11-13 | 2005-11-13 | 1 | | 1 | The Macho King | win | 2005-10-30 | 2005-11-06 | 2 | | 1 | The Macho King | loss | 2005-10-16 | 2005-10-23 | 2 | | 1 | The Macho King | win | 2005-10-09 | 2005-10-09 | 1 | | 1 | The Macho King | loss | 2005-10-02 | 2005-10-02 | 1 | | 1 | The Macho King | win | 2005-09-08 | 2005-09-25 | 3 | | 2 | General Grievous | loss | 2005-12-17 | 2005-12-17 | 1 | | 2 | General Grievous | win | 2005-12-11 | 2005-12-11 | 1 | | 2 | General Grievous | loss | 2005-11-24 | 2005-12-04 | 2 | | 2 | General Grievous | win | 2005-11-13 | 2005-11-20 | 2 | | 2 | General Grievous | loss | 2005-11-06 | 2005-11-06 | 1 | | 2 | General Grievous | win | 2005-10-23 | 2005-10-30 | 2 | | 2 | General Grievous | loss | 2005-10-16 | 2005-10-16 | 1 | | 2 | General Grievous | win | 2005-10-02 | 2005-10-09 | 2 | | 2 | General Grievous | loss | 2005-09-08 | 2005-09-25 | 3 | | 3 | Ultimate Jarin | win | 2005-11-06 | 2005-12-24 | 8 | | 3 | Ultimate Jarin | loss | 2005-10-23 | 2005-10-30 | 2 | | 3 | Ultimate Jarin | win | 2005-10-09 | 2005-10-16 | 2 | | 3 | Ultimate Jarin | loss | 2005-09-25 | 2005-10-02 | 2 | | 3 | Ultimate Jarin | win | 2005-09-08 | 2005-09-18 | 2 | | 4 | The Hulkamaniacs | loss | 2005-12-11 | 2005-12-11 | 1 | | 4 | The Hulkamaniacs | win | 2005-12-04 | 2005-12-04 | 1 | | 4 | The Hulkamaniacs | loss | 2005-11-24 | 2005-11-24 | 1 | | 4 | The Hulkamaniacs | win | 2005-11-06 | 2005-11-20 | 3 | | 4 | The Hulkamaniacs | loss | 2005-10-30 | 2005-10-30 | 1 | | 4 | The Hulkamaniacs | win | 2005-10-09 | 2005-10-23 | 3 | | 4 | The Hulkamaniacs | loss | 2005-09-18 | 2005-10-02 | 3 | | 4 | The Hulkamaniacs | win | 2005-09-08 | 2005-09-08 | 1 | | 5 | WHO WANTS TO KISS HOMER? | win | 2005-12-04 | 2005-12-04 | 1 | | 5 | WHO WANTS TO KISS HOMER? | loss | 2005-11-20 | 2005-11-24 | 2 | | 5 | WHO WANTS TO KISS HOMER? | win | 2005-11-13 | 2005-11-13 | 1 | The top record in each group is the current streak. I want only that top record for each teamr returned. I could simply 'order by enddate DESC' and in my php script loop until I have one record per team, but is there any way I can only return the top result for each team in the query itself? Here is the corresponding table schema: +-------------------+---------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+---------------------+------+-----+---------------------+----------------+ | id | int(10) unsigned | | PRI | NULL | auto_increment | | fantasy_league_id | int(10) unsigned | | MUL | 0 | | | week_num | tinyint(3) unsigned | | MUL | 0 | | | fantasy_team1_id | int(10) unsigned | | MUL | 0 | | | fantasy_team2_id | int(10) unsigned | | MUL | 0 | | | winner_id | int(10) unsigned | YES | | 0 | | | date | date | | | 0000-00-00 | | | last_update | datetime | | | 0000-00-00 00:00:00 | | | draw | tinyint(3) unsigned | | | 0 | | +-------------------+---------------------+------+-----+---------------------+----------------+ fantasy_team +---------------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------------+-----------------------+------+-----+---------+----------------+ | id | int(10) unsigned | | PRI | NULL | auto_increment | | fantasy_league_id | int(10) unsigned | | MUL | 0 | | | user_id | mediumint(8) unsigned | | MUL | 0 | | | team_name | char(40) | | | | | | fantasy_division_id | int(10) unsigned | YES | MUL | NULL | +---------------------+-----------------------+------+-----+---------+----------------+
View Replies !
|
TRACKER
