|
I want to do a JOIN on two tables, and return all the values from that JOIN where the SS.TIME_RECCEIVED is the MAX value. I have got the below SQL so far, but this only returns the two columns, i need them all. I tried doing SS.* but i a get
ORA-00979: not a GROUP BY expression<br />
SELECT SS.SERVICE, MAX(SS.TIME_RECEIVED)
FROM ISS_BSM_SVCSTATES_Table SS
INNER JOIN ISS_BSM_GeoLocation_Table GEO
ON SS.SERVICE = GEO.SERVICE_NAME
GROUP BY SS.SERVICE
ORDER BY SS.SERVICE ASC
Any ideas?
On a side note, i've tried the below, but keep getting ORA-01722: invalid number , but if a just do a SELECT MAX() on its own, it does not complain about invalid number.
SELECT *
FROM ISS_BSM_SVCSTATES_Table SS
INNER JOIN ISS_BSM_GeoLocation_Table GEO
ON SS.SERVICE = GEO.SERVICE_NAME
WHERE SS.SERVICE = (SELECT MAX(SS.TIME_RECEIVED) FROM ISS_BSM_SVCSTATES_Table WHERE SS.SERVICE = GEO.SERVICE_NAME)
Regards,
Gareth.
(FKA gareth111)
modified on Wednesday, June 10, 2009 9:12 AM
|
|
|
|
|
Problem solved:
select * from iss_bsm_svcstates_table ss, iss_bsm_geolocation_table geo
where ss.service = geo.service_name and ss.time_received in
(select max(ss1.time_received) from iss_bsm_svcstates_table ss1 group by ss1.service)
Regards,
Gareth.
(FKA gareth111)
|
|
|
|
|
Still another way.
select * from iss_bsm_svcstates_table ss, iss_bsm_geolocation_table geowhere ss.service = geo.service_name and ss.time_received in
(select top 1 ss1.time_received from iss_bsm_svcstates_table ss1 where ss1.time_received = ss.time_received order by ss1.service desc)
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hi Experts
I am also ask this Question many time but not getting Proper Answer.
What is the Use Of database Certificate(in case of Security).
how to create and use it to in Database Security.
pls Help Me
Thank u
Dinesh Sharma
|
|
|
|
|
Dear All,
I have sql query which is
create proc sp_detail
(
@p varchar(max)
)
as
select * from tblName where code in (@p)
go
and i am calling it like following
declare @codes varchar(max)
select @codes=cast(quotename('28-2801','''')+','+quotename('28-2802','''') as varchar(max))
print @codes
exec [sp_getCommunityDetail] @codes
this doesent work, while i have records in table.
i want to do like this
select * from tblName where code in ('28-2801','28-2802',....)
how can i acheieve it in procedure and how do i pass such thing to procedure?
Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
Good Morning Abdul
Stop Stop Stop Stop Stop
You created a SP called sp_detail
and you said you calling it in the Seconds statements, but there is no place in the second statement you are using the created sp.
i don't see the created Sp has anything to do with
declare @codes varchar(max)
select @codes=cast(quotename('28-2801','''')+','+quotename('28-2802','''') as varchar(max))
print @codes
exec [sp_getCommunityDetail] @codes
please before you post make sure you are posting the right things, it seems you posted the wrong Sp
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
oh i am really sorry, u r right, but the name of procedure was written wrong in here, that is infact sp_detail, i am really sorry again, would u please help me?
Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
Good Morning Abdul
If you have create Your Sp like this
create proc sp_detail
(
@p varchar(max)
)
as
select * from tblName where code in (@p)
go
if you want to execute it , you have to do the Following
declare @codes varchar(max)
select @codes=cast(quotename('28-2801','''')+','+quotename('28-2802','''') as varchar(max))
exec [sp_detail] @codes
Now to understand the Second problem you are Having, if you say it doesnt work , do you get any sort of Error or you get a logical error , please give more info.
Vuyiswa Maseko
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
i am getting no record, while there is record in table
if i use the select statement instead of procedure i can get the result
select * from tblName where code in ('28-2801','28-2802',....)
return 2 rows, while procedure returns zero (0) rows.
Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
It won't work. You need to use dynamic sql to do this, sql server does not evaluate the variable. ALternatively, split the variable into a temp table of some sort.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
yeah, i wrote a function which it was splitting the string based on comma, and then inserting those into the temp table. the temp table had my code in records, but still the procedure didnt worked that way too.
i think there is something tricky in such cases, i am searching to find that case. i need your help too.
Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
Well, its a fairly simple join.
declare @Split TABLE (.....)
insert into @Split
select .. from your_split_function
select * from table t1
join @split s on s.code = t1.code
if it still fails to return records make sure you are trimming spaces from the values in your split function
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
thank dude, this method is very cool, trimming mostly solved my issue
Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
Please to have helped
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Hi,
I want to protect my database with ID & Password.
How it will be done.
|
|
|
|
|
Sometimes its difficult to answer questions like this in the Forums , because people don't give us enough information. We have to help them and think what they are trying to achieve. let me tell you what came into my mind when i saw your question.
Because if you are having an SQL Database is Hosted into the SQl Server and that Server is Protected by a sa password or you can add more users or restrict other users from accessing your database from the permission of your Database. So you see , you did not give us enough information.
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
i dont want my client will access my database in server.
now tel me is there any way to avoid this?
|
|
|
|
|
Its Simple, Dont give the Password to the Server, if they know it change it
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
You're not the dbowner?
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
Hi,
Sir, I m using SQL SERVER 2005 in Windows authentication mode. Anybody on my PC can access the server by using same authentication. That means they can access their own database but they also can access my own too & I don't want to have access to them.
I want to secure my particular on ly.
I think it is more clesr now.
Please help.
|
|
|
|
|
Now in that case , Change the Authentication and use SQL Authentication. This means you have to give up Windows Authentication and have a spacial credentials for your Database Remove your Windows username from the Permissions of the Database and Add a new username that can only be used by you, then in that case you and only you can access the DB. remember the Windows Authentication does not even ask you for a password as long as you have the correct username the user can enter and that is not advisable in a secure environment.
Hope it helps
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Many Time I am Also Suffer From this Problem but at a long time i find a solution
about this problem's.
First you Have To Change Window Authentication mode to mix mode authentication.
use Done this by Done From Inside SQL Server Object Explore\Properties\Security\Server Authentication Change to mix mode Authentication
or
by using Query run in Window Authentication mode
1)EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
2)Alter login sa enable
3)Alter login sa with password ='[abc]' --any Password
4)EXEC sp_revokelogin 'BUILTIN\Administrators'
5)Restart you'r SQL Server Service
and Batch File in C#.NEt is
int iChar = 34;
char aChar = (char)iChar;
StreamWriter sStr = new StreamWriter(GetWorkingDir + "//SQLSEREVR.Bat");
sStr.WriteLine("@echo off ");
sStr.WriteLine(@"CD\");
sStr.WriteLine(@"osql -E -S .\sqlexpress -Q "+aChar.ToString()+"Alter login sa enable"+aChar.ToString()+"");
sStr.WriteLine(@"osql -E -S .\sqlexpress -Q " + aChar.ToString() + "Alter login sa with password ='[abc]'" + aChar.ToString() + "");
sStr.WriteLine("Net stop " + aChar.ToString() + "SQL Server (SQLEXPRESS)" + aChar.ToString() + "");
sStr.WriteLine("Net start " + aChar.ToString() + "SQL Server (SQLEXPRESS)" + aChar.ToString() + "");
sStr.WriteLine(@"osql -E -S .\sqlexpress -Q " + aChar.ToString() + @"EXEC sp_revokelogin 'BUILTIN\Administrators'" + aChar.ToString() + "");
sStr.WriteLine("Net stop " + aChar.ToString() + "SQL Server (SQLEXPRESS)" + aChar.ToString() + "");
sStr.WriteLine("Net start " + aChar.ToString() + "SQL Server (SQLEXPRESS)" + aChar.ToString() + "");
string sBatchFileName = GetWorkingDir + "//SQLSEREVR.Bat";
|
|
|
|
|
Your DB? Is this DB on a server you do not own/admin? Have you asked the srvadmin?
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
Hi Experts
I Have A SQL Server 2005 Database i want that no body or person can copy my
.mdf file. i mean to say that no body can copy my .mdf file at attach at different
location for security issue. is their any way to protect to copy
or restrict the attachment of database at different location
Thank u
Dinesh Sharma
|
|
|
|
|
Yes. Upgrade to SQL 2008.
I kid you not.
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|