query problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

query problem

Author

Topic

flion
Starting Member

8 Posts

Posted - 2010-05-06 : 06:46:39

Hi guys,

I've got a problem to create my tsql query.

The situation is like this:

I've got a query:

SELECT db_server_name, db_server_instance, db_server_domain, db_sql_name, db_server_prio, db_type_name, db_server_lastrun, db_server_sdm, db_server_comment, db_server_tech, db_server_io
db_server_version, db_server_laststatus
from db_type,db_server join db_sql on db_server.db_server_id=db_sql.db_server_id where db_server.db_server_activ=1 and db_server.db_type_id=db_type.db_type_id order by db_server_name,db_sql_name

That query gives this result:

The DB_SQL_ID column needs to be replaced with DB_ORASPACE_SIZE (look below), there I'm stuck.

The DB_ORASPACE_SIZE column is a field in the db_ora_space table. The DB_ORASPACE_SIZE contains a list of values, while I only need the most recent.

select * from db_ora_space where db_oraspace_serverid=322

You can link the DB_SQL and the DB_ORASPACE_SIZE by DB_SQL_ID and DB_ORASPACE_SERVERID. Like you can see in the pictures. DB_SQL_ID = 322 (second record at the first picture).

Is there anyone who can fix this? That I have the latest size at each record? I've been trying and trying without any result.

Many thanks in advance!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-05-06 : 07:32:52

You can use ROW_NUMBER() function in a derived table to get only one record per partition.

N 56°04'39.26"
E 12°55'05.63"

flion
Starting Member

8 Posts

Posted - 2010-05-06 : 07:43:24

Could you please implement that in the query? That's a little to difficult for me. I'm a newbie in sql.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-06 : 13:44:06

[code]SELECT db_server_name, db_server_instance, db_server_domain, db_sql_name, db_server_prio, db_type_name, db_server_lastrun, db_server_sdm, db_server_comment, db_server_tech, db_server_io
db_server_version, db_server_laststatus,
from db_type,db_server join db_sql
on db_server.db_server_id=db_sql.db_server_id
join(select row_number() over (partition by DB_ORASPACE_SERVERID ORDER BY DB_ORASPACE_TIMESTAMP DESC) AS Seq,* from db_ora_space)tmp
om tmp.DB_ORASPACE_SERVERID = DB_SQL_ID
and Seq=1
where db_server.db_server_activ=1
and db_server.db_type_id=db_type.db_type_id
order by db_server_name,db_sql_name
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

flion
Starting Member

8 Posts

Posted - 2010-05-07 : 05:28:05

Thank you very much visakh16!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources