|
Try this
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
I am taking this RecordSet
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
Since you want any one of the record set so I can use either
sal1 sal2 dur1 dur2
6 7 3 2
or
sal1 sal2 dur1 dur2
6 7 3 3
If this assumption of mine is correct, the here is the answer
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
Output:
sal1 sal2 dur1 dur2
6 7 3 2
354 867 1 2
354 872 1 2
356 867 1 2
356 872 1 2
Here I am considering only the first one for every duplicate entries
What next you can do is put this record set in some temp table , delete the original one and then insert this record back into the table.
Please let me know in case of any concern.
Note - This code will work for Sql server 2005+
Niladri Biswas
modified on Tuesday, November 24, 2009 9:31 AM
|
|
|
|
|
User row_number partitioned over your key fields. This is a sample of a partition I use
ROW_NUMBER() OVER( PARTITION BY ProductID, SubProductID, IssueLabel, Maturity, CurrencyID, Exposure Order by Exposure) as RowNo
You need to include the ID field in the rest of the select and then delete any record where the RowNo > 1.
|
|
|
|
|
Hey everyone,
I'm using Oracle 10.2g with PL/SQL Developer 7.1 from Allround Automation and I want to list my tables one by one showing each detailing its fields.. Any simple way to do this??
Many thanks!
|
|
|
|
|
The only way I've done it in the past was write some PL/SQL code that would loop through tabels like: SELECT *
FROM ALL_TABLES
WHERE OWNER='myOwner
ORDER BY TABLE_NAME;
|
|
|
|
|
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
/
|
|
|
|
|
Use:
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;
Example:
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;
Gives you all the tables and indexes for the logged in user.
|
|
|
|
|
Dear All,
I need your help and deal.
I'm using SQL Server 2005.
I want to select a number that nearby a number that i want.
Example:
30 is number i need. when select from table have some number equal it.
So i want a number is so near 30.
(12, 43, 31, 35) = 31
I am sorry if descipt not clear...
Thanks you for your help.
Best Regards,
Sovann
VB.Net
|
|
|
|
|
I dont get enough your question but I think you have to use BETWEEN in your select.
Example:
select * from yourtable where number between 29 and 31
This sttement will give you all records between value 29 and 31
It will be better if you explain more your question.
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.
www.aktualiteti.com
|
|
|
|
|
Thanks you Blue_boy
but i need only one number.
If that number do not have i need number that nearby it.
so how can i do ?
VB.Net
|
|
|
|
|
Well,I'm gonna ask you a question.
Case is: 1,29,30,32 and you want search the nearest number of 30? So, the nearest number is 29 and not 32, right?
What if you have case like this : 1,29,30,31? Which nearest number you need to get?
I think you have to write a function.
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.
www.aktualiteti.com
|
|
|
|
|
Try something like this:
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)
You may need to modify this query.
|
|
|
|
|
you can use the least-square approach, provided your numbers won't overflow when squared.
Example:
SELECT * FROM news1 ORDER BY (news_id-10)*(news_id-10)
would order the news items according to their distance of news_id value 10.
Then optionally pick the first one, or first few, with whatever your SQL environment needs for doing that
(often "TOP 1").
Luc Pattyn [Forum Guidelines] [My Articles]
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
|
|
|
|
|
Awesome answer. Thanks for teaching me something new.
|
|
|
|
|
Better yet, this query works too:
SELECT * FROM news1 ORDER BY ABS(news_id-10)
|
|
|
|
|
of course it does, however using squares is a common technique to optimize a "cost function", say when you need to find a best match for several parameters, you do:
"... ORDERBY (var1-goal1)*(var1-goal1)+(var2-goal2)*(var2-goal2)...+(varN-goalN)*(varN-goalN)"
Luc Pattyn [Forum Guidelines] [My Articles]
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
|
|
|
|
|
I believe the above will order the items by the value of news_id-10, not by their distance from the target value of 30. To do that, you would have to change the ORDER BY clause to
((news_id-10 * new_id-10) - (target_value * target_value)).
It might be easier still to use
ABS(ABS(news_id-10) - ABS(target_value))
though using the function ABS() may slow execution somewhat.
I've been trying to figure out a way to use MIN(ABS(news_id-10) - ABS(target_value)) in a way that will return a record instead of a single value, but I lack the knowledge for that (for now). That would give him the desired information in one step.
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
Hi Roger,
I don't know what you are trying with all those ABS functions, they don't make sense to me.
My point is: to order items according to their distance from a target or goal, you either use a single ABS (useful only for one criterium, one goal), or a least-square approach (where you calculate a formula involving squaring deviations as I indicated before). My example with news_id (and target 10) wasn't an exact match for the OP, it was the code that I tested for something of mine.
Luc Pattyn [Forum Guidelines] [My Articles]
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
|
|
|
|
|
I thought the ABS function might be a shortcut, in that it accomplishes the same thing as squaring when you want only the distance between two values, regardless of which is smaller. My main consideration was that the example you gave did not return a distance from the target of 30, only reorders the table. Now that I reread your response, I see that the value I was reading as a single variable (news_id-10) is actually (new_id - 10). That's what I get for reading this stuff late at night after a long day at work, wiring sewer plant pump motors in a frigid dust storm. My bad. 
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
NP
Luc Pattyn [Forum Guidelines] [My Articles]
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
|
|
|
|
|
This will work gracefully
declare @t table(num int)
insert into @t
select 12 union all select 43
union all select 31 union all select 35
declare @inputNum int
set @inputNum = 30
select num as [Closest Number] from(
select DENSE_RANK() over(order by abs(num - @inputNum)) [rank]
,num from @t
)X
where [rank] = 1
Output:
Closest Number
31
Note: Try with
Input Output
100 43
-1 12
39 43 & 35
(which is correct only because the difference is 4 for both the case). Henceforth I have used Dense_Rank()
Hope this helps
Niladri Biswas
modified on Monday, November 23, 2009 9:37 AM
|
|
|
|
|
thanks you so much Niladri Diswas....
VB.Net
|
|
|
|
|
Here is a better answer:
DECLARE @i int
SET @i = 30
SELECT TOP 1 Col1 FROM Table1 ORDER BY ABS(Col1-@i)
|
|
|
|
|
Hi,
Just 1 question to ask?
If the input is 39 what be the output? Is it 43 or 35 or both?
If the answer is the last one, then how
Shameel wrote: TOP 1 Col1
will help?
Niladri Biswas
|
|
|
|
|
If you remove the 'TOP 1' clause, you will see that the query returns both values. Adding TOP clause arbitrarily returns the first match.
|
|
|
|
|
Dear All
Thanks for you help.
Now i can get that task ready.
Best regards,
VB.Net
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
