|
Distinct won't do this because unless the timestamp was the same for both records, then you have two distinct records. What you could do is to select based on the MIN timestamp (there are may ways to do this).
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world."
Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that."
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Assuming "Oldest" means been in the DB the Longest.
This is more complex than first meets the eye due to the fact that you need to tie in the Oldest TimeStamp with its cooresponding Body field.
SELECT T1.SenderID, T2.MIN([TimeStamp]), T2.MIN(Body) AS Body
FROM Messages T1
INNER JOIN (
SELECT MIN[TimeStamp] AS MINTimeStamp
,Body
FROM Messages
GROUP BY Body
)T2
ON T1.Body = T2.Body
AND T1.[TimeStamp] = T2.[TimeStamp]
WHERE RecipeintID = 1
GROUP BY T1.SenderID
I did this blind, without sql around, so forgive me if it is a little off. It should be pretty close to what you are looking for if I understood what you were trying to do.
SELECT T1.SenderID, MIN(TimeStamp) AS MINStamp, Body AS Body
FROM @Messages T1
INNER JOIN (
SELECT
GROUP BY SenderID
|
|
|
|
|
Assuming "Oldest" means been in the DB the Longest. And I understood what you are trying to do this should be pretty close. I did this without a SQL Server so forgive if a little off.
This is more complex than first meets the eye due to the fact that you need to tie in the Oldest TimeStamp with its cooresponding Body field.
SELECT T1.SenderID, T2.MIN([TimeStamp]), T2.MIN(Body) AS Body
FROM Messages T1
INNER JOIN (
SELECT MIN[TimeStamp] AS MINTimeStamp
,Body
FROM Messages
GROUP BY Body
)T2
ON T1.Body = T2.Body
AND T1.[TimeStamp] = T2.[TimeStamp]
WHERE RecipeintID = 1
GROUP BY T1.SenderID
|
|
|
|
|
Assuming "Oldest" means been in the DB the Longest. And I understood what you are trying to do this should be pretty close. I did this without a SQL Server so forgive if a little off.
SELECT T1.SenderID, T2.MIN([TimeStamp]), T2.MIN(Body) AS Body
FROM Messages T1
INNER JOIN (
SELECT MIN[TimeStamp] AS MINTimeStamp
,Body
FROM Messages
GROUP BY Body
)T2
ON T1.Body = T2.Body
AND T1.[TimeStamp] = T2.[TimeStamp]
WHERE RecipeintID = 1
GROUP BY T1.SenderID
|
|
|
|
|
I have impersonated a user for my project (C# ASP.NET) lets call him Mr:X.
For security reasons I want to connect to a database with another user Mr:Y.
I still want to use windows authentication and Integrated Security=SSPI.
Is this possible?
|
|
|
|
|
hi folks,
Iam working sql server 2005
Iam unable to change the already existing identity value.
Pls think a while for this,,,,,,i already tried
alter table "ALTER TABLE <table_name> ALTER COLUMN <column_name> IDENTITY (x,y)"
its urgent
thanks in advance
Rupa
|
|
|
|
|
DBCC CHECKIDENT (jobs, RESEED, 30)
I hope this will help to reset the identity value
|
|
|
|
|
Hi genius,
Nice to meet you here!
When developing BI programs with SQL Server 2005, I got this problem:
Users want the report should be exported in office excel 2003 format, and stored in PIVOT TABLE.
We have decided to use OLAP tech in SSAS(SQL Server2005 Analysis Services) to generate cubes.
If we just use excel as a client tool, and create a pivot table which connects to the cube database, it's ok.
But, the users want to download the excel file from a web page, further more , they want to filter the data through the web page.
How can I do?
Wait inline.
Thank you!
|
|
|
|
|
Hi,
Myself imran, I am quite new to dotnet. i am developing web application using asp.net and C#(Visual Studio 2005). i have defined connection string in web-config like <connectionstrings>
<add name="connstr" providername="System.Data.SqlClient" connectionstring="server=local;database=HFSERVICES;uid=sa;pwd=;">
Can any one help me to know How to Access database using this connetion string.
thanks
regards
imran khan
|
|
|
|
|
Welcome to The Code Project.
Please don't post the same question all over the place. Pick the forum that best fits your question.
---
b { font-weight: normal; }
|
|
|
|
|
Thank you for first Lesson.
regards
imran khan
|
|
|
|
|
keyValue = System.Configuration.ConfigurationManager.AppSettings("key")
gets the connection string from web.config
Steve Jowett
|
|
|
|
|
Hello,
I'd appreciate if someone could kindly help me with the following database design issue. I've already solved it, but my solution is not much elegant. I'm working on this SQL database for one of my friends. It will make her work more efficient since she's currently dealing with a tangled up set of excel tables.
The database will hold various data for several thousand employees. This would be a piece of cake, but we also need to track the changes of those data values in time. For example, we need to know that John Smith was in Quality Department in October, but since November, he's been in Services Department, etc.
Having a separate "historical data states" table for each employee (or, alternatively, for each data type) is not a good idea, so I designed a simple table named "TimeTableItem" that stores:
- a link to the table that holds table.column names (for instance "19" for dbo.Department.DepartmentID table) to which the current row relates ("TimeTableDataTypeID" column below)
- and an ID of the related value itself (for example, "7" for Services Department within the Department table - "TimeTableDataValueID" column below)
The actual table design:
- TimeTableItemID (PK, int, not null)
- PersonID (FK, int, not null)
- TimeTableDataTypeID (int, not null)
- TimeTableDataValueID (int, not null)
- StartTime (dateteime, null) (null is treated as "from the beginning of time" 
- EndTime (datetime, null) (null is treated as "forever"
- CreatedOn (datetime, null)
- ModifiedOn (datetime, null)
Obviously, the PersonID is a link to the Person table. By the same token, StartTime and EndTime define in which time span the data for the particular person is in effect, CreatedOn and ModifiedOn denotes these self-explanatory timestamps.
The trouble with this kind of design is that we don't know beforehand to WHAT employee data (Department? Position? Salary band?) will the next row be related, so we can't set a relation between the tables here (we dont' know what will the other table be). This means that there's no way to set a foreign key constraint on a column that holds the VALUE of the related data element. This is not much consistent and might lead to a situation when my friend deletes a department from the departments table that is used somewhere else. This will result in orphaned rows.
Is there any way to solve this efficiently?
Thank you for any input,
Michal
-- modified at 21:14 Monday 4th December, 2006
|
|
|
|
|
michal.kreslik wrote: The trouble with this kind of design is that we don't know beforehand to WHAT employee data (Department? Position? Salary band?)
Then use a table for each type. A HistoricalDepartment table, a HistoricalSalary table and so on.
|
|
|
|
|
Yes, that's one of the options I was talking about, but is that really a systematical solution to implement a distinct table for each data type? I don't think so. There must be a way to "join" the individual datatype tables' values' relations in one master table.
Thanks,
Michal
|
|
|
|
|
Hello,
SQL Server 2005, VS 2005
I have a checked list box. The user will select a combination of staff members from the checked list box. This will display these staff members and their tasks that they have to do. Also I have a additional 4 check boxes on the form, where the user can select the prority to be shown. i.e from the checked list box, check staff members 1, 5, 8, 12. and display the prority as high. There 4 prority check boxes (low, normal, urgent, and very urgent)
All are dislayed in a datagrid on the form.
Currently there a 15 staff member that will be in the checked list box, but this could grow as more stafff members are added.
My problem is Writing a select query for each of the possible combinations of staff members and the prority.
I could end up writing many many select queries for each combination.
Is there a simple way to write a select query for this type of situation?
Many thanks in advance,
Steve
|
|
|
|
|
steve_rm wrote: My problem is Writing a select query for each of the possible combinations of staff members and the prority.
I could end up writing many many select queries for each combination.
Definitely DO NOT create a select statement for each possible combination. Build your select statement dynamically. When the user clicks the refresh button (or whatever), then put together a criteria list of staff member IDs and priorities.
Select * from Staff inner join StaffTask on Staff.StaffID = StaffTask.StaffID where Staff.StaffID in(1,5,8,12) and StaffTask.Priority in ('urgent','very urgent')
Be sure to check this out:
SQL Injection Attacks and Some Tips on How to Prevent Them[^]
--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
|
|
|
|
|
I'm trying to gather statistics from multiple queries to gauge our database performance. So, I dropped a "Set statistics io on" (with matching off) at the top of my query. In Sql Management Studio, the results of the query come back in the Results tab, while the IO statistics come back in the Messages tab. How can I gather the IO statistics (the messages) from .NET with the provided SQLCommand/SQLDataAdapter/etc. classes? I tried pausing my test app just after the query ran and looking through all the involved objects for the IO messages but couldn't find anything.
|
|
|
|
|
The SQLConnection object fires an InfoMessage event that you must catch.
|
|
|
|
|
Hi
Iam parsing very large text files-around 50MB each using split function. I populate a dataset from it and then use sqlBulkCopy to insert the records in the datbase. But im getting a low virtual memory error and it is taking a lot of time? Any other approaches-help?
Amna
|
|
|
|
|
Have you tried using a DTS job to do the same thing ?
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
No i havent. But if it is more efficient i would surely try it. Thanx
|
|
|
|
|
iam using sql server 2005 express edition. It does not have the DTS option. Any other suggestions?
|
|
|
|
|
Look at BULK INSERT in the SQL help file. If your file is in a compatible format, then this will probably be your fastest option. Otherwise, just send it one record at a time using a SQLCommand object; a stored procedure will be slightly faster than sending a bunch of INSERT statements. The DataSet object is probably taking up a lot of your memory. I routinely load text files of up to about 1GB one record at a time. I have a bunch of validation for each record, so it is very slow, but even with the validations, it loads at about 1000 records per second.
|
|
|
|
|
Sorry to bother you with this question. I'm totally new to this and I don't know what I'm doing
I want to use an Excel spread sheet as a database. Any ideas how to do it? I think I have to use ODBC but I have no idea how to write the appropriate connection string and how to write a query?
I've been googling for some time but I cannot find any information on this topic. Where should I look?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
