|
thanks, worked like a charm!
May your Karma grow with your speed and knowledge!
You are only as good as your last project.
|
|
|
|
|
hi
i want to have a function that gets
the SqlConnection and returns DataTableCollection.
i can not find anything on books.
what should i do plz?
|
|
|
|
|
Please elaborate on what you are trying to do. "a function that gets
the SqlConnection and returns DataTableCollection" doesn't make any sense at all.
|
|
|
|
|
function is a c# method that its parameter is a valuable
SqlConnection object for connecting to database and returns tables of database .
and how i can get sqldatatype of columns in this way?
thanks.
|
|
|
|
|
Make use of:
Select * from sysobjects (where type=u if you want user tables only)
select a.[name], b.[name] from syscolumns a, systypes b where a.[id] in(select id from sysobjects) and b.xtype=a.xtype
hope this will help you.
|
|
|
|
|
iam a beginner in SQL.
my request is , i have a table and there are many duplicate records in a table . i just want to remove all the duplicates records except one.
i need this query. pls help me
|
|
|
|
|
|
Colin's answer will filter out the duplicates when you do your query, but your post says you want to "remove" the duplicate records. If you want to actually remove duplicate records from the database, you will need to more than query the database, you will have to use a program to do that.
|
|
|
|
|
Or you can create a table and inserts the records from select distinct.
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
if you want to show just non-duplicate rows than
select * from TableName where Field1+Field2+Field3 not in(select Field1+Field2+Field3 from a group by Field1+Field2+Field3 having count(Field1+Field2+Field3)>1)
make sure that Field1, Field2 and Field3 are all non number type (or otherwise convert to varchar).
hope this will help.
|
|
|
|
|
Or event better (for showing NON-duplicate rows):
SELECT columnlist
FROM tablename
GROUP BY columnlist
HAVING COUNT(*) = 1 (Fill inn the columnlist with all columns of that table...)
Have a look at my latest article about Object Prevalence with Bamboo Prevalence.
|
|
|
|
|
I need to retrieve the primary key column name for the given table.
The data given is: Table name
The data to be extracted is: primary key column name
Can anyone help me out in this
|
|
|
|
|
You don't specify which database product you are using so I shall assume it is SQL Server 2000.
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
ON tc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
WHERE CONSTRAINT_TYPE='PRIMARY KEY'
AND tc.TABLE_NAME = 'MyTable'
This assumes also that there is only one schema (dbo) or that there are no tables with the same name in different schemas.
Does this help?
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
|
|
|
|
|
Hi there
Thank you very much. Iam using Sybase I have found the solution this command dont work in sybase, it was done in different way.
|
|
|
|
|
How come this exact CREATE TABLE statement works for connections to MSDE database but does not for Access .mdb? Code is in Visual C++.
-----------------------------------------
HRESULT hr;
::CoInitialize(NULL);
char CnnStr[200] = _T("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\test_db.mdb; User Id=admin;Password=;");
hr = m_Conn.CreateInstance( __uuidof( ADODB::Connection ) );
m_Conn->Open(CnnStr, "", "", NULL);
m_Conn->Execute("CREATE TABLE Employees (Lastname VARCHAR(25) PRIMARY KEY, First VARCHAR(25)", NULL, 1);
m_Conn->Close();
|
|
|
|
|
Nevermind. I figured it out.
|
|
|
|
|
I have a single function that I would like to see what it is returning. I am thinking specifically of:
DATEPART(month, getdate())
Could anyone tell me how to execute that just on its own?
Cheers,
Jim
|
|
|
|
|
select can be used to return anything, including the return value of a function.
So select datepart(..... will work fine.
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
Does anyone have any really good style guides for SQL? I wouldn't usually ask that but I wanted to know if there was a definitive guide.
Cheers,
Jim
|
|
|
|
|
One thing for sure, It should be easy to read and understand.
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
I don't know if this is a "really good style guide", but you could start with this... (Copied from our intranet Wiki):
Use proper indenting. It increases readability. (So does proper naming: See SqlNamingConventions)
Comments
When using comments in a SQL script or in StoredProcedures, you should use the line comment -- instead of the block comment . This makes it easier to refactor, experiment and debug code, since you are able to use block comments to temporarily disable parts of the script.
Descriptive header comments
When a procedure or script contains a descriptive header (as it always should). You should state not only information about parameters and returnvalues, but also which objects that are available outside of its scope it may modify. If you use temporary objects that are created outside of the script or procedure, you should state that also.
Indenting
This document suggests a set of indenting rules for a readable layout of complex queries.
Only these SQL keywords should be at a start of a line, at the same indenting level in the same query (except subqueries, which can be placed at their own indenting levels as a whole following the same rules.):
SELECT
FROM
WHERE
GROUP BY
HAVING
At the next indenting level you should naturally put the following items
- Column names in the select, if there are multiple columns in the select part.
- JOINs.
- Additional conditions in a WHERE clause starting with the words AND or OR.
- Additional GROUP BY conditions.
- Additional conditions in a HAVING clause if you put them on seperate lines.
- ON conditions of a JOIN could be placed on the next line, one more indentation than the JOIN.
Examples:
SELECT
p.Name,
k.Name
FROM Persons p
LEFT JOIN Kids k
ON p.PersonPK = k.ParentPersonFK
WHERE k.Age > 18
AND k.Height < 175
<br><br>
SELECT
Gender,
COUNT(*)
FROM Persons
WHERE Gender = 'To be confirmed...'
GROUP BY Gender
HAVING COUNT(*) > 0
Example showing the indenting of a subquery:
SELECT Something
FROM Somewhere
WHERE Something IN
(
SELECT
x
FROM
AnotherTable
WHERE Comment LIKE '%free sex%'
)
<hr>
ArjanEinbu
[Modified:]Fixed: The indenting sample didn't show the indenting[/Modified]
[Modified again:]It does... In IE... (I was using tha fox...)[/Modified again]
|
|
|
|
|
I have a questino in respect of dateadd.
I was on this website:
http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/da-db_5.htm
and I saw that they did a select on the data. I am curious how they could do that though as they are only going to have a specific point in time returned to them. How can they receive a span of times if there is only one point in time being returned?
Cheers,
Jim
|
|
|
|
|
Examples
This example prints a listing of a time frame for titles in the pubs database. This time frame represents the existing publication date plus 21 days.
USE pubs
GO
SELECT DATEADD(day, 21, pubdate) AS timeframe
FROM titles
GO
It's simple - they get a date, and they add 21 days to it. That's what the '21' parameter is for, and what DATEADD does.
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
I think you are missing my question:
From what I could tell, this function returned a time and not a time frame. Hence, how could a timespan of objects be returned?
Cheers,
Jim
|
|
|
|
|
It's not returning a timeframe at all. What it returns represents a timeframe, 21 days after the date that's in the database. If you look at the values returned, they are all dates. The timeframe is in the fact that 21 days has been added to the time in the DB.
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|