simple MS SQL queries
 

hey gang...need a little help.

I'm trying to do a limit query. I'm familiar with mySQL but apparently MS SQL is a little different.

SELECT TOP 10
id,name,comments
FROM users
WHERE comments != 'NULL'

I know this doesn't work. How do I get this to work? Its the "not equal to blank" im trying to figure out. I want to query out the top 10 records where comments is not blank. Eventually, i want to make a "next 10 records" and "previous 10 records".

edit: im also trying this but its not working

select * from (
select top 10 id,name,comments (
select top 30 id,name,comments
from users
order by comments asc
) as newtbl order by comments desc
) as newtbl2 order by comments asc

msSQL sucks my ass.

Try :

comments is not null and comments <> ''

SELECT TOP 10
currentmodel,comments
FROM items
WHERE comments is not null and comments <> ''

I get this error: [Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

It's always best to describe the table schema(s) for the table(s) involved. In this case, it looks like "items" is declared as a text column, so normal string operations won't work.

There are a few ways to get around this. You can:
1. cast comments to varchar and then compare
2. use the datalength() function, which works on text

Also, because NULL values fail any operator, if you use one of the above conditions, then it should not be necessary to check for IS NOT NULL.

And finally, I find it's always helpful to fully qualify column names, especially when joining between tables. In this case, you are querying from only one table, so it's not that important.