My problem is the following: My tables are MESSAGE and MESSAGE_COMMENT, MESSAGE (id,content) MESSAGE_COMMENT (id, message_id, content) I need to select all messages and max 3 comments for each message, like in this example: type | id | content M 15 "this is a message with no comments" M 16 "this is another message wit...
How would you write a MySQL query that will limit the results of a joined table (or sub select if that works better) and also counts the number of items in the joined table or tables? For instance, let's say you had three tables: projects, tasks and comments, where a project has 0 or more tasks and a task has 0 or more comments. How wo...
I have this data. I need to get the lowest $ full rows for each person. Amount Date Name $123 Jun 1 Peter $120 Jun 5 Peter $123 Jun 5 Paul $100 Jun 1 Paul $220 Jun 3 Paul The result of the SQl Server query should be: $120 Jun 5 Peter $100 Jun 1 Paul ...
I need a SQL query that returns the top 2 Plans by PlanDate per ClientID. This is all on one table where PlanID is the PrimaryID, ClientID is a foreignID. This is what I have so far --> SELECT * FROM [dbo].[tblPlan] WHERE [PlanID] IN (SELECT TOP (2) PlanID FROM [dbo].[tblPlan] ORDER BY [PlanDate] DESC) This, obviously, only retu...
i have 2 tables products and cost PRODUCT ProdCode - PK ProdName COST Effectivedate - PK RetailCOst Prodcode i tried this query: SELECT a.ProdCOde AS id, MAX(EffectiveDate) AS edate, RetailCOst AS retail FROM cost a INNER JOIN product b USING (ProdCode) WHERE EffectiveDate <= '2009-10-01' GROUP BY a.ProdCode; uhm yah its show...
Like, there is top keyword in sql server 2005, how to select top 1 row in mysql if i have join on multiple table & want to retrieve extreme of each ID/column. Limit restricts the no. of row returns so it can't solve my problem. ...
In the above FldID 52 = Description and FldID 54 = HistoryDetail For any given date the output should be the last entry for that date. Also the columns need to be become rows. User will provide 2 dates. Say March 2, 2010 and March 3, 2010. So output in above case should be Since Rev 6 does not have an entry for FldID 52...
I have a table with 3 fields: ID (not unique, not primary key) timestamp marker I want to get the last entry for every distinct ID, but when I doSELECT ID, max(timestamp) from table GROUP BY IDthe marker field is wrong. I have another table with the following fields: ID (unique, primary key) lat lng I would like to perform the s...
I have a bidding table, as follows: SellID INT FOREIGN KEY REFERENCES SellItem(SellID), CusID INT FOREIGN KEY REFERENCES Customer(CusID), Amount FLOAT NOT NULL, BidTime DATETIME DEFAULT getdate() Now in my website I need to show the user the current bids; only the highest bid but without repeating the same user. SELECT CusID, ...
I have a table within my database that has many records, some records share the same value for one of the columns. e.g. | id | name | software | ______________________________ | 1 | john | photoshop | | 2 | paul | photoshop | | 3 | gary | textmate | | 4 | ade | fireworks | | 5 | fred | textmate | |...
I have one table, which has three fields and data. Name , Top , Total cat , 1 , 10 dog , 2 , 7 cat , 3 , 20 horse , 4 , 4 cat , 5 , 10 dog , 6 , 9 I want to select the record which has highest value of Total for each Name, so my result should be like this: Name , Top , Total cat , 3 ...
I have two tables set up similar to this (simplified for the quest): actions- id - user_id - action - time users - id - name I want to output the latest action for each user. I have no idea how to go about it. I'm not great with SQL, but from what I've looked up, it should look something like the following. not sure though. SEL...
Hey guys I have a query that currently finds the latest comment for each of a user's topics and then orders topics by that comment's timestamp. What I want to do is expand on this query's use and print the latest comment for each topic. The problem with this query is that while it orders the topics correctly, it prints seemingly random c...
I have a tblMachineReports with the columns: Status(varchar),LogDate(datetime),Category(varchar), and MachineID(int). I want to retrieve the latest status update from each category for every machine, so in effect getting a snapshot of the latest statuses of all the machines unique to their MachineID. The table data would look like Cat...
I need to write a SQL-Server query but I don't know how to solve. I have a table RealtimeData with data: Time | Value 4/29/2009 12:00:00 AM | 3672.0000 4/29/2009 12:01:00 AM | 3645.0000 4/29/2009 12:02:00 AM | 3677.0000 4/29/2009 12:03:00 AM | 3634.0000 4/29/2009 12:04:00 AM | 3676.0000 // ...
I've got an Oracle 10g database with a table with a structure and content very similar to the following: CREATE TABLE MyTable ( id INTEGER PRIMARY KEY, otherData VARCHAR2(100), submitted DATE ); INSERT INTO MyTable VALUES (1, 'a', TO_DATE('28/04/2010 05:13', ''DD/MM/YYYY HH24:MI)); INSERT INTO MyTable VALUES (2, '...
I'm creating query to retrieve the latest posts in a forum using a SQL DB. I've got a table called "Post". Each post has a foreign key relation to a "Thread" and a "User" as well as a creation date. The trick is I don't want to show two posts by the same user or two posts in the same thread. Is it possible to create a query that contai...
I have a Table structure as id, trackid, table_name, operation, oldvalue, newvalue, field, changedonetime Now if I have 3 rows for the same "trackid" same "field", then how can i select the latest out of the three? i.e. for e.g.: id = 100 trackid = 152 table_name = jos_menu operation= UPDATE oldvalue = IPL newvalu...
I have a table of all sales defined like: mysql> describe saledata; +-------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+---------------------+------+-----+---------+-------+ | SaleDate | datetime | N...
I have a query which I am wondering if the result I am getting is the one that I am expecting. The table structure goes like this : Table : results ID TestCase Set Analyzed Verdict StartTime Platform 1 1010101 ros2 false fail 18/04/2010 20:23:44 P1 2 1010101 ros3 false fail 19/04/2010 22:22:33 P...