Database

Re: how can i group same item to 1??

5-Dec-06 23:41

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.

Colin Angus Mackay5-Dec-06 23:47

5-Dec-06 23:47

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 BE SO RUDE

Colin Angus Mackay6-Dec-06 22:53

6-Dec-06 22:53

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. Sigh | :sigh:

Eric Dahlvang7-Dec-06 3:21

Eric Dahlvang

7-Dec-06 3:21

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

Paul Conrad7-Dec-06 4:16

Paul Conrad

7-Dec-06 4:16

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

Chris Meech7-Dec-06 9:01

Chris Meech

7-Dec-06 9:01

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. Smile | :)

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]

Colin Angus Mackay7-Dec-06 9:10

7-Dec-06 9:10

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.

Using foreign tables in select queries

Chris Meech7-Dec-06 10:07

Chris Meech

7-Dec-06 10:07

LOL.

Note to self. Not a good idea to be standing behind Colin when he's arguing with himself. Be aware of flying chairs. Smile | :)

Steven J Jowett5-Dec-06 10:51

Steven J Jowett

5-Dec-06 10:51

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

Re: Using foreign tables in select queries

Pete O'Hanlon6-Dec-06 1:39

Pete O'Hanlon

6-Dec-06 1:39

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.

Select Distinct Question

matthias s.5-Dec-06 0:03

matthias s.

5-Dec-06 0:03

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]

Paddy Boyd5-Dec-06 1:35

Paddy Boyd

5-Dec-06 1:35

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)

Pete O'Hanlon5-Dec-06 1:36

Pete O'Hanlon

5-Dec-06 1:36

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).

Private_Void5-Dec-06 5:33

Private_Void

5-Dec-06 5:33

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

Private_Void5-Dec-06 5:34

Private_Void

5-Dec-06 5:34

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