Solved

unable to get sorting resultset

Posted on 2016-11-11
15
88 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
15 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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 77

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

736 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