how to group and then get top 1 within each group

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to group and then get top 1 within each group

Author

Topic

tuka
Starting Member

26 Posts

Posted - 2008-06-10 : 18:57:20

Hi all,

I have a table with the following fields:

id int
id_co int
created_on datetime

with sample data in columns so:
1 65 2008-04-24 19:55:56.083
2 65 2008-04-24 19:55:57.083
3 64 2008-04-24 19:58:58.083
4 64 2008-04-24 19:58:59.083

I would like to get a query that gets me the most recent row when grouped by id_co (the 2nd row) so that I get

row 2
row 4

as results - I am able to do ordering or grouping but not sure how to do that for subsets.

Any ideas.
TIA
tuka

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-10 : 19:02:26

SELECT id_co, MAX(created_on) AS created_on
FROM YourTable
GROUP BY id_co

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 00:10:07

or use the ROW_NUMBER() function:-

SELECT t.id,t.id_co,t.created_on
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY id_co ORDER BY created_on DESC) AS RowNo,
*
FROM YourTable
)t
WHERE t.RowNo = 1

kartik.kaveeshwar
Starting Member

18 Posts

Posted - 2008-06-11 : 01:40:50

try this

SELECT ID,ID_CO,CREATED_ON FROM TABLE1 WHERE CREATED_ON IN(SELECT
MAX(CREATED_ON) FROM TABLE1 GROUP BY ID_CO)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-11 : 11:16:23

quote:
Originally posted by kartik.kaveeshwar

try this

SELECT ID,ID_CO,CREATED_ON FROM TABLE1 WHERE CREATED_ON IN(SELECT
MAX(CREATED_ON) FROM TABLE1 GROUP BY ID_CO)

This query could produce inaccurate results.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tuka
Starting Member

26 Posts

Posted - 2008-06-12 : 17:44:49

Thanks guys,

The replies were very useful.

Karteek scripts seem ok for me. What I would like to know from tkizer is how could it produce innacurate results ? Whar are some edga cases you can think of ?

TIA,
Tuka

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-12 : 18:10:29

I don't have time to illustrate the case, but I'll describe it in words. If you've got multiple id_co values that have the same created_on value, kartik's query could return multiple rows for each id_co which is not wanted according to your first post.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tuka
Starting Member

26 Posts

Posted - 2008-06-14 : 07:18:57

Hi,

I can see why this would be a concern based on my example. But in the real case scenario, there would be no danger since I use a timestamp for my created_on field which would produce unique field records. I think the risk is acceptable.

Thanks again.
tuka

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-14 : 11:40:11

quote:
Originally posted by tuka

Hi,

I can see why this would be a concern based on my example. But in the real case scenario, there would be no danger since I use a timestamp for my created_on field which would produce unique field records. I think the risk is acceptable.

Thanks again.
tuka

But to be on safer side use either Tara's or Visakh's query

Madhivanan

Failing to plan is Planning to fail

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-16 : 12:47:13

quote:
Originally posted by tuka

Hi,

I can see why this would be a concern based on my example. But in the real case scenario, there would be no danger since I use a timestamp for my created_on field which would produce unique field records. I think the risk is acceptable.

Thanks again.
tuka

Why would you even consider using a query that has these flaws when there are accurate alternative solutions provided already?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources