Database

Krish - KP30-Jul-07 22:30

Krish - KP

30-Jul-07 22:30

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

_Damian S_30-Jul-07 19:33

_Damian S_

30-Jul-07 19:33

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!!

Member 387988130-Jul-07 20:22

Member 3879881

30-Jul-07 20:22

I think ur way of concept will be help full for me...,
Thank u,

Regards,
Magi

Member 387988130-Jul-07 20:32

Member 3879881

30-Jul-07 20:32

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

Blue_Boy30-Jul-07 22:06

Blue_Boy

30-Jul-07 22:06

SELECT     ID, MAX(value1) AS value1<br />
FROM         tableName<br />
GROUP BY ID

I Love SQL

Installing and using Developer 2005

Member 387988131-Jul-07 0:01

Member 3879881

31-Jul-07 0:01

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

Paul Selormey30-Jul-07 15:08

Paul Selormey

30-Jul-07 15:08

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.

Re: Installing and using Developer 2005

Mike Dimmick30-Jul-07 22:58

Mike Dimmick

30-Jul-07 22:58

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.

Stability. What an interesting concept. -- Chris Maunder

Alphabetical Select Statement

Sam Heller30-Jul-07 12:00

30-Jul-07 12:00

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

Paul Conrad30-Jul-07 12:16

30-Jul-07 12:16

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

Sam Heller30-Jul-07 22:16

30-Jul-07 22:16

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%

andyharman30-Jul-07 22:44

andyharman

30-Jul-07 22:44

Hi Sam

Assuming that you are using SQL-Server, try:

select * from somewhere where nameofperson like '[ABCDEF]%'

You might want to check that you have a case-insensitive collation set for your database - otherwise you will need to use the "Upper()" function around your "nameofperson" column.

Regards
Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

Sam Heller30-Jul-07 22:47

30-Jul-07 22:47

Hey good work, it works. I didn't think it would be that simple. Turns out it does pull both lower and uppper case records as well. Thanks for your help.

Paul Conrad31-Jul-07 3:01

31-Jul-07 3:01

Andy's solution is a very good one Smile | :)

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Sam Heller31-Jul-07 3:05

31-Jul-07 3:05

Yeah i think it kind of trumps yours for the fact that I can skip letters out in the way he proposes. Thanks for your solution though. It does indeed produce the results I require.

Paul Conrad31-Jul-07 13:10

31-Jul-07 13:10

Andy's works fine as long as you aren't asked to do something like A-Q Smile | :)

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Paul Conrad31-Jul-07 3:00

Re: Select from a DataTable

31-Jul-07 3:00

Here is what I was thinking of:

SELECT * FROM sometable WHERE ( Left( someField,1) >= 'A' AND Left( someField,1) <='D' )

This would return all rows in sometable where the first letter is A, B C, D. To do something like AD, you could just have this query in a stored procedure and have it parse your parameter A and D in this case, and plug it into the WHERE clause...

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

"Fuzzy search" component

afendi30-Jul-07 11:49

afendi

30-Jul-07 11:49

Good day.

I'd like to add "fuzzy search" functionality to my application.

"Fuzzy search" in this topic means selecting (from DB table) rows, which have "fuzzy search" coefficient (calculated using etalon string) not less some_predefined_const. Fuzzy search coefficient calculating algorithm can be various.

So with etalon string "Margaret" "fuzzy search" can find "Nargaret", "Margoret", "Margret" etc.

IMHO time to develop, test and tune code must be quite long. I prefer to buy such "fuzzy search" component.

Does anybody know where can I get such component - server version (SQL 2005) or client version (.NET)?
How much can such component cost?

Thanks.

Select from a DataTable

MohammadAmiry30-Jul-07 8:05

MohammadAmiry

30-Jul-07 8:05

I have a DataSet which has Two tables: TableAll, TableQueried
TabelAll has all data available.
What I want is to use a command to select only those rows in TableAll and copy them to TabelQueried which are LIKE some pattern.
I knew how to do this when I had a database and used connections; but now there is no database.
How should I do anything like this command:
"SElECT * FROM TABELALL WHERE Data LIKE [pattern]"

Aaron VanWieren30-Jul-07 13:56

Aaron VanWieren

30-Jul-07 13:56

I assume you are talking about an ADO.NET way?

If so look at DataTable.Select(CriteriaString).

I think the select method has an override for an order by clause. Criteriastring is a string in the format of:

(column name) expressions such as = or like or in; google datatable.select to get list of expressions. And then the criteria, very similar to a where condition in a sql statement.

IE for examaple

DataRow[] rows = myDataTable.Select("Column1 = 25","column1 asc")

This will return an array of DataRows which you can then work with.

Hope this helps!

Aaron

_____________________________________________________________________

Our developers never release
code. Rather, it tends to escape, pillaging the countryside all around.

The Enlightenment Project (paraphrased comment)

Visit Me at GISDevCafe

Re: Select from a DataTable

MohammadAmiry31-Jul-07 2:55

MohammadAmiry

31-Jul-07 2:55

THANK YOU!! Wink | ;)

You really helped me out! Rose | [Rose]

I was JUST looking for this.

Re: Select from a DataTable

Aaron VanWieren31-Jul-07 8:55

Aaron VanWieren

31-Jul-07 8:55

The only caveat you need to remember is that the datatable select consumes quite a bit of memory as it has to go through every record in the table. Be sure to set a primary key column and index if possible.

Aaron

View Next & Previous Data using C#

Saiyed Alam30-Jul-07 6:06

Saiyed Alam

30-Jul-07 6:06

Is there any way to view next or previous data of SQL database without using SQLDataAdapter and DataSet. If there is then how?? And If there is no way then what's the procedure of using these two thing?

Re: View Next & Previous Data using C#

Paul Conrad30-Jul-07 13:59