get serial number according order by in a mysql query

Question

1.00/5 (1 vote)

See more:

hi all ,
i need serial number in order by query in my sql. Means i have table like following(table)

C#

id   name
11   ram
21   sita
31   gita
41   deepak
51   harshit
61   prashant

my query is

C#

select name,@acount:=@acount+1 serial_number
from (SELECT @acount:= 0) AS acount,user
order by name asc

and i need result like this

C#

name     serial_number
deepak   1
gita     2
harshit  3
prashant 4
ram      5
sita     6

thanks
mohit

Posted 16-Sep-13 22:48pm

mohit.a44

Updated 16-Sep-13 23:16pm

Thanks7872

v2

Add a Solution

3 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)

Thomas ktg · Answer 1 · 2013-09-16T22:59:00

Solution 1

Hi try like this,

This is the sample query i have written to achieve your requirement.
Hope it helps.

select concat(name,' ',@row := @row + 1) as Name from user,(SELECT @row := 0) r order by @row := @row;
select name as Name,@row := @row + 1 as SNo from user,(SELECT @row := 0) r order by @row := @row;

Posted 16-Sep-13 22:59pm

Thomas ktg

Updated 16-Sep-13 23:26pm

v2

Comments

mohit.a44 17-Sep-13 5:15am

i need this with order by

Thomas ktg 17-Sep-13 5:23am

order by name or row no?

mohit.a44 17-Sep-13 5:31am

name not row, and row no will come according to name

Thomas ktg 17-Sep-13 5:33am

i updated the solution please look at it.

mohit.a44 17-Sep-13 5:35am

wrong your solution is giving me this

Name SNo
ram 1
sita 2
gita 3
deepak 4
harshit 5
prashant 6

and i need this

name serial_number
deepak 1
gita 2
harshit 3
prashant 4
ram 5
sita 6

Thomas ktg 17-Sep-13 5:39am

use order by name, u will get this.

mohit.a44 17-Sep-13 5:50am

yaah its work but i use same method with inner join query that have many order by column then result is wrong here is my query

SELECT si.*, FnSearchEngineLSP_ftl(si.CITY_ORG,si.CITY_DEST,si.CARRIER_TYPE,25,25) upcome,
CONCAT(sm.SM_NAME,'-',cm.DISTRICT) org,
CONCAT(sm1.SM_NAME,'-',cm1.DISTRICT) dest,
DATE_FORMAT(si.ENTRY_DATE,'%d/%m/%Y' ) inquiry_DATE,
um.USER_CONTACT_NAME,
DATE_FORMAT(si.DATE_PICK,'%d/%m/%Y' ) DATE_PICK,
DATE_FORMAT(si.DATE_DELIVERY,'%d/%m/%Y' ) DATE_DELIVERY,
CONCAT('response_ftl.php?inid=',si.INQUIRY_ID) pageLink,
(CASE si.BID_TYPE WHEN 'V' THEN
CASE WHEN (SELECT ab.IS_VARIFIED FROM s_user_master ab WHERE ab.user_id=25) ='V' THEN '1'
ELSE '0'
END
ELSE '1'
END
)enq_verify
,@ROW := @ROW + 1 AS SNo
FROM (SELECT @ROW := 0) AS r, s_shipper_inquiry AS si
INNER JOIN s_user_master um ON si.USER_ID=um.USER_ID
INNER JOIN (s_state_master sm , s_state_master sm1) ON si.STATE_ORG=sm.SM_ID AND si.STATE_DEST=sm1.SM_ID
INNER JOIN (s_city_master cm , s_city_master cm1) ON si.CITY_ORG = cm.DM_ID AND si.CITY_DEST=cm1.DM_ID
INNER JOIN (s_tahsil_master tm , s_tahsil_master tm1) ON si.TEHSIL_ORG=tm.TM_ID AND si.TEHSIL_DEST=tm1.TM_ID
INNER JOIN (s_pincode_master pm , s_pincode_master pm1) ON si.AREA_ORG=pm.PM_ID AND si.AREA_DEST=pm1.PM_ID

WHERE si.INQUIRY_TYPE=1
AND si.INQUIRY_ID NOT IN
(IFNULL((
SELECT sirej.INQUIRY_ID FROM getall_inquiry_res_rej sirej WHERE sirej.user_id=25 AND sirej.INQUIRY_ID=si.INQUIRY_ID
),0)

,
IFNULL((
SELECT girf.INQUIRY_ID FROM getall_inquiry_reponse_ftl girf WHERE girf.user_id=25 AND girf.INQUIRY_ID=si.INQUIRY_ID
),0))
HAVING enq_verify!='0'
ORDER BY upcome, si.DATE_PICK ,si.DATE_DELIVERY ,ENTRY_DATE ,si.INQUIRY_ID DESC
LIMIT 10

mohit.a44 17-Sep-13 5:58am

in this query have many order by and this method is not working with many order by colunm

Thomas ktg 17-Sep-13 6:15am

Check your query whether it gives the desired output and then include the row no.

mohit.a44 17-Sep-13 6:24am

means???

Thomas ktg 17-Sep-13 6:33am

Do you get the correct output while you execute the query without row number?

mohit.a44 17-Sep-13 6:53am

i used other way
limit 0,10
then 11,10
so on....
thank for reply me , thanks again

VeeraAnnapureddy · Answer 2 · 2013-09-17T02:19:00

Solution 2

SQL

select name,row_number() over(order by Id) as serial_number from user

Posted 17-Sep-13 2:19am

VeeraAnnapureddy

Comments

Member 11023380 21-Aug-14 5:43am

its not working

Member 11023380 21-Aug-14 5:44am

its not working in ms access 2007

mohit.a44 · Answer 3 · 2013-09-17T02:31:00

Solution 3

i used other way limit 0,10 then 11,10 so on....

Posted 17-Sep-13 2:31am

mohit.a44