|
Dear All,
i want to know how to get top three salary getters from the employee(eid , ename, salary) table
i tried this
select top 3 salary from employee order by salary desc
but it gives me top three salary record say there is salary 1000,1200,1300,1300,1500
then my query return me 1500,1300,1200 whereas i want to 1500,1300,1300,1200
how can i do it
please help
thanks
regards
imran khan
|
|
|
|
|
SELECT * FROM Employee
WHERE (Salary >=SELECT TOP 1 Salary FROM (SELECT DISTINCT TOP 3 Salary FROM Employee ORDER BY Salary DESC) DERIVEDTBL ORDER BY Salary))
|
|
|
|
|
Here is the pur SQL query. This will work irrespective of any RDBMS
select eid , ename, salary from employee
where salary in
(
(select max(salary) from employee),
(select max(salary) from employee
where salary<
(select max(salary) from employee)),
(select max(salary) from employee
where salary<(select max(salary) from employee
where salary<(select max(salary) from employee)))
)
order by salary desc
Regards,
Sylvester G
sylvester_g_m@yahoo.com
|
|
|
|
|
Hello,
Can someone tell me how can I get the entire script using query of table.
What I mean is, in SQL server, if we right click on a table, and say script table as-create to-New Query Editor Window, we get the entire script of the table.
I want that using a query.
Please help me its urgent.
Nana
|
|
|
|
|
please enlighten us more . give us an example
|
|
|
|
|
Thanks Sam.
I got the answer.
We can user SMO namespace
Nana
|
|
|
|
|
Hi all,
if we want to use Stored procedures in VB.Net page, then what are the namespaces we have to import?? and how can we implement Transaction management in the stored procedure??
thanks,
Rahi
If you look at what you do not have in life, you don't have anything,
If you look at what you have in life, you have everything... "
|
|
|
|
|
system.data.sqlclient
transaction can be maintained by simple way of begin transaction and thereby commit transaction or rollback the transaction.
If not you can use the concurrency levels too!!!
Gautham
|
|
|
|
|
hello
i'm trying to create One to One and One to Many relationships between two tables.
can anyone plz tell me the queries for
1. One to One
2. One to Many
supposing i have two table Table1 and Table2.
plz tell query for Onne to One relationship between Table1 and TAble2 and also One to many relationship between Table1 and Table2 through Alter table query(using Sql Server 2000)
Thanks for the time
Saira
|
|
|
|
|
The difference between One-to-One and One-to-Many is mostly conceptual. A query for either will be exactly the same. However, it can be enforced using a combination of a Foreign Key constraint and a Unique constraint. What this will do is enforce the relationship for UPDATE, INSERT and DELETE, but again, not SELECT.
If your schema looks like this:
<br />
CREATE TABLE Table1 (<br />
[ID] INT,<br />
BLAH,<br />
BLAH2<br />
)<br />
<br />
CREATE TABLE Table2 (<br />
[ID] INT,<br />
Table1ID INT,<br />
BLAH,<br />
BLAH2<br />
)<br />
<br />
Then you would create your constraints like this:
<br />
ALTER TABLE Table2 ADD CONSTRAINT FK_Table2_Table1 FOREIGN KEY(<br />
Table1ID<br />
) REFERENCES Table1 (<br />
ID<br />
)<br />
<br />
<br />
ALTER TABLE Table2 ADD CONSTRAINT UK_Table2 UNIQUE(<br />
Table1ID<br />
)<br />
|
|
|
|
|
Sorry, I forgot to specify. If you have a One-to-Many relationship you would like to enforce then all you need is the FOREIGN KEY constraint. If you have a One-to-One relationship then you need both the FOREIGN KEY and the UNIQUE constraints.
|
|
|
|
|
i have 3 columns in my table the room,timeStart,timeEnd.
For example i have inserted this data
room: room1
timeStart: 2007-03-02 8:00 AM
timeEnd: 2007-03-02 3:00 PM
Here's the data that will not be accepted if try to insert
room: room1
timeStart: 2007-03-02 9:00 AM
timeEnd: 2007-03-02 4:00 PM
because the timestart is between the timeStart and the timeEnd. Room1 will only be occupied after 3:00 PM.
Can you help me with this?.
Thank you very Much
Bernie
|
|
|
|
|
Use Trigger and put your condition there
|
|
|
|
|
Can u show me the posible code for that?..
Is it posible to use the CHECK CONSTRAINT?.
CONSTRAINT chk CHECK (timeStart BETWEEN timeStart AND timeEnd)
Can i use SELECT statement IN CHECK CONSTRAINT?
Bernie
|
|
|
|
|
Hello All,
I am working in Visual Studio 2003. I want to access local cube (.cub) file with ADODB object. How is this possible?
I am using the following code, but it doesnt seems to work
<br />
Dim cnActive As New ADODB.Connection<br />
Dim rs As ADODB.Recordset<br />
cnActive = New ADODB.Connection<br />
Try<br />
cnActive.Open("provider=msolap;Data Source=c:\temp\mycube.cub;", "", ",0)<br />
rs = cnActive.OpenSchema(ADODB.SchemaEnum.adSchemaCatalogs)<br />
Catch ex As Exception<br />
sError = ex.Message<br />
Exit Sub<br />
End Try<br />
The error is "The operation has failed because of an error in the COM component - unknown error c:\temp\mycube.cub Access Denied. ". I have given full access to this folder but no use. Any ideas?
thanx
Mubashir
Every job is a self portrait of the person who did it.
|
|
|
|
|
I have a table in an MS Access database with a Date/Time datatype for one of the fields. I generated a DataSet using a OledbDataAdapter in the VS.NET 2003 Forms Designer from the table mentioned previously.
Everything works as expected during Fill and Updates. My problem is that the DateTime displays the Date portion correctly, but the Time is constantly 12:00:00 AM, not the time that was inserted.
I recall seeing this problem a few years ago, but I have forgotten the solution and all of my searches come up empty.
Any help would be greatly appreciated!
Thanks,
Scott
"Some people spend an entire lifetime wondering if they made a difference. The Marines don't have that problem."
( President Ronald Reagan)
|
|
|
|
|
Please check your data insertion code you might be inserting only date not time.Ms-Access is showing it's default behaviour when you are trying to access date wiche is actually not present in the column or what it has inserted by it's own
|
|
|
|
|
The DataSet and INSERT statement where both created by the designer using a DateTime type for the column in code. The database is set to for Date/Time datatype. Even when I have tried to update it manually using SQL and oledb commands, the resulting query returns the same... Correct date, 12:00:00 AM time. There is a workaround somewhere, I've used it a few years back, but not recently enough to remeber what it was.
In the mean time, I changed the field datatype in the database to Numeric - Double. In my code I add a column at runtime and convert the OADate (Double) to a .NET DateTime type and disply the DateTime.ToShortDateString in generated column, then map that to my ComboBox DisplayMember property.
It works, but shouldn't be that way.
Thanks for the help
"Some people spend an entire lifetime wondering if they made a difference. The Marines don't have that problem."
( President Ronald Reagan)
|
|
|
|
|
Problem solved.
Solution:
Change all DataAdapter DateTime columns Input and Update parameters OleDbType to DBTimeStamp. By default the designer sets any Date/Time column to DBDate which only formats the data to store the date.
This goes for DataAdapters created by the designer and for manually coded OleDbParameters.
"Some people spend an entire lifetime wondering if they made a difference. The Marines don't have that problem."
( President Ronald Reagan)
|
|
|
|
|
Say I have a table with Trucks on it
TRUCK_ID, DriverName
e.g:
211, John
112, Sarah
456, Philip
And another table showing when a parcel was loaded on and off.
Tuck_IDOFF, TRUCK_IDON
e.g
211, 112 (Loaded off of John and onto Sarah)
456, 211 (Loaded off of Philip and onto John)
Now I want to run a query that will return: Tuck_ID, DriverName, Total number of offloaded (COUNT of where TRUCK_ID = Tuck_IDOFF) total number loaded (COUNT of where TRUCK_ID = Tuck_IDON)
so it would return:
211, John, 1, 1
112, Sarah, 0, 1
456, Philip, 1, 0
I know how to return the data form table 1 just fine, and the counts from table two ....
It's joining the 2 results into one set of results that it causing me problems.
Cheers.
|
|
|
|
|
select T.Truck_ID, T.DriverName,
(select count(*) from TruckLoad where Truck_IDOFF = T.Truck_ID) OffloadedCount,
(select count(*) from TruckLoad where Truck_IDON = T.Truck_ID) LoadedCount
from Truck T
order by T.Truck_ID
|
|
|
|
|
Hi,
I´m trying ot get a CLR-Trigger running. I Created the trigger, its insert correctly in the database, but when the SQLTrigger tries to open a tcpclient Connection, I get a Security Exception:
"{System.Security.SecurityException: Request for the permission of type 'System.Net.DnsPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Net.Sockets.TcpClient..ctor(String hostname, Int32 port)
at Triggers.Trigger1()
The action that failed was:
Demand
The type of the first permission that failed was:
System.Net.DnsPermission"
How / Where can I give the Trigger the needed Permissions?
-- modified at 10:58 Wednesday 28th February, 2007
|
|
|
|
|
The permissions referred to are Code Access Security permissions. If you created your assembly with the default settings in Visual Studio then the assembly is set to SAFE. In order to access external resources you need to increase the assembly to EXTERNAL_ACCESS. If you are using Visual Studio this is done using the project properties. Otherwise, if you are scripting the creation of your assembly use WITH EXTERNAL_ACCESS instead of WITH SAFE (or nothing - default)
|
|
|
|
|
I´m using standard .Net assembly... the Exception occures when the trigger tries to execute the line
TcpClient client = new TcpClient("127.0.0.1",13000);
The TcpClient is Part of the System.dll assembly, and this assembly should be usaly registered correctly?
|
|
|
|
|
The system.dll assembly isn't "registered" with Sql Server, it's part of the hosted CLR. Certain classes w/in the CLR have attributes called Host Protection attributes which indicate the type of functionality the class and it's methods expose. SQL Server restricts access to some these classes based upon how your assembly is created. In order for your assembly to access (it will compile fine, but it won't run) these classes you must create (register) your assembly using WITH EXTERNAL_ACCESS instead of WITH SAFE.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
