|
Something like this might work:
SELECT TOP 1 EmployeeID, Salary
FROM
(
SELECT TOP n EmployeeID, Salary
FROM EmployeeTable
ORDER BY Salary DESC
) AS TopSalaries
ORDER BY Salary ASCRemember to replace the n in TOP n with the actual literal value that you need (unless you are using SQL Server 2005)
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
If you don't mind temp tables this should work
######
DECLARE @TopSalary int
SET @TopSalary = 10 -- Get me the 10th hightest paid employee
create table #temp(id int Indentity(1,1) employeeID int, salary float)
insert #temp
insert employee, salary from YourTable
ORDER BY SALARY DESC
SELECT * from temp
WHERE ID = 10
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
|
That will get just the lowest Salary because the ORDER BY clause goes in ASCending order by default (i.e. Starting with the lowest)
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Select *,sal From Emp X Where n =
(
Select Count(Distinct Sal) From Emp Where sal >=X.sal
)
(Got it from the net somewhere )
By the way Colin, how do you post such nicely formatted code? Do you use any tool for that?
-
-- modified at 23:52 Tuesday 16th May, 2006
|
|
|
|
|
Dinuj Nath wrote: how do you post such nicely formatted code? Do you use any tool for that?
For the code to show up in the orange box, you use the <pre> tag and for the text in red like the TOP n that Colin uses, use the <code> tag that is in the Formatting selection of the Reply to a Message form.
I hope this helps,
Paul
|
|
|
|
|
|
hello,
I am using vb.net and I am a new comer to ADO.net! I have a relational database and I am confusing in it to connect it to vb.net. All I have is 4 tables. I want to make databinding in textboxes within the program. Can someone guide me somewhere pls  I am using MS Access 2003 not SQL. But i am trying to use SELECT statements and etc.
Thank you
Adrian De Battista: .Net Programmer, Java Programmer and Web Designer.
-- modified at 6:25 Monday 15th May, 2006
|
|
|
|
|
well i see that no body answear i don't know i think it is easy
this is the way
well i don't know if you know something abt dataset have you heard abt that before ?
this dataset is a tomporary representation of a database in the memory
this dataset is used in the deconnected mode
i hope you know all that sh*t if you don't i can send you a good course of vb.net but in french else you can ask this in microsoft.com in MSDN & i'm sure you will get what you need
if you don't find neither you can just ask me i will help as much as i can i'm a beginner too 
try to be good if you can't be the best
|
|
|
|
|
|
Hi
i dont know when use a static method and when a non-static method in the data access layer
suppose we have
#1
public class Customer
{
.
.
.
publuc static DataSet GetCustomerList( int shopID )
{
//Do something and return a dataset
}
.
.
.
}
#2
public class Customer
{
.
.
.
publuc DataSet GetCustomerList( int shopID )
{
//Do something and return a dataset
}
.
.
.
}
#1 or #2 This is the problem
|
|
|
|
|
WDI wrote: i dont know when use a static method and when a non-static method in the data access layer
You didn't say what you want to do with the data access layer.
If you use static methods then everything in the applicatio will route through the static methods which will be using the same set of static fields in the class. If your application is single threaded then you probably don't have anything to worry about. If your application is multi-threaded (e.g. An ASP.NET application - all the sessions are running in the same application) then static methods will be a bottleneck because you'll only be able to allow one query to run at a time.
Does this help?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
OOOOOPS!
Is asp.net application multi threaded????????????
|
|
|
|
|
Yes. How did you think it can handle multiple requests simulataneously?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Is any way to build a table base on xml/xsd. Is any way to build xsd from xml. also xsd has some limitation like lentgh of string field.
Any idea ?
So Thanks
|
|
|
|
|
After opening a new xml file write:
<?xml version="1.0" standalone="yes"?>
<Users xmlns="http://www.yourwebsite.net/xmlfilename.xsd" >
<User><!--table name for each record-->
<ID></ID><!--field name-->
<name></name>
<age></age>
</User>
</Users>
Then from XML menu select CreateSchema to create xsd file,
To determine the Key field right click the element in the xml schema,Add,NewKey.
In code-behind page:
void GetUserData(string userID)
{
DataSet ds=new DataSet();
string strpath=Server.MapPath(Request.ApplicationPath);
ds.ReadXmlSchema(strpath+"\\xmlfilename.xsd");
ds.ReadXml(strpath+"\\xmlfilename.xml");
DataRow dr=ds.Tables["User"].Rows.Find(userID);
string username=dr["UserName"].ToString();
}
void SetUserData(string userID)
{
string strpath=Server.MapPath(Request.ApplicationPath);
DataSet ds=new DataSet();
ds.ReadXml("xmlfilename.xml");
DataRow dr=ds.Tables["User"].Rows.Find(userID);
dr["UserName"]="user1name";
ds.WriteXml(strpath+"\\xmlfilename.xml")
}
I am too late but i will never give up
|
|
|
|
|
So Thanks
I have three question. can I build select,insert,update and create table query from xml. how can I Create Schema without visual studio and what about relational database.
thanks
|
|
|
|
|
Sorry .. i didnt try any one from your clever ideas before...I think you should put your question as a new thread.
I hope one of the forum professional answer your question.
I am too late but i will never give up
|
|
|
|
|
Hi..
I have used Full-Text Search in a stored procedure with English words whithout any problems..But when i use it with Arabic words it gives me THE Error:
Server: Msg 7619, Level 16, State 1, Procedure SearchProject, Line 19
Execution of a full-text operation failed. A clause of the query contained only ignored words.
The stored procedure :
CREATE PROCEDURE SearchProject
(
@SearchString nvarchar(500),
@CultureName nvarchar(50),
@HowManyResults int OUTPUT
)
AS
CREATE TABLE #SearchTable
(
FieldNO int,
ProjectNO int,
ProjectName nvarchar(200),
ProjectDescription nvarchar(1000),
ProjectImage nvarchar(1000),
CultureID int
)
INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM FREETEXTTABLE(Project_Locale,*,@SearchString) AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID
JOIN Project AS P ON P.ProjectNO=PL.ProjectNO
WHERE PL.CultureID=dbo.GetCultureID(@CultureName)
INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM Project_Locale AS PL ,Project AS P,
ProjectField_Locale AS FL,
FREETEXTTABLE(ProjectField_Locale,*,@SearchString) AS FT2
WHERE FL.FieldNO=P.FieldNO
AND FL.FieldCultureID=FT2.[KEY]
AND PL.ProjectNO=P.ProjectNO
AND PL.CultureID=dbo.GetCultureID(@CultureName)
AND FL.CultureID=dbo.GetCultureID(@CultureName)
INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM Project_Locale AS PL ,Project AS P,
Feature_Locale AS PFEA,
ProjectFeature AS FP,
Feature AS F,
FREETEXTTABLE(Feature_Locale,*,@SearchString) AS FT3
WHERE FT3.[KEY]=PFEA.FeatureCultureID
AND FP.ProjectNO=P.ProjectNO
AND FP.FeatureNO=PFEA.FeatureNO
AND PL.ProjectNO=P.ProjectNO
AND F.FeatureNO=PFEA.FeatureNO
AND PFEA.CultureID=dbo.GetCultureID(@CultureName)
AND PL.CultureID=dbo.GetCultureID(@CultureName)
SELECT @HowManyResults=COUNT(DISTINCT ProjectNO) FROM #SearchTable
SELECT DISTINCT * FROM #SearchTable
RETURN
GO
I called the stored procedure using the following(in the Query Analayzer) :
USE nabeel1eagle
DECLARE @HowManyResults int
EXEC SearchProject 'ملفات','ar-SA',@HowManyResults OUTPUT
but when I use the previous code directly(without calling the stored procedure using EXEC) in the Query Analyzer it works fine.Like the following (part of the code)code:
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM FREETEXTTABLE(Project_Locale,*,'ملفات') AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID
JOIN Project AS P ON P.ProjectNO=PL.ProjectNO
WHERE PL.CultureID=dbo.GetCultureID('ar-SA')
Could any one help and tell me how to solve this problem?
|
|
|
|
|
I am building software for controlling test stands with Borland C++ Builder 2006. This program uses a database. I like to create a new database during runtime with SQL-commands.
How can I realize this?
|
|
|
|
|
hi all i would like to know how to insert an image in a column which data type is binary and also how to retrieve such data
thank u all for ur help and support
abdelhameed81
|
|
|
|
|
|
well actualy i don't know how to do it with this binary or whatever but i did it by creating a column as text
i affected the root of the image in this field then i do some codes in vb.net
& it works
try to be good if you can't be the best
|
|
|
|
|
We are using .net application with oracle 9i database,we are trying to run a join query through our application but we are getting Server Application Unavailable error after the program runs for 5 minutes. we are using Windows 2003 operating system.how to increase the query performance through .net application.
There are three queries that run the aspx (output is a report in aspx). The first query returns between 9 - 18 records and is displayed horizontally.
The second query returns around 150 records. The third query returns one record for the combination of first and second query.
Thanks
|
|
|
|
|
Have you put a break point in your code, or trace statements to verify what is taking the time? If it is the database queries (when you get the data through the DataAdapter or DataReader) then the problem is likely with your database. How long does it take to run these queries through Oracle's tools?
If it takes a long time to run the queries then consider adding indexes. The columns specified in the where clause and the join are generally good candidates. If Oracle has a query profiler to assist in index tuning you might want to use that to see where the bottlenecks in the query are.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
