Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
donkerr
Starting Member
2 Posts |
Posted - 2010-05-27 : 03:09:10
|
The problem is simple, I got one table which looks like thisday leva levb---------------18 A B19 A B20 B A21 A B22 A B23 A BNow I need a query which results in FROM TO leva levb------------------------------18 19 A B20 20 B A21 23 A Ba simple group by on the columns leva and levb will not work, does someone now a solution |
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2010-05-27 : 09:57:17
|
can you explain the logic of how you get your result from your sample data?http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-05-27 : 10:06:13
|
[code]select [FROM] = min(day), [TO] = max(day), leva, levbfrom onetablegroup by leva, levb[/code] KH[spoiler]Time is always against us[/spoiler] |
|
|
donkerr
Starting Member
2 Posts |
Posted - 2010-05-27 : 10:32:47
|
I already found a solution as I said before a group by over the columns leva and levb will not work because then I will not get the result as said in the table below.lev_a = lv_alloclev_b = lv_reconday = vfdtemp is the tableday leva levb---------------18 A B19 A B20 B A21 A B22 A B23 A BCREATE GLOBAL TEMPORARY TABLE ar_own.temp_result ON COMMIT PRESERVE ROWS AS select ar_own.temp_seq.nextval, vfd, lv_alloc, lv_recon from((select t1.vfd, t1.lv_alloc, t1.lv_reconfrom AR_OWN.temp t1, ar_own.temp t2where t1.lv_alloc <> t2.lv_allocand t1.lv_recon <> t2.lv_reconand t1.vfd = t2.vfd-1)union all(select t2.vfd, t2.lv_alloc, t2.lv_reconfrom AR_OWN.temp t1, ar_own.temp t2where t1.lv_alloc <> t2.lv_allocand t1.lv_recon <> t2.lv_reconand t1.vfd = t2.vfd-1)union all(select t1.vfd, t1.lv_alloc, t1.lv_reconfrom AR_OWN.temp t1where t1.vfd = (select min(vfd) from ar_own.temp))union all(select t1.vfd, t1.lv_alloc, t1.lv_reconfrom AR_OWN.temp t1where t1.vfd = (select max(vfd) from ar_own.temp))order by vfd)select t1.vfd, t2.vfd, t1.lv_alloc, t2.lv_reconfrom ar_own.temp_result t1, AR_OWN.temp_result t2where t1.lv_alloc = t2.lv_alloc and t1.lv_recon = t2.lv_recon and t1.nextval = t2.nextval -1; |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-05-27 : 10:50:44
|
Much simplerdeclare @tbl as table(day int,leva varchar(40),levb varchar(40))insert into @tblselect 18 ,'A' ,'B' union allselect 19, 'A', 'B' union allselect 20, 'B', 'A' union allselect 21, 'A', 'B' union allselect 22 ,'A' ,'B' union allselect 23, 'A', 'B'select min(day)as [from],max(day) as [to],MIN(leva)as leva,MAX(levb)as levb from(select *,ROW_NUMBER()over(order by day)-DENSE_RANK()over(partition by leva,levb order by day)as rid from @tbl)t group by rid order by min(day) PBUH |
|
|
pawankkmr
Starting Member
4 Posts |
Posted - 2015-05-05 : 07:45:29
|
declare @tbl as table(day int,leva varchar(40),levb varchar(40))insert into @tblselect 18 ,'A' ,'B' union allselect 19, 'A', 'B' union allselect 20, 'B', 'A' union allselect 21, 'A', 'B' union allselect 22 ,'A' ,'B' union allselect 23, 'A', 'B'SELECT MIN(day) [FROM] , MAX(day) [TO] , MIN(Leva) Leva , MAX(Levb) LevbFROM( SELECT * , DAY - ROW_NUMBER() OVER (PARTITION BY leva,levb ORDER BY %%Physloc%%) rnk FROM @tbl) AGROUP BY rnk ORDER BY [FROM] |
|
|
pawankkmr
Starting Member
4 Posts |
|
|
|
|
|
|