How I find out GROUP BY items?

[SFiend]

Hello,
how I can find out items which are in GROUP BY?

Examle:

table:
ID NAME
1 john
2 martin
3 taylor
4 john
5 martin

select *, count(*) from table group by name;

the result will be somethink like this:
ID NAME COUNT
1 john 2
2 martin 2
3 talylor 1

and now I want to find out all IDs which contains ID=1

result
ID NAME
1 john
4 john

I hope you understand me, Thanks for answer

 

[TonyGroves]

I'm sorry, I don't quite follow.

If you just want to get all the ID's from the table whose names match that for ID 1, you could use:
[tt]
SELECT t1.id,t1.name
FROM t t1 JOIN t t2 ON t1.name=t2.name AND t2.id=1
[/tt]
But I'm afraid I don't understand how that is connected with your GROUP by query.


-----
ALTER world DROP injustice, ADD peace;

 

[SFiend]

Ok, thank you. You give me an idea...

Here's the query:

SELECT Zbozi1.*
FROM Zbozi AS Zbozi1, Zbozi AS Zbozi2
WHERE Zbozi1.Id_polozky LIKE Zbozi2.Id_polozky AND Zbozi1.Poznamka LIKE Zbozi2.Poznamka AND Zbozi2.Id LIKE $Id

But, what about I want update the result?

This doesn't work

UPDATE Zbozi AS Zbozi1, Zbozi AS Zbozi2
SET Zbozi1.Datum_prodeje=$Datum_prodeje, Zbozi1.Id_prodavajiciho=$Id_prodavajiciho
WHERE Zbozi1.Id_polozky LIKE Zbozi2.Id_polozky AND Zbozi1.Poznamka LIKE Zbozi2.Poznamka AND Zbozi2.Id LIKE $Id

 

[r937]

sounds like you want to list the rows where the name occurs more than once in the table

select t1.id
     , t1.name
  from yourtable t1
inner
  join yourtable t2
    on t1.name = t2.name
group
    by t1.id
     , t1.name    
having count(*) > 1

rudy

http://r937.com/

SQL Consulting