GROUP BY * ???

Question

Post reply

GROUP BY * ???

ryanm

Say Hey Kid

Points: 690
More actions
August 14, 2003 at 6:39 am

#82520

Similar to my previous post (Replace all NULLS with something), is there any way to do a GROUP BY *, instead of list all the column names?
E.G.
SELECT *
FROM table
GROUP BY *
HAVING COUNT(name) > 1
Any help is always appreciated.
Thanks,
Ryan

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

NPeeters SSChampion Points: 12541 More actions · Answer 1

So, in fact you are looking for duplicates.

You can use the CHECKSUM(*) function for that.



SELECT *

FROM (SELECT *, CHECKSUM(*) AS cs FROM table)

GROUP BY cs

HAVING COUNT(*) > 1

Don't know about performance of this though.

Edited by - NPeeters on 08/14/2003 06:51:40 AM

ryanm Say Hey Kid Points: 690 More actions · Answer 2

Thanks for your help, NPeeters.

I tried what you suggested, but keep getting an error saying Incorrect syntax near the keyword 'GROUP'.

I have tried changing things around, but still get errors.

AnzioBake SSCarpal Tunnel Points: 4134 More actions · Answer 3

Just add ' As a ' between the closing bracket and the 'group by'

SELECT *

FROM (

SELECT *, CHECKSUM(*) AS cs

FROM table

)as a

GROUP BY csHAVING COUNT(*) > 1

ryanm Say Hey Kid Points: 690 More actions · Answer 4

Thanks for your help AnzioBake.

I tried your suggestion, but I get an error stating all the columns are not contained in a GROUP BY clause.

NPeeters SSChampion Points: 12541 More actions · Answer 5

OK. Stupid me... All values in the select clause have to be contained either in an Aggregate or a group by.

So change the query to..



SELECT T.* 

FROM (SELECT *, CHECKSUM(*) cs FROM table) T

INNER JOIN 

     (SELECT CHECKSUM(*) AS cs

      FROM table

      GROUP BY cs

      HAVING COUNT(*) > 1) a

   ON T.cs = a.cs

This should return all data you need.

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 6

5409045121009-7368

SSCoach

Points: 15740

August 15, 2003 at 4:34 am

#470074

Nice generic statement, just replace the table name.

NPeeters SSChampion Points: 12541 More actions · Answer 7

I hate those SELECT * statements. I will slap my collegues around the head for using it

Well, anyway, luckily I don't think anyone is subscribed to SQLServercentral . They should though

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 8

A great fan of Joe Celko smarties.

Won't use "great solution" in production.

Agree the "Select *" will take few DBA years to be weeded out.