|
PaulC1972 wrote: Consider it done
Woo hoo!!! I'm famous! 
|
|
|
|
|
Colin Angus Mackay wrote: Woo hoo!!! I'm famous!
 
I'd like to help but I don't feel like Googling it for you.
|
|
|
|
|
PaulC1972 wrote: I'd like to help but I don't feel like Googling it for you.
Ah.... Fame is such a fickle thing indeed. I guess that's my 15 minutes over with.
|
|
|
|
|
Colin Angus Mackay wrote: Ah.... Fame is such a fickle thing indeed. I guess that's my 15 minutes over with.
No, you're still famous. The sig is on my laptop with CPHog and the message I just posted is in my faculty office using IE 6 which has an old sig. You'll see more of the other sig when I am using my laptop
I'd like to help but I don't feel like Googling it for you.
|
|
|
|
|
hello guys my main problem is how to copy schema. could you reply to that please
kesavan
|
|
|
|
|
hi,
anyone know how to search the date by "dd/mm/yyyy" in sql query!
my code is :
Select datamonth from datemonthtest where datamonth >= #" & strdatefrom & "# and datamonth <=#" & strdateto "#
and data type i set it to date/time!
The result is not efficient to select the month!
Example: when i select the date is from 01/10/2006 to date 30/10/2006 but all the previous month will be come out! 09/08/2006......
Please help!
|
|
|
|
|
Select datamonth from datemonthtest where datamonth
BETWEEN strdatefrom AND strdateto
only two letters away from being an asset
|
|
|
|
|
charleslau2855 wrote: anyone know how to search the date by "dd/mm/yyyy" in sql query!
select convert(varchar(10), getdate(),101)
"My advice to you is to get married. If you find a good wife, you will be happy; if not, you will become a philosopher." Socrates
|
|
|
|
|
albCode wrote: charleslau2855 wrote:
anyone know how to search the date by "dd/mm/yyyy" in sql query!
select convert(varchar(10), getdate(),101)
That does not "search". It formats.
The original question has a false lead in it by requesting a specific format. Date searches are format free because SQL Server does not store the date in a specific locale.
|
|
|
|
|
Use parameters then you don't need to worry about date formats and it reduces your worries about a SQL Injection attack. Also, if you are going to put a date into a SQL string it is better to use the ISO format which is yyyy-mm-dd becuase it is locale independent.
|
|
|
|
|
You have too many items in your GROUP BY clause. The GROUP BY clause should contain only the non-aggregate items that are also in the SELECT clause. If your GROUP BY clause contains more items then it will appear to be producing duplicates (although it isn't, it is just throwing away the information the distinguishes one row from the other).
|
|
|
|
|
Don't you think it is increadibly rude when someone asks a question, you then answer it and a little while later they delete the original question. That means there is now an orphaned answer to a question no one now knows. This really is NOT helpful to anyone searching the forum for the answer to a similar problem.
I really have to dispair sometimes - I really do. 
|
|
|
|
|
Colin Angus Mackay wrote: I really have to dispair sometimes - I really do.
Keep your chin up Colin...you do a fine job around here (IMO).
--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
|
|
|
|
|
Colin Angus Mackay wrote: you then answer it and a little while later they delete the original question.
That is why I make the attempt to put the question into my reply. Then they can delete it all they want but their question still sits there in my reply :->
You will see a delete button on each of your posts. Press it. - Colin Angus Mackay
|
|
|
|
|
PaulC1972 wrote: That is why I make the attempt to put the question into my reply. Then they can delete it all they want but their question still sits there in my reply
That's a good idea. Especially since it looks like Colin is either arguing with or insulting himself right now. 
Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
|
Chris Meech wrote: Especially since it looks like Colin is either arguing with or insulting himself right now.
The problem comes when I argue with myself to such an extent that it becomes violent. It's not easy to hit yourself in the face with a chair - I always manage to duck out of the way in time.
|
|
|
|
|
LOL.
Note to self. Not a good idea to be standing behind Colin when he's arguing with himself. Be aware of flying chairs.
Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
|
Is it possible to use a table in a foreign database via ODBC in a MS SQL Server 2005 SELECT query. I have data in a legacy database that I would like to use with data within SQL Server 2005.
For example, with MS Access you can "Get External Data" and link to tables in other database sources. I would like to do something similar in MS SQL Server
Thanks
Steve Jowett
|
|
|
|
|
You could always link your other database in as a Linked Server.
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.
|
|
|
|
|
Hi there,
I've got a Messages table which looks roughly like this:
SenderID (int)
RecipientID (int)
TimeStamp (datetime)
Body (varchar(512)
Now I'd like to do a select which gets me (RecipientID = 1) the first message (depending on the timestamp) of each sender. Example: Sender ID 5 has sent two messages, the recordset should contain the oldest message of this fella. Sender ID 6 has sent five messages, the recordset should contain the oldest message of this guy as well.
I thought this could be done by tying a DISTINCT to a column. I hoped I could do it somehow like this:
SELECT DISTINCT(SenderID), TimeStamp, Body FROM Messages WHERE RecipientID = 1 ORDER BY TimeStamp DESC
I was just guessing into the wild. Is there an easy way to do that?
Thanks in advance!
/matthias
I love deadlines. I like the whooshing sound they make as they fly by.
[Douglas Adams]
|
|
|
|
|
Try
SELECT M.SenderId, M.TimeStamp, M.Body FROM Messages AS M
WHERE M.TimeStamp = (SELECT MIN(M1.TimeStamp) FROM Messages AS M1 WHERE M1.SenderId = M.SenderId)
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
