Solved

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

Posted on 2011-02-16
5
305 Views
Last Modified: 2012-08-13
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'
0
Comment
Question by:rgb192
5 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 167 total points
ID: 34910598
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
 
LVL 8

Expert Comment

by:ragnarok89
ID: 34910623
You want Distinct:

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

SELECT [ALL | DISTINCT] columnname1 [,columnname2]
0
 
LVL 32

Assisted Solution

by:ewangoya
ewangoya earned 167 total points
ID: 34910650
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
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 166 total points
ID: 34910849
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
 

Author Closing Comment

by:rgb192
ID: 34911067
thanks
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now