Table: UserId, Value, Date. I want to get the UserId, Value for the max(Date) for each UserId. That is, the Value for each UserId that has the latest date. Is there a way to do this simply in SQL? (Preferably Oracle) Thank in advance! [Update:] Apologies for any ambiguity: I need to get ALL the UserIds. But for each UserId, only that ...
Say I have two tables I want to join. Categories: id name ---------- 1 Cars 2 Games 3 Pencils And items: id categoryid itemname --------------------------- 1 1 Ford 2 1 BMW 3 1 VW 4 2 Tetris 5 2 Pong 6 3 Foobar Pencil Factory I want a...
Hi, I have an articles table and a categories table. I want to fetch 7 articles for each category. Currently I have this but it's terrible slow on large tables so it's not really a solution: SELECT id, title, categories_id, body, DATE_FORMAT(pubdate, "%d/%m/%y %H:%i") as pubdate FROM articles AS t WH...
How can you join between a table with a sparse number of dates and another table with an exhaustive number of dates such that the gaps between the sparse dates take the values of the previous sparse date? Illustrative example: PRICE table (sparse dates): date itemid price 2008-12-04 1 $1 2008-12-11 1 $3 2008-12-15...
I've got the following 2 tables: ingredients (id, name) ingredient_prices (id, ingredient_id, price, created_at) such that one ingredient can have many prices. What will be the best way to get the latest entered price per ingredient? I know it can be done easily with sub-selects, but I want to know if there is a more efficient way fo...
I have data that looks like this: entities id name 1 Apple 2 Orange 3 Banana Periodically, a process will run and give a score to each entity. The process generates the data and adds it to a scores table like so: scores id entity_id score date_added 1 1 10 1/2/09 2 2 ...
OK I have a table like this: ID Signal Station OwnerID 111 -120 Home 1 111 -130 Car 1 111 -135 Work 2 222 -98 Home 2 222 -95 Work 1 222 -103 Work 2 This is all for the same day. I just need the Query to return the max signal for each...
The problem: I need to find all active [GiftPledges] that have the last three [GiftDetails] have a zero amount. SELECT gp.PledgeId FROM GiftPledge gp INNER JOIN GiftDetail gd ON gp.PledgeId = gd.PledgeId WHERE gp.PledgeStatus = 'A' GROUP BY PledgeId HAVING COUNT(PledgeId) >= 3 Now, I have all my [GiftPledges] t...
What I need done is simple... but its 3am and Im probably overlooking the obvious. Im coding a simple forum. One table stores the forum titles, descriptions, etc, while the other stores the posts. In the forum listing, that shows the list of all forums, I want to grab the latest post in each forum, and display the post subject, poster a...
I have two tables: article('id', 'ticket_id', 'incoming_time', 'to', 'from', 'message') ticket('id', 'queue_id') where tickets represent a thread of emails between support staff and customers, and articles are the individual messages that compose a thread. I'm looking to find the article with the highest incoming time (expressed as ...
Let's say I have a table "uservalue" with the following columns: integer user_id integer group_id integer value I can get the maximum value for each group easily: select max(value) from uservalue group by group_id; What I would like is for it to return the user_id in each group that had the highest value. The max function in matla...
I have this table : And I would like to make a request that would return for each deal_id the row with the highest timestamp, and the corresponding status_id. So for this example, I would have returned 2 rows : 1226, 3, 2009-08-18 12:10:25 1227, 2, 2009-08-17 14:31:25 I tried to do it with this query SELECT deal_id, status_id, ma...
There is a table "messages" that contains data as shown below: Id Name Other_Columns ------------------------- 1 A A_data_1 2 A A_data_2 3 A A_data_3 4 B B_data_1 5 B B_data_2 6 C C_data_1 If I run a query "select * from messages group by name", I will get the result as: 1 ...
I have this table CREATE TABLE `codes` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `language_id` int(11) unsigned NOT NULL, `title` varchar(60) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `time_posted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 lan...
Lets say I have a database table called "Scrape" possibly setup like: UserID (int) UserName (varchar) Wins (int) Losses (int) ScrapeDate (datetime) I'm trying to be able to rank my users based on their Wins/Loss ratio. However, each week I'll be scraping for new data on the users and making another entry in the Scrape table...
I have this releases table in a SQLite3 database, listing each released version of an application: |release_id|release_date|app_id| |==========|============|======| | 1001| 2009-01-01 | 1| | 1003| 2009-01-01 | 1| | 1004| 2009-02-02 | 2| | 1005| 2009-01-15 | 1| So for each app_id, there will be multi...
How do I get the most frequently occurring category for each tag in MySQL? Ideally, I would want to simulate an aggregate function that would calculate the mode of a column. SELECT t.tag , s.category FROM tags t LEFT JOIN stuff s USING (id) ORDER BY tag; +------------------+----------+ | tag | category | +-------...
Hi, I have a class Marketorders which contains information about single market orders and they are gathered in snapshots of the market (represented by class Snapshot). Each order can appear in more than one snapshot with the latest row of course being the relevant one. class Marketorders(models.Model): id = models.AutoField(primary...
I have two tables I need to select data from TABLE_A and TABLE_B; they have a one to many relationship. In my select statement I will often get multiple unique results from TABLE_A and this is fine. But I will also get multiple matches in TABLE_B - I need to get the most recent TABLE_B record that matches. I have an auto incremented id ...
I have a database of items. Each item is categorized with a category ID from a category table. I am trying to create a page that lists every category, and underneath each category I want to show the 4 newest items in that category. For Example: Pet Supplies img1 img2 img3 img4 Pet Food img1 img2 img3 img4 I know that I could easily...