|
hi all,
i just want to know how to get the nth highest salary from employee table.any help appreciated.
with regards,
susa
|
|
|
|
|
select top 1 sal from (select top 6 from emp order by sal asc) order by sal desc
|
|
|
|
|
Try this
SELECT empid,MAX(Salary) FROM Employees WHERE EmpID IN (SELECT TOP 2 EmpID FROM Employees ORDER BY Salary Desc)
Muralidhar
|
|
|
|
|
hi,
when im executed this query i got only the first two higher values even if i speciofied top 5 values
thanks
with regards,
susa
|
|
|
|
|
hi boss ,
At last i got a solution for that problem.
i used this one
select min(sal) from(SELECT distinct TOP n sal FROM account ORDER BY Sal desc) as newsalary
here instead of n we can give what ever the highest number we need.
with regards,
susa
|
|
|
|
|
|
I hope I am in the right forum. My data type column from the table ClockIn_Date is DateTime. The dropdownlist seems to select distinct date+time. I want to only select distinct the date and not inclusive of the time. What is the correct sql statement?
SELECT DISTINCT ClockIn_Date<br />
FROM dbo.Stud_ClockIn
thanks in advance. Much appreciated.
|
|
|
|
|
Hi I am using sql reporting services 2000.
I have a stored proc as below,
----------------
CREATE PROC Casual_Hirers_Event_List
(
@StartDate Datetime
,@EndDate Datetime
)
AS
SET NOCOUNT ON
SELECT VENUE.Venue_Name
,EVENT.Start_Date
,EVENT.End_Date
,EVENT.Event_Name
,EVENT.Event_Number
,STATUS_DESC.Status_RecNo
,EVENT.Event_Status_Desc_RecNo
FROM EVENT EVENT
INNER JOIN EVENT_VENUE EVENT_VENUE ON EVENT.Event_RecNo = EVENT_VENUE.Event_RecNo
INNER JOIN EVENT_STATUS EVENT_STATUS ON EVENT.Event_RecNo = EVENT_STATUS.Event_RecNo
INNER JOIN STATUS_DESC STATUS_DESC ON EVENT_STATUS.Status_RecNo = STATUS_DESC.Status_RecNo
INNER JOIN VENUE VENUE ON EVENT_VENUE.Venue_RecNo = VENUE.Venue_RecNo
WHERE (EVENT.Start_Date >= @StartDate)
AND (EVENT.Start_Date < @EndDate)
AND (STATUS_DESC.Status_RecNo = 4
OR STATUS_DESC.Status_RecNo = 37)
AND (EVENT.Event_Status_Desc_RecNo = 1)
ORDER BY VENUE.Venue_Name, EVENT.Start_Date
SET NOCOUNT OFF
Return
GO
----------------------
I want this proc to be executed in reporting service without creating another dataset which prompts me first for the start and end date and display the results.
is this possible in sql reporting services.
if i run with only one data set i get following error
----
c:\reports\casual hirers event list\Report1.rdl The report parameter ‘StartDate’ has a DefaultValue or a ValidValue that depends on the report parameter “StartDate”. Forward dependencies are not valid.
----
please help.
|
|
|
|
|
Hi All,
I have a table called movements:
AssetID Date LocationID
1234 3/4/06 15
312 5/6/05 12
1234 12/12/05 18
1234 8/8/06 15
312 9/9/05 10
The latest date in the table for each Asset shows where that Asset currently is. Can someone please help me with a query that, for each asset, will return its current location?
Thanks,
Martin.
-- modified at 16:06 Wednesday 9th August, 2006
|
|
|
|
|
select locationid from movements mt inner join<br />
(select max(date) as dt, assetid from movements group by assetid) as mt2<br />
on mt.date = mt2.dt and mt.assetid = mt2.assetid
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hi All
I want to calculate the total of some monatry amounts within a table
i can do this by the following
declare @cost1 money
select @cost1 =sum(cost)from mydb..amounts
sometimes though there maybe no records in the amounts table, and the above statement would then return NULL
instead of NULL i would like it to return 0
i would like to do something like
select @cost1 =sum(cost) 0 as null from mydb..amounts
but this doesnt work
does anyone have any ideas ??
Thanks
Si
|
|
|
|
|
select sum(nvl(cost,0) total_cost from mydb..amounts;
Chris Meech
I am Canadian. [heard in a local bar]
Nobody likes jerks. [espeir]
|
|
|
|
|
Chris Meech wrote: select sum(nvl(cost,0) total_cost from mydb..amounts;
thanks, but this doesnt work
whats nvl ??
|
|
|
|
|
SELECT @cost1 = ISNULL(SUM(cost), 0) FROM mydb..amounts
|
|
|
|
|
My current procedure returns XML but sans any kind of "root" tag... is there a simple clause to get it to wrap the returned values in a root element ?
Thanks
|
|
|
|
|
Hi there,
Im looking for a method to do bulk insert into SQL Server 2005 from a Generic List<t> and if its possible!?!
In .NET 2.0 is SqlBulkCopy useful and maybe we can use it!?! But we have do a extra step.
The flow:
DataSource > SqlBulkCopy > DataSource
List<> IDataReader dbo.myTable
Examplecode:
<br />
SqlConnection c = new SqlConnection("xxx"));<br />
SqlBulkCopy bc = new SqlBulkCopy(c);<br />
<br />
bc.DestinationTableName = "dbo.myTable";<br />
<br />
c.Open();<br />
<br />
myItem mi;<br />
List<myItem> myList = new List<myItem>();<br />
<br />
for (int i = 0; i < 10; i++)<br />
{<br />
mi = new myItem();<br />
mi.Text = "a" + i.ToString(); <br />
myList.Add(e);<br />
}<br />
<br />
IDataReader r = myList.????<br />
<br />
bc.WriteToServer(r);<br />
...<br />
Do you have any idea?
Regards
//Albin
|
|
|
|
|
I'm having trouble using two seperate BindingContext's on the same form.
The form contains two group boxes, each containing a TextBox. The TextBoxes are bound to the same field of the same BindingSource. Each group box has it's own BindingContext:
void BindControls()
{
groupBox1.BindingContext = new BindingContext();
groupBox2.BindingContext = new BindingContext();
textBox1.DataBindings.Add("Text", projectsBindingSource, "prDescription");
textBox2.DataBindings.Add("Text", projectsBindingSource, "prDescription");
}
projectsBindingSource is of type BindingSource!
Now I would expect that I could scroll the context in one group box independantly of the other. However, when I execute the following code:
BindingManagerBase projectsBinding;
projectsBinding = groupBox1.BindingContext[projectsBindingSource];
projectsBinding.Position++;
Both groupBox1 and groupBox2 scroll to the next record.
Ant idea's
Regards,
Kees Vermeulen
|
|
|
|
|
i need to store wav files into my database and reterive it again
how to do that
ma_refay
|
|
|
|
|
Use an image column - it can store up to 2Gb of any binary data you choose.
|
|
|
|
|
I am somewhat at a lost right now and would like some guidence. I have a SqlDataAdapterthat I want it to use a stored procedure. The stored procedure has 4 parameters. Here is the code passing the values:
sqlDataAdapter1.SelectCommand.Parameters.Add("@BNAME",SqlDbType.Char).Value = strBname;
sqlDataAdapter1.SelectCommand.Parameters.Add("@ENAME",SqlDbType.Char).Value = strEname;
sqlDataAdapter1.SelectCommand.Parameters.Add("@BZIP",SqlDbType.Int).Value = intBzip;
sqlDataAdapter1.SelectCommand.Parameters.Add("@EZIP",SqlDbType.Int).Value = intEzip;
When I run the code I get a sql error stating
this
Here is my stored procedure:
ALTER PROCEDURE dbo.SelectActive
(
@BNAME char,
@ENAME char,
@BZIP int,
@EZIP int
)
AS
SELECT CustName, CustAdd, CustCity, CustState, CustZip
FROM CustomerList
WHERE (CustType = 1 OR CustType = 6) AND (ASCII(CustName) >= ASCII(@BNAME)
AND ASCII(CustName) <= ASCII(@ENAME)) AND (CustZip >= @BZIP) AND
(CustZip <= @EZIP )
ORDER BY CustName
RETURN
When I was configuring the data adapter, I came across this page. I am a little comfused on what to do here. I can select which data column on the right, but can't do anything with it. As you can see from the procedure, 2 data rows are affected. What to do?
Thanx in advance!
Jude
|
|
|
|
|
I'm not sure what is going on here but it seems to me that you are looking for a CREATE PROCEDURE rather than 'ALTER'.
Steve
|
|
|
|
|
Hi,
I have put some free useful info on :
http://www.vkinfotek.com/data.html and
http://www.vkinfotek.com/object.html
If you need any additional specific info kindly email me or post here.
Regards
Bhar
|
|
|
|
|
We have a number of built scripts in VS2003 database projects and we utilize the Create Command File... command which creates a self-contained cmd file for running the scripts. We push this to our ops people for managing elevations of data/databases/code to production.
Visual Studio 2005 does not appear to have the Create Command File option in it anymore. But usually ( emphasize usually ) MS has an alternative somewhere. Is there an alternative or equivalent for creating the cmd file for a Database Project, and how do I use it???
We do not want our ops people to be selecting scripts and clicking Run since there is always the chance they can miss a script and impact a production deployment.
Thanks,
-- modified at 12:03 Tuesday 8th August, 2006
|
|
|
|
|
Hi,
I need to do a "select distinct emp_type from dtName" on a datatable in vb.net. in order to get a list of distinct employee types from a datatable in code. I can't work on DB side, caue i'm getting data from text file, and looping through every record is hideous!
Regards,
Tintin
|
|
|
|
|
Issue SQLcommand.Text = "select distinct emp_type from dtName" in your VB code.
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
