tags:

views:

45

answers:

1
SET @row := 0;
SELECT * FROM (
SELECT @row := @row+1 AS rank, account_id, keyword, bid_amount ,timestamp
FROM bids WHERE keyword='programmers'
ORDER BY bid_amount DESC, timestamp ASC
) AS derived_table;

i saw this somewhere and i was wondering what the set does along with the @ sign and the :=

thanks

+5  A: 

It's creating a row number column.

@row is an integer variable. := sets the variable for each row, taking the previous row's value of @row. So, you get a nicely incrementing column which tells you what number row you're on.

Eric
but from this set example its setting row to be equal to 0. then selecting that 0'th as rank. so is it basically selecting from the very beginning row?
i mean why not just do a simple select id,column1,column2,... from table ??
@sarmenhbb It is for legibility of the output only. If this returns a list of 300 rows, you can look at the middle of the list and say "this is the 150th highest bid_amount" without having to count the rows yourself.
Peter Di Cecco