Setting Flag

Question

Setting Flag

gsavitha

SSC Rookie

Points: 30
More actions
March 1, 2012 at 10:08 am

#252515

Dear All,
How can i set flag 1,2 and 3 for every rows using sql query? By ordering date field either des/asc
Your help is highly appreciated.
For eg:
Id Name date flag
9278 david02/12/2012 1
4343 sharon 21/11/2012 2
4783 elizabeth 17/11/2012 3
47846 john 08/10/2012 1
78347 chris 17/09/2012 2
37379 Joe18/06/2012 3
Thnaks
Regards,
SG

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

gsavitha (3/1/2012)
Dear All,
How can i set flag 1,2 and 3 for every rows using sql query? By ordering date field either des/asc
Your help is highly appreciated.
For eg:
Id Name date flag
9278 david02/12/2012 1
4343 sharon 21/11/2012 2
4783 elizabeth 17/11/2012 3
47846 john 08/10/2012 1
78347 chris 17/09/2012 2
37379 Joe18/06/2012 3
Thnaks
Regards,
SG

Pretty sparse on details here. Are there rules about what this flag should be? Can you post ddl (create table scripts) and some sample data (insert statements). Then explain clearly what you are trying to accomplish.

I suspect your request is fairly straight forward but I can't figure out what you are trying to do.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

Please follow the link at the bottom of my signature to find how you can get better and faster answers.

But just for this case:

set dateformat dmy

declare @t table (id int, name varchar(100), somedate datetime)

insert @t values (9278,'david','02/12/2012')

,(4343,'sharon','21/11/2012')

,(4783,'elizabeth','17/11/2012')

,(47846,'john','08/10/2012')

,(78347,'chris','17/09/2012')

,(37379,'Joe','18/06/2012')

,(39999,'to make it odd','18/06/2012')

select id, name, somedate, flag from

(

select *, row_number() OVER (partition by flag order by name) ord

from

(

select *, NTILE(3) OVER (ORDER BY (SELECT NULL)) Flag

from @t

) t

) q

order by ord, flag

You can see that flag will be set from 1 to 3 and it will try to get it in even groups where possible, and in order of group of flags ...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Remember the "old" ways... one less SORT and no 3 scan worktable behind the scenes. 😉

SELECT ID, Name, SomeDate,

Flag = (ROW_NUMBER() OVER (ORDER BY SomeDate DESC)-1)%3+1

FROM @t

ORDER BY SomeDate 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)

gsavitha SSC Rookie Points: 30 More actions · Answer 4

Dera Hall of Fame

Thanks for your reply

I am planning to retervie a 3 result set from a table.1st set contaning all record in the row 1,4,7,10 and so on

2 set contaning all record in the row 2,5,8,11 and so on

3 set contaning all record in the row 3,6,9,12 and so on

For this i thought of setting flag (1,2,3,1,2,3)and then i planned to reterive the record based on flag

Thanks

SG

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

gsavitha (3/2/2012)
Dera Hall of Fame
Thanks for your reply
I am planning to retervie a 3 result set from a table.1st set contaning all record in the row 1,4,7,10 and so on
2 set contaning all record in the row 2,5,8,11 and so on
3 set contaning all record in the row 3,6,9,12 and so on
For this i thought of setting flag (1,2,3,1,2,3)and then i planned to reterive the record based on flag
Thanks
SG

No need to add a flag to your table this type of thing. Just get it on the fly however you want it.

Using Eugene's sample table and Jeff's row_number() logic above..

--set dateformat dmy --you may need to change this if your format is dmy like most americans