|
If only you knew how many times a day I think about it...
Full-fledged Java/.NET lover, full-fledged PHP hater.
Full-fledged Google/Microsoft lover, full-fledged Apple hater.
Full-fledged Skype lover, full-fledged YM hater.
|
|
|
|
|
I have table A with col1, col2 & table B with col3, col4.
I would like to write a query like
select a.* from A join B on A.col1=B.col3 and A.col2 like col4 + '%'
Is it possible in SQL 2005?
is there a concat function in SQL 2008 or 2005. I have seen it in 2012.
Pls help me.
Thanks in advance
|
|
|
|
|
Anything wrong with google today?
Have a look here[^].
String concatenation has been around as long as SQL has. But different dialects have different syntax.
Oracle uses '||', Sql Server uses '+', most implementations can use 'concat(string1, string2)' but not SQL Server AFAIK. Could be wrong about that though.
|
|
|
|
|
Jörgen Andersson wrote: Anything wrong with google today?
Same impression here, I'm starting to believe I'm the only person with Google access today
Full-fledged Java/.NET lover, full-fledged PHP hater.
Full-fledged Google/Microsoft lover, full-fledged Apple hater.
Full-fledged Skype lover, full-fledged YM hater.
|
|
|
|
|
Hi,
In MySQL, how can I format a number like this
1000000001
where the first 1 is the weekday and the last one is my LAST_INSERT_ID and I am adding just zeroos to make it always 10 digits?
|
|
|
|
|
How about this:
SELECT (DAYOFWEEK(NOW()) * 1E9) + @@LAST_INSERT_ID
|
|
|
|
|
Hi,
I want to update the column TID with the top most value of TID as long as MID is the same number.
I want to do this (table after update)
PID SID TID MID RID
3 0 0 4 1
3 NULL 0 4 2
3 NULL 0 4 3
3 NULL 0 4 4
3 NULL 0 4 5
3 NULL 0 4 6
3 NULL 0 4 7
3 4 4 8 1
3 NULL 4 8 2
3 NULL 4 8 3
3 NULL 4 8 4
Below is the actual table before update.
PID SID TID MID RID
3 0 0 4 1
3 NULL NULL 4 2
3 NULL NULL 4 3
3 NULL NULL 4 4
3 NULL NULL 4 5
3 NULL NULL 4 6
3 NULL NULL 4 7
3 4 4 8 1
3 NULL NULL 8 2
3 NULL NULL 8 3
3 NULL NULL 8 4
Any suggestion/solutions deeply appreciated.
Thanks
|
|
|
|
|
Assuming your DB is SQL Server, you can try this (just out of mind, not tried though)
UPDATE MyTable A SET A.TID = (SELECT MAX(B.TID) FROM MyTable B WHERE A.MID = B.MID)
|
|
|
|
|
Thanks for your help.
I have solved it by creating a view of all 'Not NULL' (top most) rows . Then created a stored proc to loop through the view and update the TID. A long procedure though but it did work.
Cheers !
|
|
|
|
|
I think you want to do something like this ...
update table A
set tid =
(select max(tid)
from TableA
Group By mid)
Check these for references:
http://msdn.microsoft.com/en-us/library/ms177523.aspx
http://data.bangtech.com/sql/sql_update_with_correlated_subquery.htm
|
|
|
|
|
Hi thanks for your solution and links,
But your query returned an error.
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
|
|
|
|
|
That's because he forgot a small but important condition.
Try this:
update table A as a1
set tid =
(select max(tid)
from TableA as A2
Where A1.mid = A2.mid
Group By mid)
|
|
|
|
|
Thanks for the refinement to my SQL. Sometimes when you don't have the actual data to test with, you forget some stuff.
|
|
|
|
|
David Mujica wrote: Sometimes when you don't have the actual data to test with, you forget some
stuff.
Tell me about it. That's almost something I can put in my CV.
|
|
|
|
|
Hi everybody,
I have some numbers. Numbers are
(
0
234,9
-27
0
-55,9
0
0
-59
0
100,72
-26
0
-25,72
0
0
0
-82
0
0
0
0
0
0
-134)
I want to this.
SELECT SUM(FIELDX) FROM TBLX
but result is returning (-2,8421709430404007E-14)
actually resutl is 0 (Zero)
How can i solve this problem?
Thanks.
Başak Öner
|
|
|
|
|
Basak Oner wrote: actually resutl is 0 (Zero)
Not if you're a computer, then it's "almost 0". Try rounding the result.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
sorry I don't understand your message.
I need your detailed explanation.
Thanks.
|
|
|
|
|
Basak Oner wrote: sorry I don't understand your message.
Try the ROUND function. This is due to how computers work and how they calculate. You know what a fractional number is, but they do not exist within a computer.
Basak Oner wrote: I need your detailed explanation.
You're not in school anymore. There's an explanation here[^], good luck.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks for your free time.
|
|
|
|
|
You actually *read* it?
Ehr.. you're welcome, and yes, well done
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
You can try to change the datatype of the field.
Check if SELECT SUM(cast(FIELDX as numeric(5,2)) FROM TBLX works.
In your real data you naturally need to adjust the precision and scale of the numeric field to fitting values.
|
|
|
|
|
Hi Experts
I have a query which needs to return the second in Day, Hour, Minute, Second format.
The below code works fine when its less than a day, but does not work, when the value in second is greater than a day
PRINT Convert(VarChar, DateAdd(S, 86400, 0), 108)
86400 is exactly a day and it returns 00:00:00
Can someone modify it and show me the result something like this
1:00:00:00.
Thanks
|
|
|
|
|
Take a look at MODULO from BOL!
SELECT 38 / 5 AS Integer, 38 % 5 AS Remainder ;<br />
Use a case statement to detect if it is over 86399 and apply the appropriate formula. If you use it in multiple places then create your own UDF.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Try the option, CONVERT(VARCHAR(10),'fieldname',112)
|
|
|
|
|
Try replying to the op next time, he will not get notification when you reply to me!
Never underestimate the power of human stupidity
RAH
|
|
|
|