using Max() on an awkward statement

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 2000 Forums

SQL Server Development (2000)

using Max() on an awkward statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-15 : 08:27:46

Chad writes "This is my procedure..

CREATE PROCEDURE searchDB(
@SearchString varchar(1000) = NULL)
AS
BEGIN

SELECT Rank, Item_Title, Item_Src, Item_Blurb, Item_Date_Written, Item_Section
FROM FREETEXTTABLE(Contents, *, @SearchString) AS ft
JOIN Contents AS c ON ft.[KEY] = c.Item_ID
ORDER BY Rank Desc

RETURN @@ROWCOUNT
END

How can I retrieve the MAX() of Rank?"

Nazim
A custom title

1408 Posts

Posted - 2002-07-15 : 08:36:27

Two ways

declare @vmax numeric(3)
SELECT @vmax=max(Rank)
FROM FREETEXTTABLE(Contents, *, @SearchString) AS ft
JOIN Contents AS c ON ft.[KEY] = c.Item_ID

or

select @vmax=max(rank)
from (
SELECT Rank, Item_Title, Item_Src, Item_Blurb, Item_Date_Written, Item_Section
FROM FREETEXTTABLE(Contents, *, @SearchString) AS ft
JOIN Contents AS c ON ft.[KEY] = c.Item_ID ) a

HTH

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

G1BLETZ
Starting Member

1 Post

Posted - 2002-07-15 : 09:50:51

Thankyou for your reply however (and I should of specified this) I would like to retrieve the max(rank) AND the resultset (rank, item_title, Item_src etc.) I intend this to be passed out of the procedure as an OUT parameter but would also like to pass out the resultset as it stands, the obvious way to do this would seem to be to duplicate the procedure, have 1 to pass out the rank and 1 to return the dataset, however I would like to avoid duplication, any ideas?

Chad.

Edited by - G1BLETZ on 07/15/2002 09:55:41

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources