|
Hi all,
Select distinct(Mobile), ROW_NUMBER() OVER(PARTITION BY mobile ORDER BY dateofcall DESC ) AS 'RowNumber' from Call
having RowNumber=1
Error Invalid coulmn RowNumber
Can any one advice please?
Thank you
|
|
|
|
|
The columname-alias "RowNumber" can only be used in a ORDER BY clause. Try this one;
SELECT TOP 1
distinct(Mobile), ROW_NUMBER() OVER(PARTITION BY mobile ORDER BY dateofcall DESC ) AS 'RowNumber' from Call
I are troll 
|
|
|
|
|
top 1 returns one record.what i want is to select only those that has a first entry to my database.RowNumber=1
|
|
|
|
|
You can change the sorting by changing the ORDER BY clause, so that the first row is the first entry
I are troll
|
|
|
|
|
I cannot connect to a remote sql server 2005 database from my computer at work.
I'm using sql server 2005 express edition.
My work computer is inside a local network (work network) and obtains its ip address automatically (so it has no real ip).
I can connect successfully to the remote database through my home computer and through a server at work and both have real ips.
I've checked everything (firewall is disabled and all sql server services are working).
I think there is something blocking this connection related to the work network but I don't know what it is exactly.
I contacted the IT department but they don't know what's wrong.
I read somewhere that sql server uses port 1433. Could this be the cause??
Can someone help me out please?
|
|
|
|
|
How are you attempting to connect?
Are you using a name or an IP,Port combo?
If you are using a name to connect, e.g MainServer, try using a IP,Port combination instead, e.g 192.168.1.1,1433
|
|
|
|
|
well, yes I'm connecting using a name,port combo.
I don't really know the IP address.
How can I get the IP address?
Here's the error that sql server displays:
TITLE: Connect to Server
------------------------------
Cannot connect to name,port.
------------------------------
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (Microsoft SQL Server, Error: 10060)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=10060&LinkId=20476
|
|
|
|
|
Execute this command in a normal query window on that server:
xp_readerrorlog
Search in the result for both the IP and the Port.
Can you access any other resources on the server? E.g, browse is, or even Ping it?
|
|
|
|
|
it displays no ip address, it doesn't even show the error that was displayed.
|
|
|
|
|
Obviously you should execute that command from a PC with a working connection to the server.
|
|
|
|
|
When I ping it, it says Request timed out.
It actually displayed the ip address.
|
|
|
|
|
Sounds like you have Network/Domain problems, not SQL problems.
Once you get the those problems sorted out (Ask I.T) which is beyond the scope of this forum, you may be able to connect.
Or if you still can't connect at that stage, come ask here again.
|
|
|
|
|
it's ok now, the IT fixed it  )
|
|
|
|
|
Also, do I need a real ip for my computer at work or what?
|
|
|
|
|
|
the IT fixed the problem, it was something related to the server.
Thanks a lot for your help
|
|
|
|
|
I cannot connect to a sql server 2005 database from my computer at work.
I'm using sql server 2005 express edition.
My work computer is inside a local network (work network) and obtains its ip address automatically (so it has no real ip).
I can connect successfully to the remote database through my home computer and through a server at work and both have real ips.
I've checked everything (firewall is disabled and all sql server services are working).
I think there is something blocking this connection related to the work network but I don't know what it is exactly.
I contacted the IT department but they don't know what's wrong.
I read somewhere that sql server uses port 1433. Could this be the cause??
Can someone help me out please?
|
|
|
|
|
I have a trigger that I want to fire if:
1. A certain Column was Updated ... OR
2. The record was deleted
The code that should run in both respects is the same block of code.
What I have at the moment is:
ALTER TRIGGER [trgFlag] ON [dbo].[tblStockItems]
AFTER DELETE, UPDATE
AS
IF UPDATE(Result1) OR UPDATE(Result2)
BEGIN
<All my code to execute comes here.....>
END
But... what I need is something like this:
ALTER TRIGGER [trgFlag] ON [dbo].[tblStockItems]
AFTER DELETE, UPDATE
AS
IF UPDATE(Result1) OR UPDATE(Result2) OR DELETED
BEGIN
<All my code to execute comes here.....>
END
Kind Regards
modified on Monday, May 4, 2009 7:59 AM
|
|
|
|
|
Can you not put <All my code to execute comes here.....> in a SP and then have separate triggers for update and delete which call the SP?
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
See, the thing is, I want to make it column specific, but I didn't know how to to test to see when a record has been updated or deleted, since an update action fires a delete, then an insert.
So in the end I got it working by doing the following:
IF Update(col1) OR Update(Col2) OR NOT EXISTS(SELECT * FROM INSERTED)
This works because, during an update, DELETED and INSERTED has data, but during a delete, the INSERTED returns NULL.
Problem solved!
Thanx for your input Henry! - there I go answering my own questions again, I'm a little CP-trigger happy! 
|
|
|
|
|
evilnoodle wrote: there I go answering my own questions again
If only all posters would do this.
Excellent sleuthing, I've bookmarked this. It's bound to be useful. 
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Dear All,
I have a query that has some parameters:
(
SELECT DISTINCT
T.TICKET_NUMBER AS TicketNum, A.DESCRIPTION AS Action, O.SYMBOL_CODE AS Symbol, T.TRADE_PRICE AS Price, T.VOLUME_TRADED AS FillVol,
T.EXTENDED_PRICE AS TotalValue, T.SUBMITTED_TIME AS ActTime, T.SUBMITTED_DATE AS TransDate
FROM TSDETL AS T INNER JOIN
TSORDR AS O ON O.SUBMITTED_DATE = T.SUBMITTED_DATE AND O.TICKET_NUMBER = T.TICKET_NUMBER INNER JOIN
TSORDA AS A ON O.ORDER_ACTION = A.ACTION_CODE
WHERE (T.SUBMITTED_DATE = @SUBMIT_DATE) AND (T.VOLUME_TRADED >= @Volume) AND (T.TICKET_NUMBER IS NOT NULL) AND (O.SYMBOL_CODE= @Symbol)
ORDER BY ActTime DESC
)
I passed the three parameters (@SUBMIT_DATE, @Volume, @Symbol) successfully, but I want to read the operator (>= or <=) as a parameter...I have a combo box with <= and >=, and I want to pass the value of this combo into the query.
How can I do this???
this is because the operator may be >= or <=, and I don't want to write two queries.
Kind Regards
OBarahmeh
|
|
|
|
|
Answered in the C# forum, please don't cross-post.
I are troll
|
|
|
|
|
Would you like an ugly option? How about:
CREATE FUNCTION DoFunction
(
@Op1 BIGINT
,
@Oper NVARCHAR(2)
,
@Op2 BIGINT
)
RETURNS BIT
AS
BEGIN
IF ( @Oper = '>=' AND @Op1 >= @Op2 ) RETURN 1
ELSE IF ( @Oper = '<=' AND @Op1 <= @Op2 ) RETURN 1
-- More
RETURN 0
END
select * from test where dbo.dofunction ( VOLUME_TRADED , '<=' , @Volume )=1
Otherwise, I might suggest:
SELECT * FROM test WHERE CASE WHEN @Oper='<=' AND VOLUME_TRADED <= @Volume THEN 1 WHEN @Oper='>=' AND VOLUME_TRADED >= @Volume THEN 1 ELSE 0 END = 1
|
|
|
|
|
Hi Friends,
I need an sql query help.i need to implement distinct staetement in my select query.I need the Assets.AsstId should be distinct in query.i attach the query below .please help me i need its urgently.The bolded value should be distinct.
SELECT aspnet_Users.UserName, AssetsSettingsTypes.AssetSettingsName, CONVERT(varchar(MAX), Assets.AssetShortDescrition)
AS AssetShortDescrition,Assets.AssetId, '<img src="' + CONVERT(varchar(MAX), Images.ThumbUrl) + '" width="100" height="100"/>' AS Image,
dbo.GetFriendlyDateTimeValue(UserAssets.CreationTime) AS TimeDiff, UserAssets.CreationTime as CreationTime,
'profileUser/Profileview.aspx?' + CONVERT(varchar(MAX), aspnet_Users.UserName) AS UrlProfile,
'profileUser/userPhotos.aspx?AssetId=' + CONVERT(varchar(MAX), UserAssets.AssetId)
+ '&UserName=' + aspnet_Users.UserName AS NavUrl, 'forntend/Commentview.aspx?' + CONVERT(varchar(MAX), UserAssets.AssetId)
+ '&' + CONVERT(varchar(MAX), UserAssets.UserId) AS UrlComment, '(' + CONVERT(varchar(MAX),
(SELECT COUNT(*) FROM UserComments WHERE (UserAssetId = UserAssets.UserAssetId)))+ ')' AS CommentCount
FROM AssetsSettingsData INNER JOIN
Assets ON AssetsSettingsData.AssetId = Assets.AssetId INNER JOIN
AssetsSettingsTypes ON AssetsSettingsData.AssetSettingsTypeId = AssetsSettingsTypes.AssetSettingsTypeId INNER JOIN
Images ON Assets.AssetId = Images.AssetId INNER JOIN
UserAssets ON Assets.AssetId = UserAssets.AssetId INNER JOIN
aspnet_Users ON UserAssets.UserId = aspnet_Users.UserId
WHERE (aspnet_Users.UserId = '9D4C2631-BB10-47C0-A4EE-5B1E92FE1AB9')
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
