|
No krish i didnt get any o/p like that..,
See i have modified my query,i need o/p for that selcted month also...,
For that id=102 it want to display 5th month record not 4th one,
That i have modified...,
Plz send query for like that...,
Hlp me plz,
Advance thanks
-- modified at 9:00 Tuesday 31st July, 2007
Regards,
Magi
|
|
|
|
|
hi,could anyone explain me on how to create columns in sql wen i enter a charcter value in text box?
let say,if i enter A, one column need to be create,if B two need to be created in existing table ....
how can i do this???
thanks for any help!!!
The Best performance for good work tomorrow is to do good work Today
|
|
|
|
|
Need to create a table (CREATE TABLE ...)
and then add columns (ALTER TABLE ...)
refer this url for usage of CREATE TABLE & ALTER TABLE
http://sqlzoo.net[^]
Regards
KP
|
|
|
|
|
hi all
I want to trace only one Database operation using SQL profiler
can any busy tell me how to use it
when i create trace it shows all the events , but I only want to check of one database
Thanks in advance
The Stifler
--
Bugs can neither be created nor be removed from software by a developer. They can only be converted from one form to another. The total number of bugs in the software always remain constant.
|
|
|
|
|
Create a new template. You'll be able to edit the filters. One of those should be database name. Theres also client name which can be handy if there are mutliple developers.
|
|
|
|
|
In the SQL Server 2000 version, I found that filtering on database name was not reliable (I can't recall whether you got all databases or no results at all - it was one of those). Instead, use the DB_ID function to find out the database's numeric ID and use that in the appropriate filter.
|
|
|
|
|
Wierd 
It's been a while since I used it with 2000 but I dont remember having any issues with it. Then again I can't remember how to spell my middle name most of the time :P
|
|
|
|
|
Hi i have one table like this format:
ID Value1 EDate
100 2000 5/21/2007
101 3200 5/15/2007
102 1700 4/11/2007
103 2800 3/23/2007
100 2500 6/12/2007
102 3500 7/10/2007
Now i want the latest record of each employee:
means my output want to be like this:
Id value1 Edate
100 2500 6/12/2007
101 3200 5/15/2007
102 3500 7/10/2007
103 2800 3/23/2007
How to write the query?,
Advance thanks,
Regards,
Magi
|
|
|
|
|
is this what you are looking for
SELECT a.*
FROM a
INNER JOIN (SELECT id, Max(edt) AS edt FROM a GROUP BY id)b
ON a.id = b.id AND a.edt = b.edt
ORDER BY a.id
Regards
KP
|
|
|
|
|
No Krish,
I didnt get what ur trying to say...,
See the above table...,
And My o/p details...,
Plz hlp me,
Regards,
Magi
|
|
|
|
|
What i understood :
need to fetch thoes rows with max(EDate) for each group of ID
solution :
1. fetch all max(EDate) group by ID (in my query i've used inline view)
2. then get corresponding rows for the ID & EDate
I've just created a table by name "a" nad inserted the rows given in your example. my query returns the same rows as listed in your sample output.
Regards
KP
|
|
|
|
|
Suppose if i want the a paricular employee previous date value want, how to fetch that?
Means second highest date value need means?,
Table have like this:
Id value1 Edate
100 2000 5/21/2007
100 2500 6/21/2007
100 3000 7/11/2007
I want o/p like:
Id value1 Edate
100 2500 6/21/2007
Advance thanks,
Regards,
Magi
|
|
|
|
|
earlier i've given following solution for getting nth max value. you can use the same.
DECLARE @nth as bigint
SET @nth = 10
SELECT *
FROM TblA AS a
WHERE @nth = (SELECT COUNT(ColA)
FROM TblB AS b
WHERE b.ColA >= a.ColA)
This will return 10th Max.
change value of the variable @nth to get the required Max.
Regards
KP
|
|
|
|
|
Really ur query confusing me...,
I dont know how to use it...,
I given only one table...,
In that how u got 2 tables?,
And whats that cola & colb?,
Really dont know...,
Sorry,Plz see my table and tell me,
Regards,
Magi
|
|
|
|
|
Regret for typing mistake. Both are same tbale like "TableA"
Comparing the column (ColA) in your case EDate (also id to be used. because date is dependet on id) from outer query & subquery,
Then counting the values which are more than outer query's value.
following must work. I've checked at my end.
DECLARE @nth as bigint
SET @nth = 2
SELECT *
FROM TblA AS a
WHERE @nth = (SELECT Distinct Count(*)
FROM TblA AS b
WHERE b.EDate >= a.EDate AND b.id = a.id)
Once again regret for the mistake
Regards
KP
|
|
|
|
|
These multi-date/multi-value ones can be a bit tricky.
One solution is this: (and it assumes that each employee only has a single entry for any given date)
create a view, called vwEmployeeMaxDate that contains the following:
select ID, max(EDate) as MaxDate from TABLENAME
group by ID
Secondly, to get the information you want above, use the following query:
select TABLENAME.ID, TABLENAME.Value1, TABLENAME.EDate
from TABLENAME inner join vwEmployeeMaxDate on (TABLENAME.ID = vwEmployeeMaxDate.ID) and (TABLENAME.EDate = vwEmployeeMaxDate.MaxDate)
brackets not necessary, but used for emphasis of the two parts of the join.
Hope this helps.
-------------------------------------------
Don't walk in front of me, I may not follow;
Don't walk behind me, I may not lead;
Just bugger off and leave me alone!!
|
|
|
|
|
I think ur way of concept will be help full for me...,
Thank u,
Regards,
Magi
|
|
|
|
|
Suppose if i want the a paricular employee previous date value want, how to fetch that?
Means Suppose table have like this:
Id value1 Edate
100 2000 5/21/2007
100 2500 6/21/2007
100 3000 7/11/2007
I want o/p like:
Id value1 Edate
100 2500 6/21/2007
Advance thanks,
Regards,
Magi
|
|
|
|
|
SELECT ID, MAX(value1) AS value1<br />
FROM tableName<br />
GROUP BY ID
I Love SQL
|
|
|
|
|
Hi blue boy,
Some times the value wil go decrement also...,
So we cant say max(value),
then it will be give wrong o/p,
Regards,
Magi
|
|
|
|
|
I am currently having problems with the SQL Server 2005 Developer.
- I have a Windows XP SP2 Japanese version.
- This machine is connected to a domain managed by Linux server.
- I login with a domain account and install.
Initially, the intallations were failing with an error message that I do not
have administrator right.
We created an account on my machine with the same user name as that used
for the domain, and gave this administrator rights.
With this the installation was completed. However, there is another problem,
I cannot connect to the SQL Server.
For the installation, I chose "Local System" with Windows Authentication,
but the connection to the database still fails with the error message that
remote connection is not supported. I do not understand why I need TCP to
accesss a local installation of the SQL Server.
However, I followed the procedure outlined in a MS KB to enable TCP. But the
connection to the database still fails.
I am using the Management Studio to connect to SQL Server. I have also tried
creating a database in VS. NET 2005 and this also fails. I have checked the
services and all are running.
What is wrong and what can I do? (it takes more than an hour to install this
stuff and it is really consuming my time). Any help?
Best regards,
Paul.
Jesus Christ is LOVE! Please tell somebody.
|
|
|
|
|
By default only local machine administrators have rights to connect to SQL Server. You need to add your Windows user account as a login in SQL Server, then assign it appropriate rights, if your domain user account is a standard user on the local machine.
To make it the equivalent of the Administrators group login, make your user account's login a member of the sysadmin server role.
|
|
|
|
|
I am unsure about how to code in SQL the following select procedure. I have a list of companies, around 500, and I want to display them in a usefull way on my site. I have proposed a A-D E-H I-L etc system where the user can select just a chunk of the alphabet to display. Therefore I am going have some form of range parameter that I will be adding to the SQL query.
What would be the best way to create this code in a modular fashion so that I could fire different variations of ranges at it without having to pre set them all up.
I would guess the code would ustilise the LIKE keyword with a wildcard after it but how would I create the prefix. I have never heard of any Alphabetical objects in SQL.
Any help would be greatly appreciated. Thanks
|
|
|
|
|
You could have a computed field that contains just the first letter, then query for range letters off that computed field.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Sorry I don't follow this. Could you explain, possibly using some code, this solution in more depth.
I really want to be able to pass a parameter of say "AD" and then create some code that could work out the rest of the letters needed and then perform something like the following.
SELECT *<br />
FROM SOMEWHERE <br />
WHERE nameofperson LIKE a%<br />
AND nameofperson LIKE b%<br />
AND nameofperson LIKE c%<br />
AND nameofperson LIKE d%
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
