|
|
Hello everybody.
Here's something is driving me crazy because I have the feeling it's a simple solution and I'm not getting there yet.
Can some one help me ?
Hee's the problem.
I have a table on SQL Server 2000 with the following records:
<br />
ID Date Flag Description<br />
B1E139CF-C5B8-4C6A-8951-197C0F2BB2AC 2005-09-20 18:00:00 0 Grupo 3<br />
C472A346-A855-4721-8364-71AAA2B0D4A2 2005-09-20 18:00:00 0 Grupo 3<br />
1ADAA758-C975-4C4D-AFFA-069CECDF7BF7 2005-09-21 00:00:00 0 Grupo 1<br />
I need to write a SELECT statement that return me one record for each description for any ID. Here's an example of the final output I need:
<br />
ID Date Flag Description<br />
B1E139CF-C5B8-4C6A-8951-197C0F2BB2AC 2005-09-20 18:00:00 0 Grupo 3<br />
1ADAA758-C975-4C4D-AFFA-069CECDF7BF7 2005-09-21 00:00:00 0 Grupo 1<br />
Is this simple ? Is here anyone that can help me ?
Thanks in advance for all replies
-- modified at 18:29 Tuesday 20th September, 2005
|
|
|
|
|
hi there,
try:
select id, date, flag, distinct description from "ur table"
I'm not sure it's result is correct or not 
<< >>
|
|
|
|
|
Thank you for your reply but it doesn't work.
I got an error message saying that I'm using the distinct keywork in an invalid way.
I think that DISTINCT can only be used just after the SELECT keyword.
|
|
|
|
|
yep DISTINCT must be after SELECT keyword as the select query in the previous post
<< >>
|
|
|
|
|
Sure you are correct and I already tried it without sucess. Since distinct applies to distinct records, eliminating all records with ALL fields with the same value. Since the field ID is always diferent I get all records from the table.
I want to make a distinct select by the description field and get one record for each description ignoring what value comes on the ID field.
|
|
|
|
|
Filipe Peixinho wrote:
I want to make a distinct select by the description field and get one record for each description ignoring what value comes on the ID field.
=> play with GROUP BY clause
<< >>
|
|
|
|
|
Thanks you but I probably I don't express myself in a good way.
When I say I want to ingnore the values of the ID I don't mean that I don't want the ID field on the query result. I mean that I want all fields (including the ID field) but I want one record for each description with any ID SQL chooses to return to me.
Is this possible ?
you mention to use the GROUP BY clause. I tired several ways without sucess and also read all documentation on SQL Books online about the group by clause and can't figure out how can group by help me.
Will you explain me better please what are you trying to say on your last post ?
|
|
|
|
|
try this again
select distinct Description, ID, Date, Flag
From "table"
<< >>
|
|
|
|
|
Already tried that and it doesn't work.
It seems sql server is applying the distinct clause to all record and not the description field.
|
|
|
|
|
Do something similar to this
CREATE TABLE #TEMP (
[role] [varbinary] (2048),
[name] [varchar] (51)
)
declare @currentrole varbinary(2048)
declare @lastrole varbinary(2048)
declare @name varchar(20)
declare usr_cusror CURSOR FOR
select roles, [name] from sysusers order by roles
OPEN usr_cusror
FETCH usr_cusror INTO @currentrole, @name
WHILE (@@FETCH_STATUS=0)
BEGIN
IF ( @lastrole <> @currentrole )
BEGIN
INSERT INTO #TEMP VALUES(@currentrole,@name)
END
SET @lastrole = @currentrole
FETCH usr_cusror INTO @currentrole, @name
END
CLOSE usr_cusror
DEALLOCATE usr_cusror
SELECT * from #TEMP
DROP TABLE #TEMP
|
|
|
|
|
In SQL Server (maybe in SQL language in general), if a field of a table has an index on it, and this field is searched with LIKE, its index won't be used if the argument to LIKE starts with the jolly char "%".
I'm trying to speed up the search and i'd like to know if someone here has some ideas.
The customer would accept a search for whole word, so currently the idea would be to create a separate search table, whose record would contain the fields of the primary key of the main table plus another field which would contain a single word from the main table; this field would have an index.
The search table would be populated by a trigger which would parse the content of the field of the main table and would insert one record into the search table for every parsed word from the main table.
Do anyone has other possible solutions to try, or even just some considerations ?
|
|
|
|
|
Fulltext search will do that and more if you are able to enable it.
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
|
Yes, i've just got the very same advice. At the moment i'm not able to enable it even because i didn't even know about it. Now i'll try to learn it. Thanks!
|
|
|
|
|
At http://www.windowsitpro.com/SQLServer/Article/ArticleID/8828/8828.html i see this sentence in the section "Disadvantages of Full-Text Search":
[i]7. You can only use a wildcard (asterisk) as a placeholder for suffixes; you can't use a wildcard at the beginning of a search string. (For example, to find all occurrences of "sailboat," you need type "sail*" instead of "*boat.")[/i]
If i understand that correctly, that makes Full-Text Search useless to me; however, at the moment that also makes Full-Text Search look to me like useless in absolute, because i can't see anymore what does it do more or better than normal search.
But since it's impossible that Full-Text Search doesn't do anything more or better that normal search, this means that i'll need to better understand the whole matter.
|
|
|
|
|
hi
i want to develope new application which is multilocational & on every transaction i want to transfer data to server which is located at other place
so how i could do this?
either by doing dataset or is there any other method for this
plz answer me this is vey urgent
|
|
|
|
|
You may want to look at web services
|
|
|
|
|
Try to do little home work on Distributed Transaction if you want to update into different database.
Sreejith Nair
[ My Articles ]
-- modified at 7:12 Tuesday 20th September, 2005
|
|
|
|
|
I have a big problem with MS.Access. Now, I have two databases (DB). The problem is that I want to add a record to the first DB and the result I expect that that record is exist in the second DB automatically. In addition, two DBs are on the two computers. Thanks...
|
|
|
|
|
Create a third db (database) with the tables for the two dbs in a single located server point. Use linked tables on client dbs versions to the link central version of the tables. You can now run multiple clients off the same back end db.
|
|
|
|
|
Maybe my question is not clearly. The problem is that I want to control (add, remove, delete, or update a record of a table) the remote database of MS.Access on another computer when I'm from a database of Access on my computer.
|
|
|
|
|
hi there,
vtalau wrote:
I have a big problem with MS.Access
=>what probs u r facing?
vtalau wrote:
I want to add a record to the first DB and the result I expect that that record is exist in the second DB automatically
=> whenever u insert a record to the first DB, also insert this to the second one.
<< >>
|
|
|
|
|
Try to do little home work on Distributed Transaction if you want to update into different database.
Sreejith Nair
[ My Articles ]
|
|
|
|
|
I think "Distributed Transaction" is not exist in MS.Access. How I control (add,remove,delete,or update a record of a table) the remote database of Access when I'm from a database of Access. Two databases are also on two computers.
|
|
|
|
|
Sorry that was a mistake.
If it is access you have to ensure that both database table got Inserted/Deleted/Updated.
Otherwise you have to upgrade your access to Sql Server or other.
Sreejith Nair
[ My Articles ]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
