Opposite of Distinct - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Opposite of Distinct

Author

Topic

duf
Starting Member

39 Posts

Posted - 2012-07-01 : 08:53:37

I want to have ID of all the same record but i do not know why the result contains only one record. '3'

ID|KOD|KRAJ
1 601 D
2 502 D
3 601 D
4 601 D

SELECT ID FROM FVPOZ WHERE ID IN (SELECT COUNT(*) FROM FVPOZ WHERE FVNAG=1 GROUP BY KOD, KRAJ HAVING COUNT(*) > 1);

How to get the ID value of the same records?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-01 : 10:40:15

select t1.ID
from FVPOZ as t1
join
(
SELECT KOD, KRAJ
FROM FVPOZ
WHERE FVNAG=1
GROUP BY KOD, KRAJ
HAVING COUNT(*) > 1
)dt
on dt.KOD = t1.KOD and dt.KRAJ = t1.KRAJ

No, you're never too old to Yak'n'Roll if you're too young to die.

duf
Starting Member

39 Posts

Posted - 2012-07-01 : 10:42:00

quote:

Originally posted by sunitabeck

SELECT ID FROM FVPOZ WHERE ID IN (SELECT ID FROM FVPOZ WHERE FVNAG=1 GROUP BY KOD, KRAJ HAVING COUNT(*) > 1);

In response i get only a single record '1'

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-01 : 10:42:13

Sorry about that duf and Thanks Fred! I blew away the totally useless query that I had posted. Must be my Sunday morning blues

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-01 : 10:44:34

quote:
Hi Sunita you have missed that you can't have ID in the select list without aggregat function.

Shh.... Did you have to say it out loud that I really did that!?!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-01 : 10:45:30

This is turning into a funny thread with people deleting replies and then those showing up in quoted replies. Fred, stop typing, I promise to do the same!!!

duf
Starting Member

39 Posts

Posted - 2012-07-01 : 10:47:20

quote:
Originally posted by webfred

select t1.ID
from FVPOZ as t1
join
(
SELECT KOD, KRAJ
FROM FVPOZ
WHERE FVNAG=1
GROUP BY KOD, KRAJ
HAVING COUNT(*) > 1
)dt
on dt.KOD = t1.KOD and dt.KRAJ = t1.KRAJ

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks, that works! But can you explain me what are these chars: t1, dt?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-01 : 10:49:32

t1 is an alias name for FVPOZ
dt is an alias name for my derived table in parentheses

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources