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 ...
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...
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...
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...
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...
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-...
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...
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...
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...
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...
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 ...
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...
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...
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.
...
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...
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 ...
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...
(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?
...
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...
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...