Jump to content

a numbered column


glendango

Recommended Posts

Hi, any one know how to display a simple numeric value next to the results of a select query?   why cant i find the correct search term to do this?? i want the result to be numbered with a simple 1-10 position.=column =   pos

 

 

 

pos           name         id        leads

 

 

1              Williams         1             12

2              bond               9             4

3              brown             5              3

4             williams            3              3

5             you                   6             2

6             eyes                 7             2

etc

 

 

 

 

 

i can only find row_num which gives me the record number of the row...

Link to comment
Share on other sites

Here's how to do it with an SQL query (@seq etc are variables used to store values from one record for use in the next as the records are processed)

SELECT id
     , name
     , @seq := @seq + 1 as seq
     , @rank := CASE WHEN leads = @prev THEN @rank ELSE @seq END as rank
     , @prev := leads as leads
FROM leads
JOIN (SELECT @seq:=0, @rank:=0, @prev:=0 ) as initialize
ORDER BY leads DESC
You would do it same way using PHP. The $seq is always incremented. If the leads value in a row is the same as the previous row then the $rank is the same as the previous $rank. If the leads values are different then the $rank is the same as the $seq
Link to comment
Share on other sites

SELECT usr_id , @seq := @seq + 1 as seq FROM firsts JOIN (SELECT @seq:=0, @rank:=0, @prev:=0 ) as initialize GROUP BY usr_id DESC

 

 

this is great...

simplified it to spit out the sequence on MySQL for just the sequence.cheers....  i changed to group as well so it kept all users to 1 entry. 

 

no wi will work on the rank part . 

 

 

 

another annoying question for you...

 

 

every time i try an do something new on my app it takes 1 day and at the end of the day i ask for help and you help me....this cant be correct...  ( bearing in mind iam not a dev and only been coding a few months..)  

 

i know the answer to this is to employ a pro app creater , but what do they use??  do they actually sit there all day working this stuff out for their custoemrs or does symphony, laraval frameworks etc do it all for you???  i cant see how they would do it all for you as iam asking for quite specific things to make my app niche.. would love some inside info as to best practice // give up ( which i wont lol) 

Link to comment
Share on other sites

Frameworks can make the work go much faster, if one has the support you need. Like I've spent a long (and I cannot emphasize that enough) time working on my own framework for my own code, but with most of the important work now in place I can crank out the more interesting "app" stuff in a fraction of the time it would take without.

 

Nearly every framework out there helps with database access so you don't have to do the same tedious work over and over every time you want to run a query. For example, this

$results = db()->prepare("SELECT * FROM table WHERE column = :value")->query(["value" => $_GET["value"]]);
code written with a framework would correspond to... uh... I figure at least a minimum of a dozen lines without a framework. And it took seconds as opposed to a few minutes of finding similar code somewhere else, copying and pasting it, then adjusting the code as needed.

 

The rest is definitely experience and other learned knowledge.

Link to comment
Share on other sites

thats good to hear!  

i will only blueprint mine then get it written properly.

 

i need to work through your code tomoorow when awake but the bit ive done :

 

 

SELECT usr_id ,count(date_made)as da, @seq := @seq + 1 as seq FROM firsts JOIN (SELECT @seq:=0, @rank:=0, @prev:=0 ) as initialize GROUP BY usr_id order by da DESC

 

 

does this...  the seq seems to be acting like row_num which keeps latching onto the row number rather then just give me a straight count. da in this case is the amount of leads,,, so rather then 

1,2,6,3,5,4

 

i want 

 

1,2,3,4,5,6  

 

usr_id     da       seq  

1             12        1

3              5         2

9              4         6

5              2         3

7              2          5

6              1           4

 

this is the real select i am working with: on main app if it helps :  

 

$result = mysqli_query($conn, "SELECT u.name , u.surname, f.usr_id, COUNT(f.usr_id) AS totalfirstsever FROM users u JOIN firsts f on u.id  = f.usr_id  group by f.usr_id order by totalfirstsever DESC limit 10 ");

Link to comment
Share on other sites

this works well - gives me numbers by the side of users :

 

set @rownum := 0;
select usr_id, count(date_made) as date , @rownum := @rownum + 1 as row_number from firsts group by usr_id 

 

when i add 'order by date' at the end of the select, the  numbers follow the usr_id around and don't stay in sequence i.e 1,2,3,4,5

 

this is what all answers do on stack as well... 

.   

Link to comment
Share on other sites

I'm pretty sure that's because of when MySQL evaluates the variables and assignments: when the rows are pulled from the table. If you ORDER BY an indexed column then MySQL will use that and the numbers will be in order, but when you sort by something else the numbers will be out of order.

 

Keeping this approach, do a subquery. Converting Barand's original query,

SELECT id
     , name
       /* the ranking happens outside */
     , @seq := @seq + 1 as seq
     , @rank := CASE WHEN leads = @prev THEN @rank ELSE @seq END as rank
     , @prev := leads as leads
FROM (
	/* moved the main bulk of the query inside */
	SELECT *
	FROM leads
	ORDER BY leads DESC
) as leads
JOIN (SELECT @seq:=0, @rank:=0, @prev:=0 ) as initialize
/* sorting happened inside */
Link to comment
Share on other sites

You are trying to allocate the rownum before the count totals are known. You need to put your ducks in a row before you can allocate the correct sequence or ranking. To do this you need a temporary table of the counts for each user which you can do by creating a table subquery. I also use a subquery to initialize the @ values (it saves have to do SET queries first).

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.