having date - SQL Server Forums

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)

having date

Author

Topic

thecascades2008
Starting Member

4 Posts

Posted - 2008-09-30 : 23:40:50

Hi all,
I'm having a trouble with having date, suppose that I have a table with some attributes
- Account
- Digital content
- Date_post

So,how can i get only one digital_content latest on per account, i tried use having but not successful.
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 23:50:42

[code]SELECT t.*
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY Account,[Digital content] ORDER BY Date_Post DESC) AS Seq,*
FROM YourTable)t
WHERE t.Seq=1[/code]

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-10-01 : 00:02:34

as he wants only one digital_content latest on per account


SELECT t.*
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY [Account], [Digital content] ORDER BY Date_Post DESC) AS Seq,*
FROM YourTable)t
WHERE t.Seq=1

thecascades2008
Starting Member

4 Posts

Posted - 2008-10-01 : 00:07:13

Thanks for replys but i have a error "The OVER SQL construct or statement is not supported."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 00:11:10

quote:
Originally posted by thecascades2008

Thanks for reply but i have a error "The OVER SQL construct or statement is not supported."

Are you using SQL 2005?
Is the compatibility level of your database 90?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 00:13:16

anyways you can try this also

SELECT t.*
FROM YourTable t
JOIN
(SELECT Account,[Digital content],MAX(Date_Post) AS MaxDate
 FROM YourTable
 GROUP BY Account,[Digital content]) tmp
ON tmp.MaxDate=t.Date_Post
AND tmp.[Digital content]=t.[Digital content]
AND tmp.Account=t.Account

thecascades2008
Starting Member

4 Posts

Posted - 2008-10-01 : 00:35:23

quote:

Originally posted by visakh16

anyways you can try this also

SELECT t.*
FROM YourTable t
JOIN
(SELECT Account,[Digital content],MAX(Date_Post) AS MaxDate
 FROM YourTable
 GROUP BY Account,[Digital content]) tmp
ON tmp.MaxDate=t.Date_Post
AND tmp.[Digital content]=t.[Digital content]
AND tmp.Account=t.Account

Thanks visakh16, but your reply is not realizable,

,i'm using SQL 2k5

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 00:42:31

quote:
Originally posted by thecascades2008

quote:
Originally posted by visakh16

anyways you can try this also

SELECT t.*
FROM YourTable t
JOIN
(SELECT Account,[Digital content],MAX(Date_Post) AS MaxDate
FROM YourTable
GROUP BY Account,[Digital content]) tmp
ON tmp.MaxDate=t.Date_Post
AND tmp.[Digital content]=t.[Digital content]
AND tmp.Account=t.Account

Thanks visakh16, but your reply is not realizable, ,i'm using SQL 2k5

sorry didnt get that. what do you mean by "not realizable"?

thecascades2008
Starting Member

4 Posts

Posted - 2008-10-01 : 01:46:32

sorry that just is unachievable

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 02:02:45

quote:
Originally posted by thecascades2008

sorry that just is unachievable

why? wahts the error?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 02:16:11

then is this what you want:-

declare @Acct table
(
AcctNo int,
Digital_content varchar(100),
Date_post datetime
)


insert into @Acct
select 101,'test1','1 Jan 2002' union all
select 102,'test2','16 Apr 2005' union all
select 103,'test3','21 Oct 2002' union all
select 105,'test4','11 Jan 2006' union all
select 101,'test5','1 Nov 2006' union all
select 103,'test6','15 Aug 2008' union all
select 103,'test7','19 Jan 2007' 
--solution1
SELECT t.AcctNo,t.Digital_content,t.Date_post
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY AcctNo ORDER BY Date_Post DESC) AS Seq,*
FROM @Acct)t
WHERE t.Seq=1

--solution2
SELECT t.*
FROM @Acct t
JOIN
(SELECT AcctNo,MAX(Date_Post) AS MaxDate
 FROM @Acct
 GROUP BY AcctNo) tmp
ON tmp.MaxDate=t.Date_Post
AND tmp.AcctNo=t.AcctNo
ORDER BY t.AcctNo
output
---------------------------------
solution 1
AcctNo	Digital_content	Date_post
---------------------------------------
101	test5	2006-11-01 00:00:00.000
102	test2	2005-04-16 00:00:00.000
103	test6	2008-08-15 00:00:00.000
105	test4	2006-01-11 00:00:00.000
solution 2
AcctNo	Digital_content	Date_post
------------------------------------------
101	test5	2006-11-01 00:00:00.000
102	test2	2005-04-16 00:00:00.000
103	test6	2008-08-15 00:00:00.000
105	test4	2006-01-11 00:00:00.000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources