Is There A Keyword Or Function Opposite Of TOP? How Do I Get Various LOWEST/BOTTOM Values?
Jan 26, 2008
Hello Group,
I'm still a bit new to T-SQL and am wondering how to get various LOWEST values from a table, rather than TOP values. There does not appear to be a keyword opposite of TOP in T-SQL.
My real problem is this: I know how to get the 2nd highest value, third highest value, etc...but how does one get the 2nd lowest value, third lowest value, etc?
I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.
WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )
I kept on getting errors, like
Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'. which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.
Is there an opposite of the TOP keyword in a select statement?
I have the following query which works fine but it's not in the order I need because I have to use DESC to get the last 40 records.
select top 40 slabnumber, scarfcode, left(productname,6) as heatid, strandid, heatcutnumber, creationtime from product where scarfcode = 'V3' order by creationtime desc
I need the returned records ordered by creationtime but not descending.
I just started with SQL Server Express and was playing around with the Management Studio.
I created a query to make a column unique:
ATLTER TABLE Table_Name ADD UNIQUE (Column_Name)
That worked perfect, if I fill in the same data in the next row of that column, I get an error as expected. But, how do I remove the UNIQUE property on that column? I can find it in the column properties within Management studio, neither I can't find the command to undo this action.
Koroner writes "Hi all. I'm very new to SQL programming so when I started coding I already expected some problem like the one I now describe to present itself soon. I'm not an English native speaker, pardon me for making any mistake.
I'm currently programming in ASP.NET using Visual Studio.
I've defined a function which consists of one quite simple query calling itself in a recursive way. The problem occurrs when running a view to check whether the function works prorperly, since the "nesting limit exceeded" error (not exact words) pops up. I didn't know there was such a limit.
Searching the Web I've learnt there is a way to modify this limit, that is using the OPTION clause and the MAXRECURSION hint.
But the fact is whenever I add the OPTION clause to the SELECT of my function Visual Studio tells me that there is a syntax error near "OPTION" keyword. I'm sure VS is not recognizing the "MAXRECURSION" keyword, because there are no syntax errors and the word is not colored in blue (unlike other keywords).
What puzzles me most is that I tried to define a recursive SELECT provided with OPTION clause within a view or an on-the-fly query and all works fine there.
Within the LinkingID, there are duplicates in ID1 and ID2 but just in opposite columns. I have been trying to figure out a way to remove these set based. It doesn't matter which duplicate is removed. Essentially these are just endpoints and I don't care which side they are on. The solution must recognize the duplicates and not just remove based on every 2nd row.
I have a table like thisorder_id price1 -10002 20003 30004 4000I want to know the total of the order amount, I use this querySELECT SUM(price)FROM MyTableand I get 8000 instead of 9000. The negative value seems to be read twice!Do you have an idea?Thanks a lot
I have this assignment where i have a table full of two digit exam scores and I have to write a function that eliminate x number of top values and x number of bottom values and return all the middle values. When the function is called, obviously a number is entered such as 3 and the top 3 and bottom 3 scores are not returned. i.e. SELECT * FROM GetMiddleValues (3);
If anyone has any ideas on how to accomplish this, that would be great.
I have created a function to return values, which works fine, but I can't do calculations in it.
CREATE FUNCTION [dbo].[tf_Asset_Portfolio](@deal_id int, @as_of_date datetime) RETURNS TABLE AS RETURN ( SELECT DISTINCT dbo.Assets.issue_id, SUM(DISTINCT dbo.Assets.par_amount) AS par_amount, SUM(DISTINCT dbo.Assets.par_amount) AS market_value FROM dbo.Issue INNER JOIN dbo.Assets ON dbo.Issue.issue_id = dbo.Assets.issue_id INNER JOIN dbo.Issuer_Rating_History ON dbo.Issue.issuer_id = dbo.Issuer_Rating_History.issuer_id WHERE (dbo.Issuer_Rating_History.as_of_date <= @as_of_date) GROUP BY ALL dbo.Assets.issue_id, dbo.Assets.deal_id, dbo.Issue.default_date HAVING (dbo.Assets.deal_id = @deal_id) )
I need to do calculations on market value based on the default date. If default date isn't specified then it should be 100% of par amount. If default date is less than one year ago - 65% of the par_amount. If default date is one or more years ago - 0. I have no idea about how to do this and everything I try wont work. I created another function to do the calculations and this seems to work, but it only does one record instead of all of them.
CREATE FUNCTION dbo.tf_Asset_Portfolio2 (@deal_id int, @as_of_date datetime) RETURNS @Market TABLE (issue_id int, par_amount money, market_value money) AS BEGIN DECLARE @ReturnDate datetime DECLARE @DD datetime DECLARE @PA money DECLARE @MV money DECLARE @ID int DECLARE @DateD int
SELECT TOP 1 @ReturnDate = LAST_BATCH FROM master..sysprocesses WHERE SPId = @@SPID
SELECT @ID = issue_id FROM Assets WHERE Assets.deal_id = @deal_id SELECT @PA = SUM(DISTINCT par_amount) FROM Assets WHERE Assets.issue_id = @ID AND Assets.deal_id = @deal_id SELECT @DD = default_date FROM Issue WHERE Issue.issue_id = @ID
SET @DateD = DateDiff("yyyy", @DD, @ReturnDate)
If @DD = Null BEGIN SET @MV = @PA END Else If @DD > @ReturnDate BEGIN SET @MV = @PA END Else If @DateD < 1 BEGIN SET @MV = @PA * .65 END Else If @DateD >= 1 BEGIN SET @MV = 0 END
insert into @Market (issue_id, par_amount, market_value) values (@ID,@PA,@MV)
RETURN END
I need to combine the functionality of being able to return mutliple records that isn't in the 2nd function and being able to calculate the market value which isn't in the first one. Please help. Thank you in advance.
OK, this should be an easy one but my brain isn't quite working right now.
I have a table, we'll call Table1 like so:
ProductID Supplier Cost 12345 A 14.50 12345 B 13.49 12345 C 12.00 43222 A 15.00 43222 B 15.21 43222 C 13.99 12312 B 14.00 15421 A 21.99 15421 C 20.00
And I want to Get the name of the Supplier with the Lowest cost, I know I can go like:
SELECT ProductID, MIN(Cost) FROM Table1 GROUP BY ProductID
and get the lowest cost, but what would be the most effiecent way to get all three fields returned by the query? I need the ProductID, Supplier and Cost.
@AppId AS Application_Id , MB.POLICY_ID AS Policy_Id , MH.BFN_CHG_DATEAS Date_Of_Disinvestment , AD.X_INVEST_AMT AS Original_Investment_Value , MB.TOTAL_AMOUNT AS Encashment_Value , WH.TOT_COMMISSION AS Top-Up_Commission
FROM: I select from lot of table, as specified from my select statement
My Aliases returns just one values, of which that's what I am expecting, so I need to make a comparison between Original_Investment_Value and (Encashment_Value + Top-Up_Commision), and return the lowest value
Is there a more elegant way to do the following?...declare @d1 datetime, @d2 datetimeset @d1 = '2005-01-01'set @d2 = '2005-02-01'selectcasewhen datediff(dd,@d2,@d1)is nullthen coalesce(@d1, @d2)when (datediff(dd,@d2,@d1)> 0)then @d2else @d1endCheers,..N
Hi, this seems to be an easy question but I can't find a quick answer to it. I have an asp page that communicates with a database and I want the following example to work...
Insert 5 records into the DB with primary keys 1-5. Remove record with key number 2.
Now, if I insert a new record I want it to take the smallest available key number (in this case number 2). This will save space in the database and keep the key numbers from increasing forever.
Does auto increment work like this? If not, how do I do it?
Hi, We Have Been Trying To Convert Some Pf The Procs Into Functions Of Late,but There Is A Problem :-we Have Been Unable To Return More Than 1 Table Value From A Function.
Create Function F_clusters() Returns @ki Table(names Nvarchar(200),total Int), As Begin Insert @ki Select Names,count(distinct Chremail) As From Customer Where Chremail Is Not Null Return End
This Works Fine :- And Gives The Reqd. Results.
But,
If I Am Using The Same Function To Return Two Tables Then It Doesn't Work,could You Pls Chk.
Create Function F_clusters() Returns @ki Table(names Nvarchar(200),total Int),@k2 Table(names Nvarchar(200),total Int) As Begin Declare @cnt Int Set @cnt = 1 While @cnt <= 2 If @cnt =1 Begin Insert @ki Select Names,count(distinct Chremail) As From Customer Where Chremail Is Not Null Set @cnt = @cnt + 1 End If @cnt =2 Begin Insert @k2 Select @naamre,count(distinct(a.intcustomerid)) As Pura_ginti From Trcustomerpreference03july A Inner Join Cleancustomer B On A.intcustomerid = B.intcustomerid Where Chremail <> ' ' And Chremail Is Not Null And Intpreferenceid In (6,7,2,3,12,10) Set @cnt2 = @cnt2 + 1 End End Return End
Can We Return Two Tables Or Is It Not Possible ? Pls Chk Into This And Tell Me.
I have an existing function and need to alter function to give result of the values multipiled until its parent is reached.need two seperate functions for city and amt columns..need to also display the parent-description
select [date], [close], AVG([close]) over (order by [date] rows between 2 preceding and current row ) as [ma] from dax2
My Problem is that the first 2 rows in column [ma] are not correct. They Show a value since it is not a 3 days average. In the first row in column [ma]is the same value as in [Close]. In the second row in column [ma] is the average value of the first and second value of column [Close].
How can i achieve that this "erroneous" values are not inserted or rather are shown as null.
I'm trying to do a Clr function wiht null values but I have an error. My Clr is like this:
using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Collections; using System.Text;
[code]...
A .NET Framework error occurred during execution of user-defined routine or aggregate "function":
System.InvalidCastException: La conversión especificada no es válida. System.InvalidCastException:   en Microsoft.SqlServer.Server.ValueUtilsSmi.GetSqlInt16(SmiEventSink_Default sink, ITypedGettersV3 getters, Int32 ordinal, SmiMetaData metaData)   en UserDefinedFunctions.function()
I have a sales report that is pulling up data and displaying the detailed lines and the aggregate/summary lines grouped by a single field.
THe report say has 10 summary/aggregate lines and each summary lines have a maybe 20 more lines to it.
I want to NUMBER the SUMMARY lines only. In a new column on the summary line cell, if I type, =RowNumber(Nothing), I get a count of all its sub-lines displayed. How can I limit the scope of numbering to just the summary lines and make sure it does not include the sub lines involved ?
I have a CLR function that throws an error if one of the parameters is NULL. Am I using the IsNullable tag correctly or am I supposed to do this another way? The function simply formats decimal values using .NET culture information and returns a string. Thanks very much for any help. -- Erik
Our company is a proffesional deticated bullet proof server and general server provider in china. If you're looking for a reliable and long term cooperation bp server provider, it deservers you to read our company file to know more about us.
I'm using sql express 2005. I need help with buliding a view.
I'm trying to join two tables and then, if duplicate ID's exist, retrieve the row with the lowest value in a specific column.
For instance:
TableHousehold:
ID Name Status
001 The Smith€™s A
002 The Jone€™s A
003 The Adam€™s A
004 The Grant€™s A
TableResidents
Pk HouseholdID ResidentID Name Gender
1 001 1 Mary Smith F
2 001 2 Jason Smith M
3 001 3 Rachel Smith F
4 002 1 Mike Jones M
5 002 2 Sara Jones F
6 003 2 Erik Adams M
The resident ID is auto increment and assigned by the server. It is possible for resident 1 for each household to have been deleted. It is also possible that a household does not contain any residents. Therefore, I wish to join these tables and return this:
ID HouseHoldName Status ResidentName Gender
001 The Smith€™s A Mary Smith F
002 The Jone€™s A Mike Jones M
003 The Adam€™s A Erik Adams M
004 The Grant€™s A null null
There are other columns in both tables that I will want to join, but for simplicity, the above example illustrates my need.
I was able to accomplish this by creating two views. But I€™d like to try and do this in one.
I have created the below function and apply it on a column in a table to replace the below identified values with Blank. It works fine but i have so many different varieties of values i need to add to ths list. Is there any way i insert these values in a table and call the values from that table instead of writing separate SET Statements.
CREATE FUNCTION [dbo].[sv_ReplaceChar] (@badString varchar(8000)) RETURNS VARCHAR(8000) AS BEGIN
I have a long stored procedure that performs some inserts and updates on the table that users are accessing through a user interface. We this stored procedure runs users experience slowness, is there any way I can get the stored procedure runs with a lowest priority so that user will be able to perform their tasks first.
I have a little problem that I just haven't been able to solve. I don't think it is very difficult but I can't seem to make it work. Here's the scenario:
I have a database with the following values: Model Make Price DA1100 GTN$88.00 DA1100 GTN $100.00 DA1000 GBN$110.00 DA668 GTN$100.00 DA880 GTN$200.00
In this case DA1100 is listed twice with 2 different prices. I only want to display the one with the lowest price. So the result I want is: Model Make Price DA1100 GTN$88.00 DA1000 GBN$110.00 DA668 GTN$100.00 DA880 GTN$200.00
