max formula

[aspnetuser]

I have the following set of data
id num
1 0
2 3
3 4
4 5

How can I determine the max id (4) then show in expression field (5) repeating for every record.

Like below:

id num expression
1 0 5
2 3 5
3 4 5
4 5 5

sql server 2000

 

[donutman]

[Blue]SELECT[/Blue] id[Gray],[/Gray] num[Gray],[/Gray] [Gray]([/Gray][Blue]SELECT[/Blue] [Blue]TOP[/Blue] 1 num 
                    [Blue]FROM[/Blue] YourTable 
                    [Blue]ORDER[/Blue] [Blue]BY[/Blue] id [Blue]DESC[/Blue][Gray])[/Gray]
   [Blue]FROM[/Blue] YourTable

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLBill]

As for the new column,

ALTER TABLE mytable
ADD COLUMN expression INT
GO
UPDATE TABLE mytable
SET expression = 5
GO

Refer to the BOL for more information on ALTER TABLE, ADD COLUMN (under ALTER TABLE) and UPDATE TABLE.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875