select the last entry with condition | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

select the last entry with condition

hi all, can someone please explain to me why this isn’t working? I have a store procedure that selects text from the the latest entry… CREATE PROCEDURE dbo.GetLatest
AS
SELECT
[Text]
FROM MyTable
WHERE
[ID] = (select max(ID) from MyTable)
GO The above stored procedure worked has needed, but now I need to add another condition from another column… which has three possible values (NULL, 1, -1) … I want (1). So I tried doing something like this… CREATE PROCEDURE dbo.GetLatest
AS
SELECT
[Text]
FROM MyTable
WHERE
[ID] = (select max(ID) from MyTable)
AND
[OtherColumn] = (1)
GO I have also tried passing the value as a parameter… CREATE PROCEDURE dbo.GetLatest
@OtherColumn int
AS
SELECT
[Text]
FROM MyTable
WHERE
[ID] = (select max(ID) from MyTable)
AND
[OtherColumn] = @OtherColumn
GO What I am trying to do is select text for the latest entry with the column condition. Please help someone. PLEASE. [V] -a8le

Sorry, I posted in the wrong forum section.
Did you get error when running that code?
Post some sample data and the result you want Madhivanan Failing to plan is Planning to fail
I can’t see any errors of your code. —————————————-
http://spaces.msn.com/members/dineshasanka

As your code is written you are saying "I want the last record AND it’s value for OtherColumn has to be = 1. I think what you meant was
AS
SELECT
[Text]
FROM MyTable
WHERE
[ID] = (select max(ID) from MyTable where OtherColumn = 1) So that it will find the rows with OtherColumn = 1 and then return the Max(ID) for it.

Madhivanan, the only error i got was, the usual "object ref, not set to object." which is really no help. druer, you are exactly right, your code worked, beautifully! and after thinking about it, i also got the desired return with: AS
SELECT
[Text]
FROM MyTable
WHERE
[OtherColumn] = 1
ORDER BY ID DESC
GO Do you guys see anything wrong with this? I am considering using this new one because I will be able to "later, if needed" query for the latest X number of entrys.
With your code as it is you will end up getting multiple rows. The max will be the first row returned, but you will end up with second to last, third to last etc if there are multiples that match for OtherColumn. If you changed your code around and did the same concept ID = (select top 1 id from mytable where [othercolumn] = 1 order by id desc) It would use the order by desc but would then only take the top 1 (the MAX 1). However, in running both, the MAX version works faster.
hi druer, while i am at it, may i ask you if there was a way to also limit the query to entries that have "lets say 300 words or less within the [Text] column?" … using SQL that is? -a8le
In order to know the number of words, you would have to first parse out the text to get a word count. If you are just looking for a rough number then there is a very simple way to do that. But it isn’t precise because it is assuming that words are space seperated and have only 1 space between them. Declare @WordCount int
Select @WordCount = DataLength(Text) – DataLength(Replace(Text, ‘ ‘,”)) This basically says take the starting length of my text, and then subtract from it the length of the remaining string after I remove all of the spaces. If the answer is 1000 then you have about 1000 words in the text. If the answer 100, then you only had 100 spaces in the text, which means you had about 100 words in the text. As I say this isn’t precise as after a ‘,’ you might have 3 spaces, or you might have none. It likely excludes the last word in the sentence because there would be no space after etc. If what you are trying to say is I want the Last Text that has a OtherColumn value of 1 and less than 300 words then the following will get it done: SELECT
[Text]
FROM MyTable
WHERE
[ID] = (select max(ID) from MyTable where OtherColumn = 1 AND (DataLength(Text) – DataLength(Replace(Text,’ ‘,”))<300))
hi druer, thanks for the reply, i have setup to where all extra white space is removed upon submission. however, there could be more than one <br>’s . by the way when you say word is that an actual word count or character count. sorry, if i wasn’t clear about that. So if I am trying to get a character count that is less than 775 than I would do this?… CREATE PROCEDURE dbo.GetLatest
@OtherColumn int
@WordCount int
AS
SELECT
[Text]
FROM MyTable
WHERE
[ID] = (select max(ID) from MyTable where OtherColumn = 1 AND (DataLength(Text) – DataLength(Replace(Text,’ ‘,”))<776))
GO is that right? I declared the @wordcount as well. oh, am i passing that in as a parameter? -cheers,
a8le
druer, is there a way to also make sure that the first character in string is alphbetic? -a8le
If you want the Character Count and not the word count then that is very precise, just use the DataLength command and don’t bother with the replace. ISNUMERIC(Left(MyField, 1)) will tell you if the first character is numeric or not.
Thank you druer for all the help. -a8le
]]>