Error - 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

General SQL Server Forums

New to SQL Server Programming

Error

Author

Topic

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2008-02-01 : 02:40:37

Dear all,

create table news (id int identity,message text,date1 datetime)
insert news values('First news',getdate())
insert news values('second news','2008-01-10 13:07:12.217')
insert news values('Third news','2008-01-11 13:07:12.217')
insert news values('Fourth news','2008-01-13 13:07:12.217')
insert news values('Fifth news','2008-01-13 13:07:12.200')
insert news values('Sixth news','2008-01-14 13:07:12.203')
insert news values('Seveth news','2008-01-14 13:07:12.221')
insert news values('Eigth news','2008-01-14 13:07:12.230')

select * from news

I try to execute a querey ,give me an error message like
The text, ntext, and image data types are invalid in this subquery or aggregate expression.

select message from news where message not in
(select top 3 message from news order by date1 desc)

How can i select messages except top 3 messages from my table

Thanks in advance!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-01 : 03:29:08

[code]SELECT *
FROM news
WHERE id NOT IN (SELECT TOP 3 id FROM news ORDER BY date1 DESC)[/code]

KH
[spoiler]Time is always against us[/spoiler]

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2008-02-01 : 03:38:56

quote:
Originally posted by khtan

SELECT *
FROM news
WHERE id NOT IN (SELECT TOP 3 id FROM news ORDER BY date1 DESC)

KH
[spoiler]Time is always against us[/spoiler]

Hi Khtan!
Thanks for your reply.I want to correct my querey.How can i do that?
why don't i do it throgh message field?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-01 : 03:43:23

your message column is a text and you may have different row with same value in the message column. You may be excluding more rows that necessary. ID is a unique field. Using column with unique value will ensure you will be excluding the exact number of rows that you need. Unless that is what you want.

KH
[spoiler]Time is always against us[/spoiler]

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2008-02-01 : 03:48:07

quote:
Originally posted by khtan

your message column is a text and you may have different row with same value in the message column. You may be excluding more rows that necessary. ID is a unique field. Using column with unique value will ensure you will be excluding the exact number of rows that you need. Unless that is what you want.

KH
[spoiler]Time is always against us[/spoiler]

Thanks for your proper guidance....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources