greatest-n-per-group

T-SQL: Selecting Column Based on MAX(Other Column)

I'm hoping there's a simple way to do this without using a sub-query: Scenario: You have "TableA" with columns "Key", "SubKey", and "Value". I need to get the "Value" of the MAX("SubKey") for a given "Key". So if the Table contained the rows: KEY SUBKEY VALUE 1 1 100 1 2 200 1 3 300 For Key = 1, I need the val...

MySQL: Limit a query to 10 rows where every index has 5 tags.

Hi, I'd like to make a query where I can retrieve an interval of 10 rows with 5 tags per each one. I have houses and tags table, I know how to do it only for simple queries with a SELECT using LIMIT but how can I do it in this case? table houses id house country 1 Grunt Mansion us 2 Hororo Suneku jp 3 Ca...

How to make this Mysql query work?

My query: SELECT * FROM forum_topics WHERE cat_id IN(1,2,3,4,5,6,7) ORDER BY last_message DESC LIMIT 7 I want to get the biggest and only one value of each cat_id (7 values total). How to correct this query to make it work if it's even possible? There is forum topics and each has value last_message and I want to get the latest to...

Need MySQL help

Hey folks :) I'm having a little trouble with an sql query. Which is why I hope some of you will be able to help me. I've got an query that selects the latest entry from each relational id but i want to select the latest where the status is different. The table looks like this. +-----------+-------------+------+-----+-----------------...

Return 1 result per left join

Currently I am performing a left join on two tables. The first table has an id and a persons name, the second table has an id, the id of a person from table 1, and then a timestamp (of a flight). People Flights id | name id | person_id | time ------------ ---------------------...

SQL: How to select the record with the most recent past date for every record on other table.

How can I reformulate this two queries in one single query on MySQL? SELECT * FROM tvnetwork //this query gives me a list of TV networks But networks usually change names and logos, so for each of the retrieved TV networks: SELECT name, logo FROM tvnetworkinfo WHERE id = $tvnetwork.id AND date_since < NOW() ORDER BY date_...

COUNT results from SQL Query with a HAVING clause

Are you able to use COUNT in a query with a HAVING clause so that the COUNT returns the number of rows? When I try, Im getting the count of the number of times the ID shows up in the table. Here is the query: SELECT col_appid, min(col_payment_issued_date) as PayDate FROM tbl_ui_paymentstubs WHERE isnull(col_payment_amount,0) > 0...

MYSQL Select 5 records from one table based on several different values from another

I am by no means fluent in MYSQL. What I am trying to do is I have a table that has a list of Galleries with unique ID numbers. I have another table ImageData that has images that may be as many as 1000 that are related to each GalleryID that is in the first table. I want to do a select that will get 5 random records from each Gallery...

Mysql. Order locations and join listings randomly

I have two tables: locations and listings. locations id title address latitude longitude listings id location info status SELECT locations.title, locations.address, ( 3959 * acos( cos( radians('".$center_lat."') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('".$center_lng."') ) + sin( radians...

group by problem

SQL group by problem  I have a SQL group by problem. My table has the following form. Cust_id.  Price_id     Price. ---------------------------- 1.          556.        5000. ----------------------------- 2.          654.        600. 2.          432.         487. 2.          546.         500. --------------------------- 3. ...

Help with MySQL query that is based on the number of clicks on an item

I have a table setup like this (simplified for example): user_id item_id click_dt Each item_id has many user_id's. Basically it's storing the clicks on items, associated to a user_id. I want to query through this and list only the latest user_id for each item_id, based on click_dt. So if there are 5 clicks for item_id 55, the last ...

SQL: Select first row in each a GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a "purchases" table that looks like this: > SELECT * FROM purchases: id | customer | total 1 | Joe | 5 2 | Sally | 3 3 | Joe | 2 4 | Sally | 1 I'd like to query for "the id of the largest...

Select most recent rows that match a condition in SQLite

Let's say I have a table: Name, status, timestamp And I want to select the rows that match status='active' but only those that have the most recent timestamp for each of them. So if there were rows like this: Bob, active, 5/10/2010 Bob, active, 6/12/2010 Ann, inactive, 6/12/2000 Ann, active, 9/3/2009 Ann, active, 9/25/2010 I'd wan...

Select most recent results based on composite of fields

I'm using MySQL, but I think this is a basic SQL question. I don't know how else to ask but to give an example. Say I have this data in my table: id date_time foreign_key key value 1 2010-01-01 00:00:00 1 'temperature' 84 2 2010-01-01 00:00:01 1 'humidity' 34 3 ...

MySql Select statement distinct latest date value based on Primary ID of Keyword

Basically I have a list of SEO keywords in one table and their Ranking including Date entered in another table. keywords [keywordID(PKID)] [keyword] ---------------------------- 1 SEO 2 Vancouver Homes keywordRanking [rankingID(PKID)] [keywordID(FKID)] [ranking] [rankingDate(timestamp)] --------------...

Select N records for each category and order by X

I have a database table that contains blog posts. I want to show on the homepage one (or more) post for each category, ordering by date, for example. So my posts table looks like this: id | title | description | cat | filename | date How would I create such a query? I've thought to use group-by or a subselect but I'm not sure if it's ...

(MySQL) Greatest Number By group Inner join , performance problems.

2 tables are still very small compared on how they will be in the future. Already on doing tests on some regular queries , performance problems arise. Could someone enlighten me on a better table structure or query ? ==== Table 1 : tv_show ( currently 117 rows) Table 2 : tv_episodes ( currently 43,000 rows ). tv_show contains an ...

MySQL greatest-n-per-group trouble

Hey everyone. I believe this is a 'greatest-n-per-group' question but even after looking at several questions on StackOverflow, I'm unsure how to apply this to my situation... I'm using a MySQL database and have a basic blog-type system set up about Computer Applications... The tables look like this: POSTS post_id post_created post_t...

Limitations of GROUP BY

Disclaimer: I'm an SQL newb and this is for a class, but I could really use a poke in the right direction. I've got these three tables: student(_sid_, sname, sex, age, year, gpa) section(_dname_, _cno_, _sectno_, pname) enroll(_sid_, grade, _dname_, _cno_, _sectno_) (primary keys denoted by underscores) I'm trying to write an Oracle...

How to generate these two reports with MySQL?

My Schema I have the following tables table notes/example values ------------------------------------------------ users ( id email # "[email protected]" ) games ( id name # "Space Invaders", "Asteroids", "Centipede" ) players ( ...