Solved

unable to get sorting resultset

Posted on 2016-11-11
15
48 Views
Last Modified: 2016-11-15
Hi Experts,

I am using below query to get the sorted resultset but unfortunately fetched resultset is not coming as sorted. Unable to understand what wrong in the below stated query please help.

select * from (
SELECT      FName
FROM      EmpTable
ORDER BY Fname desc
)
WHERE      rownum >  0 AND      rownum < 3000
0
Comment
Question by:satmisha
15 Comments
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41883515
Try ..ORDER BY Fname desc at the end. rownum  code ? Is this for Oracle / SQL Server ?  

select * from
(
 SELECT      FName
 FROM      EmpTable
 )
 WHERE      rownum >  0 AND      rownum < 3000
ORDER BY Fname desc


FOR SQL Server


;WITH CTE AS
(
	SELECT 1 a , 'Aneesh' FName
	UNION ALL
	SELECT 2 a , 'Pawan' FName
)
SELECT a,FName FROM 
(
	SELECT * , ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rnk FROM CTE
)k
ORDER BY k.FName DESC

Open in new window



Output
-----------
a      FName
2      Pawan
1      Aneesh


FOR Oracle

WITH CTE AS
(
	SELECT 1 a , 'Aneesh' FName FROM DUAL
	UNION ALL
	SELECT 2 a , 'Pawan' FName  FROM DUAL
)
SELECT *
FROM CTE
ORDER BY FName DESC

Open in new window


Output
-----------
     A   FNAME
1   2   Pawan
2   1   Aneesh
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 41883596
Try ..ORDER BY Fname desc at the end. rownum  code ? Is this for Oracle / SQL Server ?  

select * from
(
 SELECT      FName
 FROM      EmpTable
 )
 WHERE      rownum >  0 AND      rownum < 3000
ORDER BY Fname desc

Oh my God!!! NEVER do something like that!!! The behavior and thus the result of queries like that are just unpredictable!!!! Let's imagine the EmpTable has OVER 3000 entries: the ordering will be FALSE!!

And yes, rownum is Oracle ;-)

Btw: @Pawan Kumar Khowal: You don't seem to know Oracle and/or SQL very well, so please DO NOT mislead people here!!
1
 

Author Comment

by:satmisha
ID: 41883599
Thanks Pawan for your quick reply, I am doing this for oracle & provided below is the query that I am using.

SELECT *
FROM
  (SELECT
    Row_number() over(Order by Fname desc) AS RowRank,
    Fname
  FROM Emp_Table
  WHERE (Emp_Table.status != '1111')
  ORDER BY  Fname desc
  ) tbl
WHERE RowRank BETWEEN 0 AND 2000
0
 

Author Comment

by:satmisha
ID: 41883601
Thanks alexander the ordering is completely false, I have mentioned the query against which I am getting unpredicting result set i.e. :

SELECT *
FROM
  (SELECT
    Row_number() over(Order by Fname desc) AS RowRank,
    Fname
  FROM Emp_Table
  WHERE (Emp_Table.status != '1111')
  ORDER BY  Fname desc
  ) tbl
WHERE RowRank BETWEEN 0 AND 2000

PLs suggest, I am using oracle db.
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 41883605
Taking Oracle's example table as shown below, the query works as it should ;-)

select *
  from (select last_name
          from hr.employees
         order by last_name desc)
 where rownum < 50;

Open in new window


Could you provide us the strucure and some sample data of your table?!
0
 

Author Comment

by:satmisha
ID: 41883621
Thanks alexander what is rownum in this case ? Also I am doing custom paging i.e. why used WHERE RowRank BETWEEN 0 AND 2000, how would I able to make that ?

select *
  from (select last_name
          from hr.employees
         order by last_name desc)
 where rownum < 50;
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 41883629
Taken from https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm
For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

Regarding the custom pagination, take a look here: http://stackoverflow.com/questions/13738181/best-practice-for-pagination-in-oracle
SELECT * 
  FROM (SELECT *, rownum rn
          FROM (SELECT *
                  FROM your_table
                 ORDER BY col)
         WHERE rownum <= :Y)
 WHERE rn >= :X

Open in new window


Where do you want to perform that pagination (what application)?!
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:satmisha
ID: 41883773
Thanks alexander,
I am using .net application and oracle as db.
I have 100000 record set in db, I am trying to fetch 1000 records at a time like
1st time --> 0 to 1000
2nd time --> 1001 to 2000
3rd time --> 2001 to 3000
4th time --> 3001 to 4000
5th time --> 4001 to 5000
0
 

Author Comment

by:satmisha
ID: 41883791
Thanks Alexander,
 I tried to use but it throes exception i.e. :

ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected

SELECT *
  FROM (SELECT *, rownum rn
          FROM (SELECT *
                  FROM your_table
                 ORDER BY col)
         WHERE rownum <= :Y)
 WHERE rn >= :X
0
 

Author Comment

by:satmisha
ID: 41883793
I got foolowing error when I used suggested query:

ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"


SELECT *
  FROM (SELECT *, rownum rn
          FROM (SELECT *
                  FROM your_table
                 ORDER BY col)
         WHERE rownum <= 1000)
 WHERE rn >= 0

above I am trying to fetch rows from 0 to 1000.
0
 

Author Comment

by:satmisha
ID: 41883809
Hi Alexander,

able to run query successfully but still resultset is not sorted the way it should be

 SELECT * FROM (SELECT ROWNUM rnum,a.EmpName FROM (
 SELECT EmpName FROM Emp_Table order by EmpName desc) a)
 WHERE rnum BETWEEN 0 AND 1000;
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 41884338
I would suggest what you used in  #a41883601

You don't need the order by that is outside of the ROW_NUMBER function.  It just adds unnecessary work.

SELECT *
FROM
  (SELECT
    Row_number() over(Order by Fname desc) AS RowRank,
    Fname
  FROM Emp_Table
  WHERE (Emp_Table.status != '1111')
  ) tbl
WHERE RowRank BETWEEN 0 AND 2000

Open in new window



You say the ordering isn't working.  Please provide sample data and expected results.

If it is a name case issue, just add LOWER or UPPER to the ROW_NUMBER:  
Row_number() over(Order by lower(Fname) desc) AS RowRank,
0
 

Author Comment

by:satmisha
ID: 41885884
GM Slightwv,

Thanks for giving me right direction.

After further analysing I found that issue is with lower and upper case which caused confusion in end resultset. Like it first sort lower case date and than followed by upper case letters for example:

desc part brings following resutl-set:

for first 1 to 5000 records sets it brings :

w
u
r
n
d
c
a
Z
Y
W
V
U

whereas expected value should be irrespective of lower or upper case i.e. :

Z
Y
W
w
V
U
u
r
n
d
c
a


Is there a way to sort irrespective of upper or lower case as above result set is quite confusing.
0
 
LVL 13

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 250 total points
ID: 41885947
See here: https://docs.oracle.com/cd/B28359_01/server.111/b28298/ch5lingsort.htm#i1009059

You can influence this sorting behavior either through altering the session, like (for example)
alter session set nls_sort=binary_ci;

Open in new window


or using NLSSORT like
with list2sort as(
  select 'w' val from dual union 
  select 'u' from dual union 
  select 'r' from dual union 
  select 'n' from dual union 
  select 'd' from dual union 
  select 'c' from dual union 
  select 'a' from dual union 
  select 'Z' from dual union 
  select 'Y' from dual union 
  select 'W' from dual union 
  select 'V' from dual union 
  select 'U' from dual)
select *
  from list2sort
 order by nlssort(val, 'NLS_SORT=binary_ci') desc;

Open in new window

0
 

Author Closing Comment

by:satmisha
ID: 41887762
Thanks both of you experts. Best solution that worked for me in this scenerio was of slightwv, the issue was with the name case which got rectified by using lower() as suggested by him.

Thanks both of your time.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now