where count of 2 columns is greater than 1

rgb192
rgb192 used Ask the Experts™
on
CREATE TABLE [dbo].[payments](
      [paymentid] [int] IDENTITY(1,1) NOT NULL,
      [orderid] [int] NOT NULL,
      [dateentered] [datetime] NULL,
      [datebilled] [datetime] NULL,
      [type] [varchar](50) NOT NULL,
      [number] [varchar](80) NOT NULL,
      [expiration] [varchar](10) NULL,
      [CV2] [int] NOT NULL,
      [confirmation] [varchar](70) NOT NULL,
      [resultcode] [varchar](40) NOT NULL,
      [debit] [money] NOT NULL,
      [paymentinfo] [text] NULL,
 CONSTRAINT [PK_payments] PRIMARY KEY CLUSTERED
(
      [paymentid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF



select * from payments where orderid (int) count>1 and number(varchar) count >1

sample data
orderid     number
12            56
12            56
13            6
14            54
14            55

result
orderid number
12        56
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

SELECT T1.*
FROM payments T1
WHERE EXISTS (SELECT 1 FROM payments
              WHERE T1.orderid=orderid
                    AND T1.number=number
                    AND T1.paymentid<>paymentid
             )

Open in new window

If you only want the orderid and number returned, then this will do
SELECT orderid, number
FROM @t 
GROUP BY orderid, number
HAVING COUNT(1) > 1

Open in new window

Author

Commented:
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial