» Get top 1 row of each group
Get top 1 row of each group
|July 27, 2011
Posted by forumadmin
I have a table which I want to get the latest entry for each group. Here’s the table:
|ID| DocumentID | Status | DateCreated |
| 2| 1 | S1 | 7/29/2011 |
| 3| 1 | S2 | 7/30/2011 |
| 6| 1 | S1 | 8/02/2011 |
| 1| 2 | S1 | 7/28/2011 |
| 4| 2 | S2 | 7/30/2011 |
| 5| 2 | S3 | 8/01/2011 |
| 6| 3 | S1 | 8/02/2011 |
The table will be grouped by DocumentID and sorted by DateCreated in descending order. For each DocumentID, I want to get the latest status.
My preferred output:
| DocumentID | Status | DateCreated |
| 1 | S1 | 8/02/2011 |
| 2 | S3 | 8/01/2011 |
| 3 | S1 | 8/02/2011 |
Is there any aggregate function to get only the top from each group? See pseudo-code
select DocumentID, GetOnlyTheTop(Status), GetOnlyTheTop(DateCreated)
group by DocumentID
order by DateCreated desc
If such function doesn’t exist, is there any way I can achieve the output I want?
- Or at the first place, could this be caused by unnormalized database? I’m thinking, since what I’m looking for is just one row, should that
status also be located in the parent table?
Please see the parent table for more information:
| DocumentID | Title | Content | DateCreated |
| 1 | TitleA | ... | ... |
| 2 | TitleB | ... | ... |
| 3 | TitleC | ... | ... |
Should the parent table be like this so that I can easily access it’s status?
| DocumentID | Title | Content | DateCreated | CurrentStatus |
| 1 | TitleA | ... | ... | s1 |
| 2 | TitleB | ... | ... | s3 |
| 3 | TitleC | ... | ... | s1 |
Thanks in advance!
I just learned how to use “apply” which makes it easier to address such problems.
More Related Questions
- Teradata/SQL: Getting other field names when grouping by aggregtate functions like min and max Let's say I have a recordset like this:
user role orderno
--------- ------ -------
john csr 1
ed csr 2
mary csr 3
george mgr 1
john mgr […]
- Misuse of aggregate in SQLite This error occurs in this SQLite query:
select c.id, c.title, sum(some_expression) as ratio
where c.active = 1 or (ratio = 1.0 and c.active = 0
and c.deactivated […]
- Delete duplicate records from a SQL table without a primary key I have the below table with the below records in it
create table employee
insert into employee values(1, 'Jack', […]
- TSQL Finding Order that occurred in 3 consecutive months Please help me to generate the following query. Say I have customer table and order table.
- SQL update query using joins I have to update a field with a value which is returned by a join of 3 tables.
,im.sku as iSku
,gm.SKU as GSKU
,mm.ManufacturerId as ManuId
- How to SELECT * INTO [temp table] FROM [stored procedure] How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]?
Select all data from BusinessLine into tmpBusLine works fine.
- How to group time by hour or by 10 minutes like when I do
GROUP BY [Date]
how can I specify the group period ?
MS SQL 2008
SELECT MIN([Date]) AS […]
- SQL Server – pull X random records per state I have a table with records for each zip code in the united states. For the purposes of displaying on a map, I need to select X random records per state. How would I go about doing […]
- sql query for displaying start/end points with time interval I have a table called vehicle_Summary and the data loks like:
A_Date vehicle_Name Location_Name
2012-02-08 09:36:20.000 AA000AA […]
- How do I create unique constraint that also allows nulls in sql server Adding a column to table, and I want a unique constraint, so the column will insert GUID's, but to allow for existing data before column was added, I need to allow for NULL's in the […]