greatest-n-per-group

select distinct col1 with min(col2) and max(col3) from table

My table looks like this with duplicates in col1 col1, col2, col3, col4 1, 1, 0, a 1, 2, 1, a 1, 3, 1, a 2, 4, 1, b 3, 5, 0, c I want to select distinct ...

MySQL - Counting rows in preparation for greatest-n-per-group not working?

Referring to SO and other sites have given me examples of how to use MySQL to create a 'greatest-n-per-group' query. My variant on this would be to have a query that returns the first 3 rows of each category. As the basis for this I need to sort my data into a usable sequence and that is where my problems start. Running just the seque...

how to get latest entry of a item when item have multiple rows?

i have an table tbl_exp id| exp_id|qnty| last_update 1 | 12 | 10|2010-05-18 19:34:29 2 | 13 | 50|2010-05-19 19:34:29 3 | 12 | 50|2010-05-19 19:34:29 4 | 15 | 50|2010-05-18 19:34:29 5 | 18 | 50|2010-05-20 19:34:29 6 | 13 | 70|2010-05-20 19:34:29 now i need only latest entry of each exp_id id| exp...

How to get all the fields of a row using the SQL MAX function?

Consider this table (from http://www.tizag.com/mysqlTutorial/mysqlmax.php): Id name type price 123451 Park's Great Hits Music 19.99 123452 Silly Puddy Toy 3.99 123453 Playstation Toy 89.95 123454 Men's T-Shirt Clothing 32.50 123455 Blouse Clothing 34.97 123456 Elec...

SQL Server Distinct Question

I need to be able to select only the first row for each name that has the greatest value. I have a table with the following: id name value 0 JOHN 123 1 STEVE 125 2 JOHN 127 3 JOHN 126 So I am looking to return: id name value 1 STEVE 125 2 JOHN 127 Any idea on the MSSQL Syntax on how to perform this operation?...

SQL - get the article title with the last comment date

I have articles table, article_comments. I want to get the value: last_modified_all of article. I need this value to be the great date of this two: the last_modified field of articles table. the last comment of article_comments last_modified column. Can someone help me? the structure is very simple, and you can guess is without pro...

Is there a way to find TOP X records with grouped data?

I'm working with a Sybase 12.5 server and I have a table defined as such: CREATE TABLE SomeTable( [GroupID] [int] NOT NULL, [DateStamp] [datetime] NOT NULL, [SomeName] varchar(100), PRIMARY KEY CLUSTERED (GroupID,DateStamp) ) I want to be able to list, per [GroupID], only the latest X records by [DateStamp]. The kicke...

mysql query - blog posts and comments with limit

Hi, I have 2 tables: comments and posts and I'd like to display a list of 15 posts and maximum 2 most recent comments under each blog post the database scheme looks like this posts_table: post_id, post_txt, post_timestamp comments_table: post_id, comment_txt, comment_timestamp how the mysql query should look like to select 15 posts ...

How to SELECT DISTINCT Info with TOP 1 Info and an Order By FROM the Top 1 Info

I have 2 tables, that look like: CustomerInfo(CustomterID, CustomerName) CustomerReviews(ReviewID, CustomerID, Review, Score) I want to search reviews for a string and return CustomerInfo.CustomerID and CustomerInfo.CustomerName. However, I only want to show distinct CustomerID and CustomerName along with just one of their CustomerRevi...

how do I get the 2 most recent records

I have a table similar to the example shown below. I would like to be able to select the two most recent entrys for each accountNo. I am using Microsoft SQL 2000. Thank you for any help that you can provide. AccountNo, DateOfOrder, OrderID ----------------------------------------- 123, March 1 2010, 1 222, March ...

SQL query for selecting the firsts in a series by column

I'm having some trouble coming up with a query for what I am trying to do. I've got a table we'll call 'Movements' with the following columns: RecID(Key), Element(f-key), Time(datetime), Room(int) The table is holding a history of Movements for the Elements. One record contains the element the record is for, the time of the recorded l...

Order By, Group By - Problems

Ok, so here's the issue. Currently I'm using the symfony framework to develop a web app. I'm using MySQL in the backend but want to keep my queries in their doctrine format to avoid any problems if I ever need to change databases. In one section of the web application I'm looking for the last comment associated by any distinct user, s...

How to Select only one unique record from the Table using Distinct

my table have several records which has the same MemberID. i want to result out only one record. select DISTINCT(MemberID) from AnnualFees; then result will come. but i want to show the other column data also but when i do this select DISTINCT(MemberID),StartingDate,ExpiryDate,Amount from AnnualFees; all the details including s...

Join single row from a table in MySQL

I have two tables players and scores. I want to generate a report that looks something like this: player first score points foo 2010-05-20 19 bar 2010-04-15 29 baz 2010-02-04 13 Right now, my query looks something like this: select p.name player, m...

Select exactly one row for each employee using unordered field as criteria

I have a data set that looks like the following. EMPLID PHONE_TYPE PHONE ------ ---------- -------- 100 HOME 111-1111 100 WORK 222-2222 101 HOME 333-3333 102 WORK 444-4444 103 OTHER 555-5555 I want to select exactly one row for each employee using the PHONE_TYPE field to establ...

Select items that are the top N results for a related table

Say I have a game where a question is asked, people post responses which are scored, and the top 10 responses win. I have a SQL database that stores all of this information, so I might have tables such as Users, Questions, and Responses. The Responses table has foreign_keys user_id and question_id, and attribute total_score. Obviously, ...

How to return additional fields based on aggregate function ?

I have two tables, jobcli client_id job_id jobs job_id jobTitle startDate projectmgr What I am having trouble with is to display client_id, job_id, jobTitle, startDate, and projectmgr in a query, where the results are grouped by client_id and have max of date. So far I can get a list that's groupped by client_id with their c...

MySQL Query Design for Latest Post per Forum

Hello, i have this 3 tables forums_forum +-----+--------+-------------+-------+-----+ | fid | name | description | index | cid | +-----+--------+-------------+-------+-----+ | 36 | gdghdf | hjghj | 54 | 5 | | 45 | yutuy | iuyi | 99 | 6 | +-----+--------+-------------+-------+-----+ forums_threads +----+--...

SQL for Selecting fix number of row "families"

Hello, Given table | id | user | | 1 | 1 | | 1 | 2 | | 1 | 3 | | 1 | 4 | | 2 | 5 | | 2 | 6 | | 2 | 7 | | 2 | 8 | I want to write a query that will return 3 rows for id=1 and 3 rows for id=2 (order does not matter, yet I would assume that it can be enforced). So the end result should be something lik...

Retrieveing the most recent records within a query

I have the following tables: tblPerson: PersonID | Name --------------------- 1 | John Smith 2 | Jane Doe 3 | David Hoshi tblLocation: LocationID | Timestamp | PersonID | X | Y | Z | More Columns... --------------------------------------------------------------- 40 | Jan. 1st | 3 | 0 | 0 | 0 | M...