Problem with selecting distinct records.

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)

Problem with selecting distinct records.

Author

Topic

Wodzu
Yak Posting Veteran

58 Posts

Posted - 2010-07-16 : 05:39:23

Hi guys.

I am having problems with constructing a query over this set of data:


RecordID ObjectID DateCreation Param1 Param2
    1       1      2009-10-10     1     1 
    2       1      2009-11-10     2     3 
    3       1      2009-09-09     1     1 
    4       2      2009-12-05     1     1 
    5       2      2009-12-06     1     1

I would like to select distinct ObjectID with the highest DateCreation but I would also like to have info about Param1 and Param2.
So the outcome result of such query would be like this:


RecordID ObjectID DateCreation Param1 Param2
    2       1      2009-11-10     2     3 
    5       2      2009-12-06     1     1

RecordID coulmn is not necessery but it would be nice to have.
Is this possible to achieve without cursor?

Kristen
Test

22859 Posts

Posted - 2010-07-16 : 05:52:08

[code]

DECLARE @MyData TABLE
(
RecordID int,
ObjectID int,
DateCreation datetime,
Param1 int,
Param2 int
)

INSERT INTO @MyData
SELECT 1, 1, '20091010', 1, 1 UNION ALL
SELECT 2, 1, '20091110', 2, 3 UNION ALL
SELECT 3, 1, '20090909', 1, 1 UNION ALL
SELECT 4, 2, '20091205', 1, 1 UNION ALL
SELECT 5, 2, '20091206', 1, 1

SELECT *
FROM
(
SELECT [T_RowNumber] = ROW_NUMBER()
OVER
(
PARTITION BY ObjectID
ORDER BY ObjectID, DateCreation DESC
),
*
FROM @MyData
) AS T
WHERE T_RowNumber = 1
ORDER BY ObjectID
[/code]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-16 : 05:53:59

select * from
(
select
row_number() over (partition by ObjectID order by DateCreation desc) as rownum,
*
from your_table
) as dt
where dt.rownum=1

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-16 : 05:54:30

No, you're never too old to Yak'n'Roll if you're too young to die.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-07-16 : 05:54:49

Alternatively (although I like Kristens answer better)..
[code
DECLARE @tab TABLE
(
RecordID INT
, ObjectID INT
, DateCreation DATETIME
, Param1 INT
, Param2 INT
)

INSERT INTO @tab
select 1,1,'2009-10-10',1,1
union select 2,1,'2009-11-10',2,3
union select 3,1,'2009-09-09',1,1
union select 4,2,'2009-12-05',1,1
union select 5,2,'2009-12-06',1,1

SELECT
tab.RecordID
, MaxTab.ObjectID
, MaxTab.DateCreation
, tab.Param1
, tab.Param2
FROM
@tab tab
INNER JOIN
(
SELECT
ObjectID
, MAX(DateCreation) AS DateCreation
FROM
@Tab
GROUP BY
ObjectID
) MaxTab
ON
MaxTab.ObjectID = tab.ObjectID
AND
MaxTab.DateCreation = tab.DateCreation
ORDER BY
tab.ObjectID
[/code]

Wodzu
Yak Posting Veteran

58 Posts

Posted - 2010-07-16 : 05:56:10

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources