|
Hi all,
I want to get a multicolumn selection but with the behavior of the DISTINCT cmd only on the first column, something like that
SELECT DISTINCT order_id, date FROM Orders WHERE .....
but the distinction should only refer on the "order_id" and not even on the "date" .
How does the sql-cmd looks like ?
thanks in advance
Frank
|
|
|
|
|
Can you give us a sample of what data exists in the table, and then what data you want after the query has been run? Because what you have doesn't seem to make sense...if your data looks like this:
order_id - date
1 - 1/1/2008
2 - 1/1/2008
2 - 1/2/2008
then which date are you going to want to display? If you distinct on only the order_id (which isn't really possible using a distinct, you would have to use a group by) then the SQL doesn't know which date you want...(which is why it's not possible using Distinct)....so please supply us some more info so we can help.
|
|
|
|
|
Hi Kschuler,
my problem is that I have to show the date and I have to show the order_id but only once.
I know, in most cases it doesn't makes sense. But for my application it doesn't matters which date has to be displayed when double entries exists. The central point is that all entries have to show a date multiple entries or not.
This is the crux.
Now I think there is no SQL solution for that problem.
Now I filter the recordset.
Thank you for your help
|
|
|
|
|
You can accomplish it with something like this:
SELECT order_id, MAX(date) FROM tablename GROUP BY order_id
The idea is that you will return one order_id and the largest date with that order_id. I know it would work for a number, I'm just not sure how a date reacts to the MAX() function...you could also use MIN if you want the smallest date...and if you did this
SELECT order_id, MAX(date), COUNT(*) FROM tablename GROUP BY order_id
It would also display how many dates exist for that order id.
So maybe you need to do some research on GROUP BY and how it affects date datatypes.
Hope this helps.
|
|
|
|
|
Great Kschuler,
it works !!!! thanks
It works because you are using agregat functions as a second and/or third parameter after the select.
That helps for the future.
Frank
|
|
|
|
|
This doesn't make sense. Either all of the data is distinct or none of it is. If you want a single order_id, then you can only retrieve one date otherwise this order will span multiple lines.
|
|
|
|
|
Hi all
I have the following the statements
SET @TempMenuId= 'SELECT @res1 = coalesce(@res1 + ',', '') + CAST([Tb001_Id] AS Varchar(8)) FROM [admin].[cm_tb_005] WHERE' + @Role_Idv +'=1'
EXEC @TempMenuId
Iam Passing @Role_Idv dynamicall.But its showing error message like this
The name 'SELECT @res1 = coalesce(@res1 + ',', ') + CAST([Tb' is not a valid identifier.
Please help me on this.
Regard's
Veeresh
i want to join this group
|
|
|
|
|
this is working !
DECLARE @IDNAL VARCHAR(36)
DECLARE @ID_STAVKE VARCHAR(36)
DECLARE @IDDOC VARCHAR(36)
DECLARE @IDVL VARCHAR(36)
DECLARE @IDLOK VARCHAR(36)
DECLARE @NEVALJA CHAR(1)
DECLARE @RECENICA AS VARCHAR(5000)
DECLARE @LOCAL_KONTO AS VARCHAR(36)
DECLARE @ID_FORMULE AS VARCHAR(36)
DECLARE @FORMULA AS VARCHAR(500)
SET @MySTRING='
UPDATE #A SET
#A.col001=#A.col001+COALESCE((SELECT ('+@FORMULA+') FROM #LOCAL_STAVKE
WHERE #LOCAL_STAVKE.VD='''+@IDDOC+'''
AND #LOCAL_STAVKE.KONTO= CASE '''+@LOCAL_KONTO+'''
WHEN '''' THEN #LOCAL_STAVKE.KONTO ELSE '''+@LOCAL_KONTO+''' END
AND #LOCAL_STAVKE.ID=#A.IDNALOGA ),0)'
EXECUTE(@MySTRING)
|
|
|
|
|
|
If your using SQL Server 2005 you could use the Day() / Month() / Year() - Methods to get the specified part of the date. I don't know exactly whether these functions are available in SQL Server 2000, too.
|
|
|
|
|
thanks
You get the best out of others when you give the best of yourself.
|
|
|
|
|
Don't delete your original message. It is rude!
|
|
|
|
|
Is there a clear cut guide, tool, etc... for doing a migration from MySQL to SQL Server 2000 or 2005?
I have to migrate a small DB and am looking for an easy way to do it if such a thing exists.
|
|
|
|
|
Hi,
Our website provide users with a forum where they can enter messages. So, typically, a user would enter a message, click submit, and their message would be displayed on the forum.
The db that stores these msgs uses a varchar field. Now, we're adding a facility to convert any urls that are entered to links by adding 'a href' tags, so that when users see messages with urls, those urls appear as links, and they can click on them. The problem is that if a user enters a message of 8K chars, then we add 'a href' tags to any urls they have, that makes the message length > 8K, preventing us from storing it in our db. (Please note that we do need to add HTML tags for other reasons as well, not just to convert urls to links.)
We thought about adding 'a href' tags during pre-render, thus avoiding storing html in the db, but found that doing such string manipulations, while a user is waiting to see their post displayed on the forum, was expensive. Timing tests revealed that the time to render such pages more than doubled.
I have solved such problems previously by adding an extra field in the db to store any overflow data, that is, data that appears after the first 8K chars.
However, I learnt of sql server 2005's varchar(max) datatype, and 'am wondering if the best way to solve this problem would be to replace the varchar field with varchar(max).
Thanks in advance for any advice.
Shefali
|
|
|
|
|
shefali_sinha wrote: We thought about adding 'a href' tags during pre-render, thus avoiding storing html in the db, but found that doing such string manipulations, while a user is waiting to see their post displayed on the forum, was expensive. Timing tests revealed that the time to render such pages more than doubled.
To be honest, that sounds odd. If you were using a regular expression to detect URIs then you might want to switch to a cheaper option, or consider using a precompiled regex.
shefali_sinha wrote: However, I learnt of sql server 2005's varchar(max) datatype, and 'am wondering if the best way to solve this problem would be to replace the varchar field with varchar(max).
Yes, that would be a solution.
Be aware that memo-style fields are stored differently (physically) to standard fields. I doubt this will make a difference to a typical forum database though.
|
|
|
|
|
Thanks a lot.
Haven't had a chance to look into pre-compiles regex, but will look into it.
I simply converted a varchar(8000) field to varchar(max) and another ntext field to varchar(max), the latter conversion was for the sake of better perfromance of a varchar(max) vs. ntext, and my world stayed perfect. Testing reveled no problems. To do the conversion in sql server 2005, I used the statement:
alter table alter column <column name=""> varchar(max).
Just thought I should document this here in case it helps someone else.
Shefali
|
|
|
|
|
ntext -> nvarchar(max). Otherwise you'll blow away any unicode data....
|
|
|
|
|
Hi,
I have a sql script that returns the value of one query to a table variable (Table A). I then compare this table variable to a physical table (Table B) in my database to retrieve all values in B that are not in A. I am able to see the results via the query analyzer but would like to be able to store these results in a view (for reporting purposes). Unfortunately, I am unable to Create the View (as it must be the first statement in the batch). Secondly, I am using variables that can be only used in the scope of the sql statement that it was created (which means that I cannot reference a declared table variable within scope of a newly created view statement nor use views w/table variables or temp tables). Can anyone recommend a way to store the returned data? Thanks in advance to all responses.
|
|
|
|
|
It sounds like you'll need another table in your database to store the results. Include an INSERT statement with your results to the new table.
|
|
|
|
|
SQL 2000
System.Data.SqlClient.SqlException: Warning Fatal error 3624
when executing fill dataadapter from sql data table
Please help me to solve the problem
Thanks
|
|
|
|
|
alexrad wrote: SQL 2000
System.Data.SqlClient.SqlException: Warning Fatal error 3624
when executing fill dataadapter from sql data table
Please help me to solve the problem
How can anyone help you if you don't provide your code? The people on this forum aren't psychic.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Ofcourse,
but it is every time when i call storedprocedure on that same table,
no metter what query. Eny of them on that table doesnt work.
|
|
|
|
|
Since you didn't get the point of my previous reply, let me make it simple for you.
No information = no help
If you don't provide enough information to let someone understand what the problem is, your question will remain unanswered. It's up to you.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Error 3624 is a SQL Server internal, unexpected error. Usually there's a note of the SQL Server source file and line number that the assertion occurred at.
I would start by making sure you're running the latest service pack on the server (SP4) and the newest version of MDAC on the client (2.8 SP1, included in Windows XP SP2 and Windows Server 2003 SP1, if I recall correctly). If the problem doesn't go away you'll have to search for the solution at support.microsoft.com[^], and if no solution exists you'll have to call Microsoft support.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Thanks, i was thinking the same, but enother view of the problem is very usefull. I don't have sp4 on that server, sp2 is ok. I will try your way !
Thanks again !
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
