September 23, 2015 at 7:52 pm
I have a table with the following data:
ID, value
1111,14.0
1111,14.0
2222,13.0
2222,13.0
3333,15.0
3333,11.0
4444,12.0
4444,12.0
I'm having trouble with a query that will only return id and value where the value is not the same for both same IDs (such as ID 3333). Suggestions greatly appreciated.
September 23, 2015 at 9:06 pm
This can certainly be performance tuned but will work:
-- easily consumable sample data
DECLARE @table TABLE(ID int, value decimal (3,1))
INSERT @table
SELECT 1111,14.0 UNION ALL
SELECT 1111,14.0 UNION ALL
SELECT 2222,13.0 UNION ALL
SELECT 2222,13.0 UNION ALL
SELECT 3333,15.0 UNION ALL
SELECT 3333,11.0 UNION ALL
SELECT 4444,12.0 UNION ALL
SELECT 4444,12.0
-- solution
SELECT DISTINCT ID
FROM
(
SELECT ID, c = COUNT(*)
FROM @table
GROUP BY ID, value
) X
WHERE c = 1;
-- Itzik Ben-Gan 2001
September 23, 2015 at 10:31 pm
Alan.B (9/23/2015)
This can certainly be performance tuned but will work:
Quick thought, the single most important tuning tip here is to eliminate the sort operator by supplying a covering index that satisfies the sort order as any deduplication operation such as distinct and group by requires a sorted input.
😎
September 24, 2015 at 4:52 am
I need to complicate this a bit more accurately to the data. Also assume that there are sometimes, only single entries existing already......and I do NOT want them to be returned in the query. Based on the below, the only returned value should be 3333
1111,14.0
1111,14.0
2222,13.0
2222,13.0
3333,15.0
3333,11.0
4444,12.0
4444,12.0
5555,11.0
6666,12.0
September 24, 2015 at 7:18 am
martyres (9/24/2015)
I need to complicate this a bit more accurately to the data. Also assume that there are sometimes, only single entries existing already......and I do NOT want them to be returned in the query. Based on the below, the only returned value should be 33331111,14.0
1111,14.0
2222,13.0
2222,13.0
3333,15.0
3333,11.0
4444,12.0
4444,12.0
5555,11.0
6666,12.0
I'm not at a PC at the moment but it's easy. Just do a group by for id's HAVING a count > 1. Put that in a sub query and query the sub query using the logic I already posted.
-- Itzik Ben-Gan 2001
September 24, 2015 at 7:44 am
select ID
FROM #table
group by ID
having count(distinct value)>1;
Oops misread the requirement
select base.ID,base.value
from #table base
join (
select grp.ID
FROM #table grp
group by grp.ID
having count(distinct grp.value)>1
) mto -- More Than One
on mto.ID = base.ID;
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);September 24, 2015 at 7:58 am
Another option...
DECLARE @table TABLE(ID int, value decimal (3,1))
INSERT @table
SELECT 1111,14.0 UNION ALL
SELECT 1111,14.0 UNION ALL
SELECT 2222,13.0 UNION ALL
SELECT 2222,13.0 UNION ALL
SELECT 3333,15.0 UNION ALL
SELECT 3333,11.0 UNION ALL
SELECT 4444,12.0 UNION ALL
SELECT 4444,12.0;
SELECT
t.ID
FROM
@table t
GROUP BY
t.ID
HAVING
MIN(t.value) <> MAX(t.value)
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply