Database

der2

select sa1,sa2,count(sa1) from EXP_REL_CLSH_CONT
group by sa1,sa2
having count(sa1)> 1
order by sa1,sa2

SELECT ACTV  as ACTV_ID--, count([VENU]) as , sum([stud])
into #temp
FROM [dbo].SOL_ACTV_VENU
group by ACTV
having count(venu) > 1
order by sum(stud) 

SET ROWCOUNT 1
DELETE EXP_REL_CLSH_CONT 
from EXP_REL_CLSH_CONT C
INNER JOIN #ttemp t
on c.sa1 = t.sa1 
and c.sa2 = t.sa2
SET ROWCOUNT 0 

declare @t table(sal1 int,sal2 int,dur1 int,dur2 int)
insert into @t 
	select 6,7,3,2 union all select 6,7,3,3 union all
	select 354,867,1,2 union all select 354,867,1,3 union all
	select 354,872,1,2 union all select 354,872,1,3 union all
	select 356,867,1,2 union all select 356,867,1,3 union all
	select 356,872,1,2 union all select 356,872,1,3
	
select * from @t

sal1	sal2	dur1	dur2
6	7	3	2
6	7	3	3
354	867	1	2
354	867	1	3
354	872	1	2
354	872	1	3
356	867	1	2
356	867	1	3
356	872	1	2
356	872	1	3

sal1	sal2	dur1	dur2
6	7	3	2

sal1	sal2	dur1	dur2
6	7	3	3

select sal1,sal2,dur1,dur2 from(
select row_number() over(partition by sal1,sal2 order by sal1,sal2) rn, sal1,sal2,dur1,dur2 from @t) X
where rn = 1

sal1	sal2	dur1	dur2
6	7	3	2
354	867	1	2
354	872	1	2
356	867	1	2
356	872	1	2

CREATE OR REPLACE VIEW schema_tables AS
SELECT   o.object_type AS object_type
,        c.table_name AS table_name
,        c.column_id AS column_id
,        c.column_name AS column_name
,        DECODE(c.nullable,'N','NOT NULL','') AS nullable
,        DECODE(c.data_type
,          'BFILE'        ,'BINARY FILE LOB'
,          'BINARY_FLOAT' ,c.data_type
,          'BINARY_DOUBLE',c.data_type
,          'BLOB'         ,c.data_type
,          'CLOB'         ,c.data_type
,          'CHAR'         ,DECODE(NVL(c.data_length,0),0,c.data_type
,        c.data_type||'('||c.data_length||')')
,          'DATE'         ,c.data_type
,          'FLOAT'        ,c.data_type
,          'LONG RAW'     ,c.data_type
,          'NCHAR'        ,DECODE(NVL(c.data_length,0),0,c.data_type
,        c.data_type||'('||c.data_length||')')
,          'NVARCHAR2'    ,DECODE(NVL(c.data_length,0),0,c.data_type
,        c.data_type||'('||c.data_length||')')
,          'NUMBER'       ,DECODE(NVL(c.data_precision||c.data_scale,0)
,        0,c.data_type
,        DECODE(NVL(c.data_scale,0),0
,        c.data_type||'('||c.data_precision||')'
,        c.data_type||'('||c.data_precision||','|| c.data_scale||')'))
,          'RAW'          ,DECODE(NVL(c.data_length,0),0,c.data_type
,        c.data_type||'('||c.data_length||')')
,          'VARCHAR'      ,DECODE(NVL(c.data_length,0),0,c.data_type
,        c.data_type||'('||c.data_length||')')
,          'VARCHAR2'     ,DECODE(NVL(c.data_length,0),0,c.data_type
,        c.data_type||'('||c.data_length||')')
,          'TIMESTAMP'     , c.data_type,c.data_type) AS data_type
,        CASE WHEN c.data_default IS NULL THEN 'N' ELSE 'Y' END AS data_default
FROM     user_tab_columns c,user_objects o
WHERE    o.object_name = c.table_name
ORDER BY c.table_name, c.column_id
/
set feed off markup html on spool on
/
spool 'c:\filename.htm'
/
select * from schema_tables
/
spool off
/
set markup html off spool off
/

dbms_metadata.get_ddl(
object_type IN VARCHAR2,
name        IN VARCHAR2,
schema      IN VARCHAR2 DEFAULT NULL,
version     IN VARCHAR2 DEFAULT 'COMPATIBLE',
model       IN VARCHAR2 DEFAULT 'ORACLE',
transform   IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
     FROM USER_TABLES u;

SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
     FROM USER_INDEXES u;

DECLARE @Input int
SET @Input = 30
SELECT TOP 1 Col1 FROM Table1
WHERE Col1 = (SELECT MIN(ABS(Col1-@Input))+@Input FROM Table1)
OR Col1 = (SELECT @Input-MIN(ABS(Col1-@Input)) FROM Table1)

SELECT * FROM news1 ORDER BY (news_id-10)*(news_id-10)

SELECT * FROM news1 ORDER BY ABS(news_id-10)

"... ORDERBY (var1-goal1)*(var1-goal1)+(var2-goal2)*(var2-goal2)...+(varN-goalN)*(varN-goalN)"

select num as [Closest Number] from(
select DENSE_RANK() over(order by abs(num - @inputNum)) [rank]		
		,num from @t
)X
where [rank] = 1

Closest Number
31

DECLARE @i int

SET @i = 30

SELECT TOP 1 Col1 FROM Table1 ORDER BY ABS(Col1-@i)