Access Query - Count(*) as sl_no order by is not working?

Question

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 6-Apr-14 21:39pm

Paramu1973

Add a Solution

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?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Suk@nta · Answer 1 · 2014-04-07T01:34:00

Solution 2

//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

Posted 7-Apr-14 1:34am

Suk@nta

Updated 7-Apr-14 20:12pm

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

Aarti Meswania · Answer 2 · 2014-04-06T22:18:00

Solution 1

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!
:)

Posted 6-Apr-14 22:18pm

Aarti Meswania

Updated 6-Apr-14 22:52pm

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!

Aarti Meswania 8-Apr-14 12:35pm

Thank you! Maciej los :)