Click here to Skip to main content
15,792,539 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a access query contains count(*) to select as like Row_Number(), but the order by is not working like the expected result.

My Query :
Select 
    (
        select 
            count(*) 
        from 
            partymst 
        where 
            pt.ID>=ID order by pt.pty_name) as sl_no,
            pt.* from partymst as pt order by pt.pty_name

The above statement is giving like the following Output.
Sl-No Name 
2     Maheswaran & Co
3     Pollachi Thatha & co
1     Pollachi Kodi Thatha

So, how to achieve the sl_no by 1,2,3

Thanks for the right directions.
Posted
Comments
Dennis_E 7-Apr-14 4:03am    
You are ordering by pt.pty_name
Just order by sl_no
or am I missing something???
btw, what is the point of the 'order by' in the inner statement?

SQL
SELECT *
FROM
(
         select
	(SELECT count(*) FROM partymst WHERE pt.ID >= ID) AS sl_no
	,pt.* 
	from partymst as pt
) as temptbl 
order by sl_no

Happy Coding!
:)
 
Share this answer
 
v2
Comments
Paramu1973 7-Apr-14 4:42am    
Syntax error in Join Operation....Thank you
Aarti Meswania 7-Apr-14 4:52am    
I have updated ans try now
Paramu1973 7-Apr-14 5:11am    
Index has not working....Thankyou need to include Order by ...Thankyou
Aarti Meswania 7-Apr-14 5:23am    
Welcome! :)
Maciej Los 8-Apr-14 2:13am    
+5!
//i have tested the below code its working correctly in sql server
try this one agin and let me know

SQL
//chnage the last pt.pty_name to pt.sl_no
Select 
    (  select 
            count(*) 
        from 
            TestTable 
        where 
            pt.ID>=ID) as sl_no,
            pt.Name from TestTable as pt
 
         order by pt.ID
 
Share this answer
 
v3
Comments
Paramu1973 7-Apr-14 21:24pm    
OleDbException was unhandled : No value given for one or more required parameters
Thankyou
Suk@nta 8-Apr-14 1:55am    
agin try above changed code..If it not help then plz check which line showing error..then give a reply

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900