the sql max function

[a8le]

hi all, can someone please explain to me why this isn't working?

I have a store procedure that select a 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

And 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

AHHHHHH, AHHHHHH

-a8le

 

[ManicCW]

use:
select top 1 id from mytable where othercolumn=@othercolumn order by id desc

 

[a8le]

Thank you for the reply, i finally got it to work using...

AS
SELECT
[Text]
FROM MyTable
WHERE

[ID] = (select max(ID) from MyTable where [OtherColumn] = 1)
GO

I like the Top 1 syntax though, I'll give that a try someday.

-a8le

 

[ManicCW]

ok, but you are complicating your sql string using max ...