|
Hi I have table Table1 contains SerialNumber, Name, City ,State
SerialNumber, Name, City , State
1 ABC Dallas TX
2 BCD VCDFV NY
3 ABC Dallas TX
Here I want to get the max of the serialnumber with if same combination of Name, City ,State is repeated , here 1 and 3 repeted , Please suggest how can I do this
Thanks,
|
|
|
|
|
select max(serialnumber), Name, City, State
from YourTable
group by Name, City, State
That's a start. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Building on Chris's post:
If you need only max by state you might try something like:
select yourtable.serialnumber, yourtable.name, yourtable.city, yourtable.state
from yourtable
Inner join (
select Max(serialnumber), state
from yourtable
group by state) as sub1
on yourtable.serialnumber = sub1.serialnumber
(NOT TESTED)
|
|
|
|
|
I currently have a table in SQL 2005 with two columns, one for Date, and one for Datetime. These are stored as nominal values, and is defined as the number of days since 12/30/1899, i.e. NOM_DATE 0 = 12/30/1899. Moment is defined as the number of minutes since 12/30/1899 12:00am (GMT), i.e. MOMENT 0 = 12/30/1899 12:00am.
My question is how would I go ahead and convert these into actual dates like dd/mm/yy,using a stored procedure?
Answer Found -
Using DATEADD(days, Date, '18991230') worked.
modified on Thursday, August 25, 2011 10:24 AM
|
|
|
|
|
Since you have already found the solution, why not post it as an answer and mark it as answered?
|
|
|
|
|
You can only do that on the QA forums and not the general programming forums
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
But when I click 'Good Answer' on a forum reply, I get a message saying 'Marked as answer'.
|
|
|
|
|
Pls guys, i wrote some code which is supposed to be installed on other computers. but when i finished, it could only run on my system, and generates SQL database errors when installed on other systems. the code was written in C#, i used the sql compact server that comes with visual studio 2008.this is how i wrote the connection string:
private static string connectionString()
{
string conString = @"Data Source=|DataDirectory|\electionsoft.sdf;Password=emejulu.4190;Persist Security Info=True";
return conString;
}
private void openConnection()
{
if(con.State == ConnectionState.Closed)
con.Open();
}
|
|
|
|
|
What errors? What OS on the target?
Did you actually set the DataDirectory value somewhere? (Or did you forget to do that like I forgot to do that a while back?)
|
|
|
|
|
The OS i'm using is Win7. How do i set the DataDirectory value
|
|
|
|
|
Put this somewhere where it will run before doing any database stuff:
AppDomain.CurrentDomain.SetData("DataDirectory", System.Environment.GetFolderPath(System.Environment.SpecialFolder.CommonApplicationData));
Depending on whether you're installing for "everyone" or for each individual user, choose the CommonApplicationData or LocalApplicationData "SpecialFolder" there at the end.
And, of course, that's where the database file has to sit. (which makes debugging from within Visual Studio a pain in the butt.)
|
|
|
|
|
Emejulu JVT wrote: Source=|DataDirectory|\electionsoft.sdf
Where does the 'DataDirectory' get put at? It's likely to be a permissions issue on Vista / Windows 7 if this is in the program files directory. Just guessing here as the actual error message was not included in the post.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
Hi,
I downloaded 'SSCERuntime_x64-ENU.exe' from http://www.microsoft.com/download/en/details.aspx?id=17876 but when I go in Visual Studio 2010 to Add Connection I only see Microsoft SQL Server Compact 3.5 and nothing for v4.
Am I missing something? Is there another download I should install? The system is Win7, 64bit.
Thanks in advance.
modified on Thursday, August 25, 2011 3:11 PM
|
|
|
|
|
Hi I use this
ALTER TABLE table_name
ADD column_name datatype to add column in table. How to use Alter Query to add column in query? I use MS Access 2003.
|
|
|
|
|
You can't add it.
Depends on what you mean by 'query'.
In terms of procs you must replace the proc either by using 'replace' or 'drop' and 'create'. Which one depends on the database.
If the query is other code then the code must be updated.
|
|
|
|
|
for example I have 1 query in who participate 3 columns in 2 table and I want to add 1 column in first table and column in query will be 4. I want do this with code.
|
|
|
|
|
ivo75 wrote: I want to add 1 column in first table and column in query will be 4.
As jschell indicated, you will need to use an alter statement to replace the existing view with a new one.
Here is a site that has more detail about how you alter a veiw in Access (I didn't think it was posible, but then I havent worked with MS Access since 1997)
http://allenbrowne.com/func-DDL.html[^]
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
ivo75 wrote:
for example I have 1 query in who participate 3
columns in 2 table and I want to add 1 column in first table and column in query
will be 4. I want do this with code.
Not sure I understand what you are saying.
However you appear to be referring to that you now have the following:
select t.a, t.b, t.c from...
And you want the following:
select t.a, t.b, t.c, t.d from...
The problem with that is that SOMETHING is using the first query. And it is not going to magically use the second one. At best (depending on a number of things) it will simply do nothing at all. At worse something will fail in a way that makes it difficult to determine what happened.
So the best case has no impact - so no point in trying to change it.
In the worst case many things fail - so not point in trying to change it.
Thus it follows, what you think that you want to do will NOT work.
|
|
|
|
|
alter exists column datatype.
alter table tblname
alter column columnname datatype(variablesize)
or
add new column................
alter table tblname
add columnname datatype(variablesize)
|
|
|
|
|
For an existing Access database holding around 100 queries, I'm looking for a comfortable way to export the actual SQL statements that represent those queries; ideally I'd like some (C#) code that generates a DataTable, with columns queryName and SqlStatement. Alternatively, a command or script that tells Access to create a text file holding such information. Or some magical SQL snippet that creates a new table in the database itself.
To be perfectly clear, I need the SQL statements, not the results of the queries themselves. And I'll need to run it a couple of times in the next few months, so I don't plan on doing it manually, one by one!
Luc Pattyn [My Articles] Nil Volentibus Arduum
modified on Wednesday, August 24, 2011 10:12 AM
|
|
|
|
|
This[^] is not exactly what you requested, but might prove a useful starting point.
|
|
|
|
|
Thanks a lot, I got it working right away.
|
|
|
|
|
|
Thanks for the quick response. I managed to solve my little problem with the first reply I got.
|
|
|
|
|
Problem solved, using "good" old DAO from C# isn't that hard after all, and all that was required is this:
public override void GetQueries() {
DAO._DBEngine dbEng=new DAO.DBEngineClass();
DAO.Workspace ws = dbEng.CreateWorkspace("", "admin", "", DAO.WorkspaceTypeEnum.dbUseJet);
DAO.Database db = ws.OpenDatabase(File, false, false, "");
foreach(DAO.QueryDef q in db.QueryDefs) {
env.log(0, q.Name+" = "+q.SQL);
}
}
Thanks for all the input!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
