need help in having clause

Question

Post reply

need help in having clause

crazy4sql

SSCoach

Points: 19590
More actions
September 29, 2010 at 2:42 am

#228853

I am having a table where there are duplicate record and some are unique record. The record filter are based on three columns and I am able to fetch the filtered records using groupby and having clause.
But problem is I want to see all the columns while my having clause can only show me those columns which I am using in filter.
Can someone suggest how can I all the columns which are filtered using having clause?.
My query is :-
select count(*) as cnt, column1,column2,column3 from tablename group by (column1,column2,column3 ) having count(*) > 1
and I have 12 other columns which I am not able to see by this.
----------
Ashish
September 29, 2010 at 4:45 am

This was removed by the editor as SPAM
September 29, 2010 at 5:53 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

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

crazy4sql SSCoach Points: 19590 More actions · Answer 1

Thanks it worked. Though was giving error 'Incorrect syntax near ','.'

but sorted it by removing the breacket in

group by (.......,.....,...).

Thanks again for help.

Can we use the same method if we compare two table having same two column and then select the record having count(*)>1 from first table.

so like table 1 have

col1,col2,col3,col4,col5,col10 (col1 is PK auto increment)

and table 2 have

col2,col3, col7,col8,col5,col10

then select only those records in table2 which have more than one occurence for

table1.col2 = table2.col2

and table1.col3 = table2.col3

----------
Ashish

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

Ashish, i would suggest you read YOUR signature line to get the best help from the forumites :w00t:

crazy4sql SSCoach Points: 19590 More actions · Answer 3

Thanks a lot Stew

Ashish, i would suggest you read YOUR signature line to get the best help from the forumites :w00t:

Actually I cant read my signature while posting the queries 😀 😀 😀

It was kind of dynamic request which can be created in every environment without kowing the table definition and data in table

----------
Ashish

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Here's another way, less cluttered. As always, check performance before deciding which solution to use.

; WITH PartitionedData AS (

SELECT ElementCount = COUNT(*) OVER(PARTITION BY column1, column2, column3),

* -- use a column list in preference to this

FROM [tablename]

) SELECT * FROM PartitionedData WHERE ElementCount > 1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden