Duplicates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Duplicates

Author

Topic

JeffT
Posting Yak Master

111 Posts

Posted - 2006-05-10 : 23:32:26

Hi,
I'll see if I can explain this clearly. The query below
selects rows from the "hdr_ctl_nbr_status" table if the value in the field "tcn" from that table is found in the table "temp_tcn". I want all fields from the "hdr_ctl_nbr_status" table to be selected BUT only one row. In other words for a tcn with a value "12345678" there are 10 rows returned from the hdr_ctl_nbr_status table, I want only 1. Is there a way I can use SELECT DISTINCT to do this ? I know this usually functions on one or more fields but I want the DISTINCT to be on tcn only BUT return all fields in the query.

Select h.*,'' from hdr_ctl_nbr_status as h WITH (NOLOCK)
where h.tcn in (select tcn from temp_tcn)

Thanks, sorry if this is too confusing. Jeff

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-05-11 : 01:55:49

Which one of the 10 do you want ?

--
This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-11 : 02:16:28

Probably you may need to use Top 1 instead of Distinct

Madhivanan

Failing to plan is Planning to fail

JeffT
Posting Yak Master

111 Posts

Posted - 2006-05-11 : 07:11:05

Thanks PSamsig and Madhivanan,

I want the first one of the 10 for each tcn (the temp_tcn table has thousands of these tcns). So, for instance, this table will have:

12345678 <= I want this one
12345678
12345678
12345678

23456789 <= I want this one
23456789
23456789
23456789
23456789

34567890 <= I want this one
34567890
etc.
I want just the first occurrence of each. Thanks, Jeff

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-11 : 07:15:56

Somthing like this ??

Select Tcn From TableName Group by Tcn

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

JeffT
Posting Yak Master

111 Posts

Posted - 2006-05-11 : 07:22:56

Thanks chiragkhabaria,

I'll try that. Being new to sql server I'll rely on your expertise !

JeffT
Posting Yak Master

111 Posts

Posted - 2006-05-11 : 07:33:25

I tried this:

Insert into temp_hdr_ctl_nbr_status
Select h.*,'' from hdr_ctl_nbr_status as h WITH (NOLOCK)
where h.tcn in (select tcn from temp_tcn)
Group by tcn

But get a list of errors referring to each field in the "hdr_ctl_nbr_status" table like this:

Column 'h.hdr_ctl_nbr' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Thanks, Jeff

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-11 : 07:50:40

oh sorry for this things you cant use group by clause..

can you post the table structure for the table hrdr_ctl_nbr_status ??

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-11 : 07:54:38

If you want to get data from other columns also then refer point 2
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan

Failing to plan is Planning to fail

JeffT
Posting Yak Master

111 Posts

Posted - 2006-05-11 : 08:04:29

no problem chiragkhabaria, here it is, thanks, Jeff:

[hdr_ctl_nbr] [char] (20)
[tcn] [char] (12)
[FILE_AUTH_NBR_837] [char]
[FILE_AUTH_NBR_835] [char] ,
[GS06GRP_CTL_NBR] [char] (9)
[ST02TXN_CTL_NBR] [varchar] (9) ,
[CLM01PT_ACCT_NBR] [varchar] (38)
[txn_start_seq_nbr] [int] NULL ,
[txn_end_seq_nbr] [int] NULL ,
[hl_start_seq_nbr] [int] NULL ,
[hl_end_seq_nbr] [int] NULL ,
[hl_b_start_seq_nbr] [int] NULL ,
[hl_b_end_seq_nbr] [int] NULL ,
[hl_c_start_seq_nbr] [int] NULL ,
[hl_c_end_seq_nbr] [int] NULL ,
[pt_start_seq_nbr] [int] NULL ,
[pt_end_seq_nbr] [int] NULL ,
[ln_start_seq_nbr] [int] NULL ,
[ln_end_seq_nbr] [int] NULL ,
[tt_start_seq_nbr] [int] NULL ,
[tt_end_seq_nbr] [int] NULL ,
[status] [varchar] (10)
[PROVIDER_NUMBER] [varchar] (30)
[PROVIDER_NAME] [varchar] (35)
[MEMBER_ID] [varchar] (80)
[CLAIM_CHARGE] [numeric](18, 2) NULL ,
[LINE_CHARGE] [numeric](18, 2) NULL ,

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-11 : 08:13:19

[code]
Insert into temp_hdr_ctl_nbr_status
Select A.* From
((Select h.*,'' from hdr_ctl_nbr_status as h
where Pk = (Select Min(Pk) From hdr_ctl_nbr_status h1 Where h1.Tcn = h.Tcn) As A
Inner Join
temp_tcn On A.Tcn = temp_tcn.TcN)

[/code]

Here Pk is the primary key column in the table.. if you dont have any primary key then i guess this method will fail..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

JeffT
Posting Yak Master

111 Posts

Posted - 2006-05-11 : 08:20:47

Thanks very much chiragkhabaria !! I will try this and let you know how things worked out...
Jeff

JeffT
Posting Yak Master

111 Posts

Posted - 2006-05-11 : 09:17:10

OK, here's what I have:

Select a.*,'' from
((Select h.*,'' from hdr_ctl_nbr_status as h
where tcn =
(Select Min(tcn) from hdr_ctl_nbr_status h1
where h1.Tcn = h.Tcn)) as a
inner join
temp_tcn on a.tcn = temp_tcn.tcn

but I'm getting this error and can't see anything wrong:
"No column was specified for column 28 of 'a'. "

The column 28 is a [type] [varchar] (10)
field appended to the file "inserted" into. I though I had that covered with the '' in the first line: Select a.*,'' from

Thanks,
Jeff

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-11 : 09:27:28

try this


Select a.*,'' from
((Select h.*,'' As Tmp  from hdr_ctl_nbr_status as h
where tcn =
(Select Min(tcn) from hdr_ctl_nbr_status h1
where h1.Tcn = h.Tcn)) as a
inner join
temp_tcn on a.tcn = temp_tcn.tcn

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

JeffT
Posting Yak Master

111 Posts

Posted - 2006-05-11 : 09:39:51

Ok, I will try that...thanks chiragkhabaria...
Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources