Hi all. I'm looking for assistance to get the greatest value from 3 or
more different columns. I'm assuming that the best way is to put the
columns into a temp table and use 'max' function to return the greatest
value but don't know how to code it. Thanks in advance.
*** Sent via Developersdex http://www.developersdex.com ***
Hi all. I'm looking for assistance to get the greatest value from 3 or more different columns. I'm assuming that the best way is to put the columns into a temp table and use 'max' function to return the greatest value but don't know how to code it. Thanks in advance.
2. Also how do you write and call a function in SQLServer (syntax ??), eg.num_Business_Days is a function returning number of business days between the 2 dates....
Here is my SQL statementSELECT a.name, b.meterno, c.meterstatus, d.accountno, d.creation_dateFROM fmdata.location a, cisdata.service_meters b, cisdata.meter_master c,cisdata.Account_Master dWHERE a.location_id = b.location_id AND b.meterno IS NOT NULL AND b.meterno= c.meterno AND a.location_id = d.location_idORDER by meterstatus, nameHere is my resultsNAME METERNO METERSTATUS ACCOUNTNO CREATION_DATE01010004 20512944 Active 101000402 7-Feb-200201010004 20512944 Active 101000401 8-May-199701010005 25917180 Active 101000501 27-May-200301011001 13646231 Active 101100102 17-Mar-199901011002 18389246 Active 101100201 29-Apr-199401011003 84473845 Active 101100301 24-Apr-199701012002 47511850 Active 101200202 26-Jan-199601013001 35653963 Active 101300101 28-Feb-1979If you notice I'm getting two 01010004 under the NAME column. I would likethe SQL statement to select the greatest CREATION_DATE if there areduplicate NAME'S. So in this case, it would select the top 01010004 since ithas the greatest CREATION_DATE.thanks in advancebart
Hi All, I need to find the highest of two numbers and lowest of the two numbers. I read on the web that Greatest and least functions in sql can help me do that. but when i use greatest in my query it gives me this error
GREATEST is not a recognized built-in function name. all I have is
declare @first int, @second int set @first='12' set @second='14' select GREATEST(@first,@second)
Can somebody point me in the right direction. Thanks so much
Hello, Here's one way to sum only the top 5 (greatest 5) values per group. I assume there is a table called IdValues that contains two columns: id int, value int.
declare @lastId int declare @value int declare @count int declare @idList varchar(5000) declare @valuelist varchar(5000) set @count=0 set @lastId = -1 set @value = 0 set @idList='' set @valuelist=''
select @count=(case when @lastId<>id then 1 else @count+1 end), @value=(case when @lastId<>id then value when @count<=5 then @value+value else @value end), @idList=(case when @lastId<>id then cast(id as varchar)+','+@idList else @idList end), @valuelist=(case when @lastId<>id then cast(@value as varchar)+','+@valuelist else cast(@value as varchar)+','+right(@valuelist,len(@valuelist)-charindex(',',@valuelist)) end), @lastId=id from IdValues order by id desc, value desc
select @idList,@valuelist
It's a funny approach. I'd be interested to see a better method. In MySQL it is possible to do this much better and have it produce an actual resultset (since MySQL allows you to assign variables and product a resultset in the same query).
I also noticed something interesting. If you do any operation on the order-by columns, the query doesn't work. For example, if I do: order by id+0 desc, value desc something funny happens and you only get one id and one value in the list variables. Maybe someone else who actually some idea of how SQL Server works can explain this.
I have table1 and table2.In table1 I have a column of numbers, numbers1.In table2 I have a column of numbers, numbers2.I'd like to select the highest number represented in either column.Example:table1:column1--------------345565643656555676table2:column2--------------3456564556456456456456The number I would want would be 56456 since it's the largest numberout of all combined.How can I get that number with one select statement?--[ Sugapablo ][ http://www.sugapablo.com <--music ][ http://www.sugapablo.net <--personal ][ Join Bytes! <--jabber IM ]
What happens when autoincrement reaches its last value? Does my application crash? Will autoincrement wrap?
Let us assume I figure that my database table will be much smaller than 2^16 records. Since it will be nowhere near the limit, I select smallint (2 bytes wide) as my ID field. I set the seed value to -32,767. I set the increment value to 1. Over the course of actual useage, many records are added and deleted many times. Although I correctly anticipated that the number of records in my database is far less than 65,535, autoincrement has reached 65,535. What will happen next? Ideally I would like autoincrement to wrap and take the next unused value. But does it do that? Or does it freeze at the highest value? What happens?