greatest-n-per-group

ActiveRecord nested SELECT -- can I do it without manual SQL?

I have a table with (among other things) a name and a rank. I'd like to return the set of all unique names, but for each name returned, I'd like to pick the row with the highest rank. This is simple with two nested SELECT statements: SELECT * FROM (SELECT * FROM foo ORDER BY rank DESC) AS ordered GROUP BY name MySQL takes the first ...

ROW_NUMBER() in MySQL

Is there a nice way in MySQL to replicate the MS SQL Server function ROW_NUMBER()? For example: SELECT col1, col2, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS intRow FROM Table1 Then I could, for example, add a condition to limit intRow to 1 to get a single row with the highest col3 for each (col1, col...

Retrieve 2 last posts for each category.

Hello, Lets say I have 2 tables: blog_posts and categories. Each blog post belongs to only ONE category, so there is basically a foreign key between the 2 tables here. I would like to retrieve the 2 lasts posts from each category, is it possible to achieve this in a single request? GROUP BY would group everything and leave me with only...

Select newest entry from a joined MySQL table

I have stock quantity information in my database. 1 table, "stock", holds the productid (sku) along with the quantity and the filename from where it came. The other table, "stockfile", contains all the processed filenames along with dates. Now I need to get all the products with their latest stock quantity values. This gives me ALL th...

SQL Query to select bottom 2 from each category

In Mysql, I want to select the bottom 2 items from each category Category Value 1 1.3 1 4.8 1 3.7 1 1.6 2 9.5 2 9.9 2 9.2 2 10.3 3 4 3 8 3 16 Giving me: Category Value 1 1.3 1 1.6 2 9.5 2 9.2 3 4 3 8 Before I migra...

How Do I Select the Entire Record When Using MAX() With GROUP BY

Using MYSQL I would like to refactor the following SELECT statement to return the entire record containing the newest invoice_date: > SELECT id, invoice, invoice_date FROM invoice_items WHERE lot = 1047 id invoice_id invoice_date ----------------------------------- 3235 1047 2009-12-15 11:40:00 3295 1047 2009-...

Group By/Threaded Messaging MySQL Prob

I'm building a private messaging system for a dating site..having troubles with a group by query. Here is the structure of the table: `id` bigint (20) NOT NULL AUTO_INCREMENT , `fromme` integer (11) NOT NULL, `tome` integer (11) NOT NULL, `subject` varchar (255) NOT NULL, `message` longtext NOT NULL, `mydate` datetime NOT NULL, `t...

mysql query tuning

Hello, I have a query that I have made into a MYSQL view. This particular view is central to our application so we are looking at tuning it. There is a primary key on Map_Id,User_No,X,Y. I am pretty comfortable tuning SQL server queries but not totally sure about how MySql works in this aspect. Would it help to put an index on it tha...

sql problem,challenge

I want to get id a b c -------------------- 1 1 100 90 6 2 50 100 ...from: id a b c -------------------- 1 1 100 90 2 1 300 50 3 1 200 20 4 2 200 30 5 2 300 70 6 2 50 100 It's the row with the smallest b group by a. How to do it with sql? EDIT I...

Select the newest entry based on other entries with the same values?

Lets say I have a table with some data like the following: ID text OtherID _______________________ 6 text1 24 7 text2 24 8 text3 24 9 text1 25 10 text2 25 As you can see I have multiple entries with the same OtherID. what would be an sql statement that would select only the newe...

Seemingly Simple Query in Pl Sql

I have a table "defects" in the following format: id status stat_date line div area 1 Open 09/21/09 F A cube 1 closed 01/01/10 F A cube 2 Open 10/23/09 B C Back 3 Open 11/08/09 S B Front 3 closed 12/12/09 S B Front My problem is that I want to ...

SQL - help on "top 1 by group"

Relevant tables: DepartmentPhone: DepartmentPhoneID int, DepartmentID int, PhoneID int Phone: PhoneID int, PhoneType int There are 6 phones with PhoneType=4 that belong to DepartmentID=2. So this produces 6 records: select * from DepartmentPhone join Phone on Phone.PhoneID = DepartmentPhone.PhoneID and Phone.PhoneType = 4 where Depar...

selecting latest rows per disctinct foreign key value

excuse the title, i couldn't come up with something short and to the point... I've got a table 'updates' with the three columns, text, typeid, created - text is a text field, typeid is a foreign key from a 'type' table and created is a timestamp. A user is entering an update and select the 'type' it corresponds too. There's a correspon...

Top Values in a Query by Group

I know how to get the top values but am having trouble with something very simple. I have a student table. It has: name numberoflaps grade I want the get a query or report that shows the top two kids with the most laps per grade. ...

SQL - ID is lost after performing MIN then GROUP BY

To simplify, if I had a table called 'employee': id INT NOT NULL PRIMARY KEY, salary FLOAT, department VARCHAR(255) I want to perform and query where I retrieve the minimum salary in each department. So my query is something like this: SELECT employee.ID, MIN(employee.salary), employee.department FROM employee GROUP BY employee.depar...

Mysql subquery fails, need help :)

I have a table PICTURES: username varchar(50) picture_id varchar(50) datetime ...and I have a table FRIENDS: user_1 varchar(50) user_2 varchar(50) datetime When you have friends on the website your username goes in user_1, and your friend username's go in user_2. For each new friend a new row... I want to show the 5 last ...

SQL join: selecting the last records in a one-to-many relationship

Suppose I have a table of customers and a table of purchases. Each purchase belongs to one customer. I want to get a list of all customers along with their last purchase in one SELECT statement. What is the best practice? Any advice on building indexes? Please use these table/column names in your answer: customer: id, name purchase: i...

How can a do a "greatest-n-per-group" query in django?

(This is the django version of the thread at http://stackoverflow.com/questions/2111384/) Suppose I have a table of customers and a table of purchases. Each purchase belongs to one customer. I want to get a list of all customers along with their last purchase. Can it be done without raw SQL and without multiple database queries? ...

How to order, group, order with mySQL

Here is a simplified version of my table tbl_records -title -created -views I am wondering how I can make a query where they are grouped by title, but the record that is returned for each group is the most recently created. I then will order it by views. One way I guess is to do a sub query and order it by created and then group it b...

mysql: Using LIMIT within GROUP BY to get N results per group?

The following query: SELECT year, id, rate FROM h WHERE year BETWEEN 2000 AND 2009 AND id IN (SELECT rid FROM table2) GROUP BY id, year ORDER BY id, rate DESC yields: year id rate 2006 p01 8 2003 p01 7.4 2008 p01 6.8 2001 p01 5.9 2007 p01 5.3 2009 p01 4.4 2002 p01 3.9 2004 p01 3.5 2005 p01 2.1 2000 p...