September 13, 2011 at 8:28 am
I have a problem that I don't think should be too difficult I just can't think of an easy solution...
I have a table ...
CREATE TABLE #tmp1(
[field1] [varchar](10) NULL,
[field2] [varchar](10) NULL
)
insert into #tmp1 values('test1','test2' )
insert into #tmp1 values('test3','test4' )
insert into #tmp1 values('test5','test6' )
insert into #tmp1 values('test1','test2' )
what I want is to have a select statement that splits the results...
so the select would return something like this...
groupa|test1|test2
groupb|test3|test4
groupa|test5|test6
groupb|test1|test2
September 13, 2011 at 8:36 am
Could do with a way to order the results. Does this work for you?
;WITH addID AS (
SELECT field1, field2, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn
FROM #tmp1)
SELECT CASE WHEN rn%2=0 THEN 'groupb' ELSE 'groupa' END,
field1, field2
FROM addID
September 13, 2011 at 9:09 am
Row_Number() is the most likely solution, as already mentioned.
However, it won't be deterministic if you don't have something in the table that it can Order By. You might get different results on subsequent calls of the query, or might not, and you can't predict which, unless you can order by some value in the table. Can you?
- 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
September 13, 2011 at 9:11 am
Thank you, I knew it had to be something easy, but my brain just was drawing a blank 🙂
September 13, 2011 at 9:20 am
I actually just need to evenly split the results evenly between groups and the ROW_NUMBER() works perfectly. thank you for your help
September 13, 2011 at 9:23 am
wburnett_1 (9/13/2011)
Thank you, I knew it had to be something easy, but my brain just was drawing a blank 🙂
Beware, as I alluded to and GSquared explained, without an actual ORDER to the results, the query I have provided may give inconsistent results. You need something to actually order the results by, SQL Server doesn't guarantee ordering unless an ORDER BY is specified (note that I've ordered by "SELECT NULL", which essentially means I'm ordering the data by any order, which means the result could change).
September 13, 2011 at 9:52 am
Understood, that actually works better for this case, in that it should be more like drawing names from a hat...
September 13, 2011 at 10:03 am
wburnett_1 (9/13/2011)
Understood, that actually works better for this case, in that it should be more like drawing names from a hat...
Look up NTILE in BOL, this is what it's for.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 13, 2011 at 10:56 am
wburnett_1 (9/13/2011)
Understood, that actually works better for this case, in that it should be more like drawing names from a hat...
Not really, most of the time the order will be consistent but there is no guarantee. If you want a more random order you could order by NEWID(). It is not totally random but it will be much more random than the previous way.
_______________________________________________________________
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/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply