I only want the orderids where there is only one row and resultcode='unconfirmed'

CREATE TABLE [dbo].[payments](
      [paymentid] [int] IDENTITY(1,1) NOT NULL,
      [orderid] [int] NOT NULL,
      [dateentered] [datetime] NULL,
      [datebilled] [datetime] NULL,
      [type] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [number] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [expiration] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [CV2] [int] NOT NULL,
      [confirmation] [varchar](70) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [resultcode] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [debit] [money] NOT NULL,
      [paymentinfo] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ebayquantity] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [paymentfee] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS 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





there may be many rows for one orderid(int)
I only want the orderids where there is only one row and resultcode='unconfirmed'
LVL 1
rgb192Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
select t.*
  from yourtable t
 where t.resultcode = 'unconfirmed'
   and not exists ( select null from yourtable o where o.orderid = t.orderid and o.paymentid <> t.paymentid )
0
 
ragnarok89Commented:
You want Distinct:

DISTINCT - Used to select unique records. Only unique values are returned.

SELECT [ALL | DISTINCT] columnname1 [,columnname2]
0
 
Ephraim WangoyaConnect With a Mentor Commented:
try

select *
from payments A
where resultcode='unconfirmed'
and exists (select 1      
              FROM PAYMENTS B
              where B.orderid = A.orderid
              GROUP BY ORDERID 
              HAVING COUNT(ORDERID) = 1)

Open in new window

0
 
SharathConnect With a Mentor Data EngineerCommented:
select *
  from (
select *,count(OrderID) over (partition by OrderID) cnt 
 from your_table) t1
where resultcode='unconfirmed' and cnt = 1

Open in new window

0
 
rgb192Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.