• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 130
  • Last Modified:

unable to get sorting resultset

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
satmisha
Asked:
satmisha
2 Solutions
 
Pawan KumarDatabase ExpertCommented:
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
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
satmishaAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
satmishaAuthor Commented:
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
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
satmishaAuthor Commented:
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
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
satmishaAuthor Commented:
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
 
satmishaAuthor Commented:
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
 
satmishaAuthor Commented:
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
 
satmishaAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
satmishaAuthor Commented:
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
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
satmishaAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now