Simple query?!
-
It seems to be a simple problem but it keeps giving horrible queries with subqueries and a lot of group by as the solution...
id nr text val1
1 1 aa A
2 1 bb A
3 2 cc B
4 3 aa C
5 3 ab D
6 4 cd E
7 4 rf F
8 4 rt F
9 4 qq G
How to get only the (complete) rows with the following conditions: nr stays the same but with different (and more than one) val1 value? (the result is the rows with id 4, 5, 6, 7 and 9).
Suggestions are very welcome!
-
Subqueries don't make queries horrible 😉 How have you approached the problem?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
Please read the first article in my signature block about asking for help. Follow the instructions in that article on what and how to post to get the best help quickly.
Based on your initial post, I really don't even know where to start in helping you.
-
Here's what I could come up with:
create table #T (ID int primary key,
NR int,
Txt char(2),
Val1 char(1));
insert into #T (ID, NR, Txt, Val1)
select 1 ,1 ,'aa' ,'A' union all
select 2 ,1 ,'bb' ,'A' union all
select 3, 2 ,'cc' ,'B' union all
select 4 ,3 ,'aa' ,'C' union all
select 5 ,3 ,'ab' ,'D' union all
select 6 ,4 ,'cd' ,'E' union all
select 7 ,4 ,'rf' ,'F' union all
select 8 ,4 ,'rt' ,'F' union all
select 9, 4 ,'qq' ,'G';
;with CTE as
(select row_number() over (partition by NR order by ID) as Row,
*
from #T)
select *
from CTE
where exists
(select *
from CTE CTE2
where NR = CTE.NR
and Val1 != CTE.Val1
and Row in (CTE.Row - 1, 2));
It's going to have to have a few sub-queries, because SQL table rows don't have an inherent order to them. That's not a bad thing, it's just how relational data works.
- 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
-
Another way
WITH CTE AS (SELECT ID, NR, Txt, Val1,
ROW_NUMBER() OVER(PARTITION BY NR,Val1 ORDER BY ID) AS rn,
MAX(Val1) OVER(PARTITION BY NR) AS mx,
MIN(Val1) OVER(PARTITION BY NR) AS mn
FROM #T)
SELECT ID, NR, Txt, Val1
FROM CTE
WHERE mxmn AND rn=1
ORDER BY ID
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply