Comaptible queries in sql-server

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

Other Forums

Other Topics

Comaptible queries in sql-server

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-03-30 : 08:39:37

shreeman narayana dash writes "hi can any body suuply me the sql-server counter paert of following oracle queries

nth val:-

select level, max('col_name') from my_table
where level = '&n'
connect by prior ('col_name') > 'col_name')
group by level;

-- Example :
--
-- Given a table called emp with the following columns:
-- id number
-- name varchar2(20)
-- sal number
--
-- For the second highest salary:
--
-- select level, max(sal) from emp
-- where level=2
-- connect by prior sal > sal
-- group by level
--

-----------------------------------------------------
delete duplicate:-

DELETE FROM table_name A WHERE ROWID > (
SELECT min(rowid) FROM table_name B
WHERE A.key_values = B.key_values);
------------------------------------------------------
delete from my_table t1
SQL> where exists (select 'x' from my_table t2
SQL> where t2.key_value1 = t1.key_value1
SQL> and t2.key_value2 = t1.key_value2
SQL> and t2.rowid > t1.rowid);
-------------------------------------------------------------------
Can one retrieve only the Nth row from a table?
Rupak Mohan provided this solution to select the Nth row from a table:
SELECT * FROM t1 a
WHERE n = (SELECT COUNT(rowid)
FROM t1 b
WHERE a.rowid >= b.rowid);
--------------------------------------------------------
SELECT * FROM (
SELECT ENAME,ROWNUM RN FROM EMP WHERE ROWNUM < 101 )
WHERE RN = 100;
------------------------------------------------
SELECT f1 FROM t1
WHERE rowid = (
SELECT rowid FROM t1
WHERE rownum <= 10
MINUS
SELECT rowid FROM t1
WHERE rownum < 10);
SELECT rownum,empno FROM scott.emp a
GROUP BY rownum,empno HAVING rownum = 4;

Alternatively...
SELECT * FROM emp WHERE rownum=1 AND rowid NOT IN
(SELECT rowid FROM emp WHERE rownum < 10);

Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even help in the odd situation.
-----------------------------------------------------------------------------------
Can one retrieve only rows X to Y from a table?
Shaik Khaleel provided this solution to the problem:
SELECT * FROM (
SELECT ENAME,ROWNUM RN FROM EMP WHERE ROWNUM < 101
) WHERE RN between 91 and 100 ;

Note: the 101 is just one greater than the maximum row of the required rows (means x= 90, y=100, so the inner values is y+1).
Ravi Pachalla provided this solution:

SELECT rownum, f1 FROM t1
GROUP BY rownum, f1 HAVING rownum BETWEEN 2 AND 4;

Another solution is to use the MINUS operation. For example, to display rows 5 to 7, construct a query like this:
SELECT *
FROM tableX
WHERE rowid in (
SELECT rowid FROM tableX
WHERE rownum <= 7
MINUS
SELECT rowid FROM tableX
WHERE rownum < 5);

Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even help in the odd situation.

-------------------------------------------------------------

How does one select EVERY Nth row from a table?
One can easily select all even, odd, or Nth rows from a table using SQL queries like this:
Method 1: Using a subquery

SELECT *
FROM emp
WHERE (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4)
FROM emp);

Method 2: Use dynamic views (available from Oracle7.2):
SELECT *
FROM ( SELECT rownum rn, empno, ename
FROM emp
) temp
WHERE MOD(temp.ROWNUM,4) = 0;

Method 3: Using GROUP BY and HAVING - provided by Ravi

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-03-31 : 19:03:08

In SQL Server there is no ROWID system field. You can mimic this using an IDENTITY in your SELECT statement like Damian shows here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33658

As for the Nth highest, use the technique shown in the What's After Top article

And for Deleting Duplicates, check out Deleting Duplicate Records

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources