Identity in descending order

Question

Post reply

Identity in descending order

sql_novice_2007

Hall of Fame

Points: 3307
More actions
April 22, 2008 at 2:24 pm

#185643

Is there any way I can pass a variable to the seed value in when I create the identity.
This is because I need to display f_name,l_name,city,state,zip_code and the I need to display the list order by f_name desc.
If the following is the data I will display from the query
f_name l_name city state
Fn3 ln3 c1 s1
fn2 ln2 c1 s1
fn1 ln1 c4 s1
For the above result I need to add another column display_id
so the new result should be like the following
display_id f_name l_name city state
3 Fn3 ln3 c1 s1
2 fn2 ln2 c1 s1
1 fn1 ln1 c4 s1
If its a fixed number of rows(say 3) I can pass seed as 3 and increment as -1 but the number of rows will vary.
Is there any way I can do this?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

When you query the data, add "Order By display_id desc". The "desc" at the end makes it go from highest to lowest.

That should do what you need.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

sql_novice_2007 Hall of Fame Points: 3307 More actions · Answer 2

Thanks for response but My problem is something different please look at the following is the code and let me know if I will be able to do this with a variable seed value.

DECLARE @sqlstmt varchar(2000),

@tbl_temp1 varchar(100)

SELECT @tbl_temp1='##tbl_temp_get_data'

SELECT @sqlstmt='SELECT f_name,l_name,city,state INTO ' + @tbl_temp1 + ' from tblx ORDER BY ' +@col_name+' '+@orderby

EXEC (@sqlstmt)

SELECT IDENTITY(int, 3, -1) AS display_id, * INTO #tmpReport11 FROM ##tbl_temp_get_data

SELECT * FROM #tmpReport11

So display_id is just the row id of the result and it should be in descending order.

But in the above piece of code seed is not a fixed value.

I Need to pass the count of number of rows in the table ##tbl_temp_get_data as seed for this identity.

DECLARE @count int

SELECT @count=COUNT(*) FROM ##tbl_temp_get_data

SELECT IDENTITY(int, @count, -1) AS display_id, * INTO #tmpReport11 FROM ##tbl_temp_get_data

Is it doable?

Thanks for your help.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

you'd have to use Dynamic SQL to do so.... It doesn't look like it like a variable in the Identity() call

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

You can just put the data in a table with the IDENTITY in forward order and the select in reverse order. Or, you could select using ROW_NUMBER OVER(ORDER BY).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 5

Why not just make your first SQL statement return the data?

[font="Courier New"]SELECT @sqlstmt='SELECT ROW_NUMBER() OVER (ORDER BY ' + @col_name + ') AS display_id f_name,l_name,city,state from tblx ORDER BY ' +@col_name+' '+@orderby

EXEC (@sqlstmt)[/font]

If you are using dynamic SQL, why not just go with it?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

My appologies... I missed the fact that this is for display purposes...

You don't need a TempTable nor any form of dynamic SQL for this in SQL 2005... this will do as you ask...

SELECT ROW_NUMBER OVER (ORDER BY F_Name DESC) AS Display_ID,

L_Name,

City,

State

FROM yourtable

ORDER BY F_Name DESC

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)