greatest-n-per-group

Selecting distinct identity rows based on the lowest value of a joined priority column

Simplified table structures, all INT columns and no PKs outside of the identity columns: Nodes (n) table: id Attributes (a) table: id, node_id, type_id Type (t) table: id, priority I'm trying to select a set of attributes, each of which has the lowest type.priority for its respective node. Though there are multiple attributes per nod...

SQL Server - SELECT TOP 5 rows for each FK

I've got the following query, that looks up the TOP 5 Products matching the search. Each Product is associated with a Shop SELECT TOP 5 * FROM Products p, Shops s WHERE p.ShopId = s.ShopId AND p.ProductName LIKE '%christmas%' I need to extend this so that it returns me the TOP 5 Products in each Shop. Could any...

No idea how to write complex SQL query

Hi I have following db structure: File, User, FileRevision (has foreign key to File, and many-2-many connection through intermediate table to User). I want to fetch all FileRevision-s that: are newest/freshest in their corresponding File-s, have many-2-many link to User that performs search (permission checking). I found out that...

select maximum score grouped by date, display full datetime

Gday, I have a table that shows a series of scores and datetimes those scores occurred. I'd like to select the maximum of these scores for each day, but display the datetime that the score occurred. I am using an Oracle database (10g) and the table is structured like so: scoredatetime score (integer) ------------...

Select Earliest Date and Time from List of Distinct User Sessions

I have a table of user access sessions which records website visitor activity: accessid, userid, date, time, url I'm trying to retrieve all distinct sessions for userid 1234, as well as the earliest date and time for each of those distinct sessions. SELECT DISTINCT accessid, date, time FROM accesslog WHERE userid = '1234' G...

SQL Statement(s)

If I have the following table: CREATE TABLE #temp ( id int, num int, question varchar(50), qversion int ); INSERT INTO #temp VALUES(1, 1, 'Question 1 v1', 1); INSERT INTO #temp VALUES(2, 1, 'Question 1 v2', 2); INSERT INTO #temp VALUES(3, 2, 'Question 2 v1', 1); INSERT INTO #temp VALUES(4, 2, 'Question 2 v2', 2); INSERT...

How to make a SQL query for last transaction of every account?

Say I have a table "transactions" that has columns "acct_id" "trans_date" and "trans_type" and I want to filter this table so that I have just the last transaction for each account. Clearly I could do something like SELECT acct_id, max(trans_date) as trans_date FROM transactions GROUP BY acct_id; but then I lose my trans_type. I c...

Implementing MOST recent comment - SQL Server

So suppose I have a bunch of blog entries and I want to find out the most recent comment in each of them, how would I go about finding that out in SQL Server. I have a list of integer id's of these blog entries in a temp table. Something like select top 1 does not work in this case. The approach coming to my mind is looping, and we a...

one-to-many query selecting all parents and single top child for each parent

There are two SQL tables: Parents: +--+---------+ |id| text | +--+---------+ | 1| Blah | | 2| Blah2 | | 3| Blah3 | +--+---------+ Childs +--+------+-------+ |id|parent|feature| +--+------+-------+ | 1| 1 | 123 | | 2| 1 | 35 | | 3| 2 | 15 | +--+------+-------+ I want to select with single query every row fro...

SQL: how to select single record for multiple id's on the basis of max datetime?

I have the following SQL table, Id WindSpeed DateTime -------------------------------------- 1 1.1 2009-09-14 16:11:38.383 1 1.9 2009-09-15 16:11:38.383 1 2.0 2009-09-16 16:11:38.383 1 1.8 2009-09-17 16:11:38.383 1 1.7 2009-09-19 16:11:38.382 ...

PostgreSQL SELECT the last order per customer per date range

In PostgreSQL: I have a Table that has 3 columns: CustomerNum, OrderNum, OrderDate. There may(or may not) be many orders for each customer per date range. What I am needing is the last OrderNum for each Customer that lies in the date range that is supplied. What I have been doing is getting a ResultSet of the customers and querying ...

Aggregate function in comparison of 2 rows in the same table (SQL)

Given the table definition: create table mytable ( id integer, mydate datetime, myvalue integer ) I want to get the following answer by a single SQL query: id date_actual value_actual date_previous value_previous where: date_previous is the maximum of all the dates preceeding date_actual for each id and values corresp...

Finding top n for each unique row

I'm trying to get the top N records for each unique row of data in a table (I'm grouping on columns b,c and d, column a is the unique identifier and column e is the score of which i want the top 1 in this case). a b c d e 2 38 NULL NULL 141 1 38 NULL NULL 10 1 38 1 NULL 10 2 38 1 NULL 1 1 38 1 ...

Mysql GROUP BY and COUNT for multiple WHERE clauses

Simplified Table structure: CREATE TABLE IF NOT EXISTS `hpa` ( `id` bigint(15) NOT NULL auto_increment, `core` varchar(50) NOT NULL, `hostname` varchar(50) NOT NULL, `status` varchar(255) NOT NULL, `entered_date` int(11) NOT NULL, `active_date` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `hostname` (`hostname`), KEY `st...

choosing latest string when aggregating results in mysql

I've been tasked to do generate some reports on our Request Tracker usage. Request Tracker is a ticketing system we use for several departments were I work. To do this I'm taking a nightly snapshot of details about tickets altered for the day into another database. This approach decouples my reporting from the the internal database schem...

Does the following query correct the problem?

hi 1) The following query obtains from each film category the cheapest possible DVD with the highest rating: SELECT FilmName, Rating, DVDPrice, Category FROM Films AS FM1 INNER JOIN Category AS C1 ON C1.CategoryId = FM1.CategoryId WHERE FM1.DVDPrice = (SELECT MIN(DVDPrice) FROM Films AS FM2 WHERE FM2.DVDPrice IS NOT...

Select Top 5 records of every employee in SQL Server

I have the following issue, I have this query that select the latest 5 records created for an employee: SELECT TOP 5 p.value, p.record_date AS FECHA FROM employee_loan_movements p WHERE p.employee_code = '1' AND p.record_date <= '2009-11-11' AND p.movement_type = 1 AND p.value > 0 ORDER BY p.record...

Combining the following mulitple SQL queries into 1 with MS Access & ASP

I want to combine Select top 2 scouting.* From scouting Where scouting.astroLoc Like 'D01%' AND scouting.ownerGuild = 'SWARM' Order By scouting.jumpGate Desc with Select top 2 scouting.* From scouting Where scouting.astroLoc Like 'D02%' scouting.ownerGuild = 'SWARM' Order By scouting.jumpGate Desc with Select top 2 scouting.* Fr...

Complicated MS Access Greatest-N-Per-Group problem

I am looking to combine the following queries into one, where scouting.jumpGate is integer, scouting.astroLoc is a string, scouting.ownerguild is a string and scouting.galaxy is a integer that cross-links to another table (and is my GROUP): Select TOP 3 scouting.jumpGate, scouting.astroLoc, scouting.ownerGuild...

MySQL world database Trying to avoid subquery

I'm using the MySQL WORLD database. For each Continent, I want to return the Name of the country with the largest population. I was able to come up with a query that works. Trying to find another query that uses join only and avoid the subquery. Is there a way to write this query using JOIN? SELECT Continent, Name FROM Country c1 WH...