extract non-unique records from a table

Can anyone pls help me with any SQL syntax / logic of extracting only the
non-unique records from an SQL table ?
Thanks
ShekharIf i understand correctly you're after a query that returns the duplicates.
If so give this a try :-
SELECT Col001, COUNT(*) FROM tablename
GROUP BY Col001
HAVING COUNT(*) > 1
This will return any Col001 that are duplicate
HTH. Ryan
"Shekhar Gupta" <ShekharGupta@.discussions.microsoft.com> wrote in message
news:38DC7E2D-051B-4573-9C9A-5B6164F99E78@.microsoft.com...
> Can anyone pls help me with any SQL syntax / logic of extracting only the
> non-unique records from an SQL table ?
> Thanks
> Shekhar|||gr8, Thanks Ryan, this worked
shekhar
"Ryan" wrote:

> If i understand correctly you're after a query that returns the duplicates
.
> If so give this a try :-
> SELECT Col001, COUNT(*) FROM tablename
> GROUP BY Col001
> HAVING COUNT(*) > 1
> This will return any Col001 that are duplicate
> --
> HTH. Ryan
>
> "Shekhar Gupta" <ShekharGupta@.discussions.microsoft.com> wrote in message
> news:38DC7E2D-051B-4573-9C9A-5B6164F99E78@.microsoft.com...
>
>

Extract IBM UniData (Pick database) data into SQL Server

Good afternoon SQL dudes

Does anyone have any experience of extracting data from IBM's UniData (http://en.wikipedia.org/wiki/UniData) (or any post-relational\ Pick\ nested relational\ multi-valued relational database) into a SQL Server?
More info here (http://www.rpbourret.com/xml/ProdsXMLEnabled.htm), here (http://www.pick-ware.co.uk/) and here (http://en.wikipedia.org/wiki/Pick_operating_system)

I don't (which is why I am asking) but I could imagine it being a right bugger. No need for detailed or technical info - I have no more info at this stage - just wondered if anyone has any similar experience.

Super duper, thank you SQL troopers :)Probably gonna show my age now :/ its been a while since i have done anything in Pick (over 10 years)

If tools dont already exist to do the export the easies way would be to write a small program in PickBasic that just displayed the data to screen, and a small app that runs on the PC that initates the Pick Program, and then just captures to output to a file (from what i remember alot of pc based terminal emulation programs already do that)

Just remember to calculate a checksum for the data, then re-calculate it from the captured files.

If its UniData then there are ODBC drivers available that will let you connect to the data directly.

Sorry cant be more helpfull, its been a while since i have used that technology, although i must admit, i loved working in that environment xD|||Thanks Mark - appreciated.

Checksum is a good idea.

extract distinct information and order the results

Hi,

MSSQL 2000 T-SQL

I have a problem in extracting information pertaing to a key value and matching that key value to another transaction but the order is based on another value in the same row.

I've attached a sample of DB data below.

tran_nr ret_ref_no msg_type description

5111 12345 420 reversal
5112 12345 200 auths
5113 15236 200 auths
5114 46587 200 auths
5115 46587 420 reversal

Requirement using the above data is to extract data where the ret_ref_no is the same for more than one row but also check that the msg_type 420 happens before the 200. Is there a way of retrieving the information in this way using the tran_nr coloumn values? The tran_nr values is basically the serial number when the transaction is wrriten away to the DB.

I've managed only to retrive the 1st half of my query whereby the same ret_ref_nr is being used by more then one transaction. Still need to figure out the 2nd part where the msg_type of 420 happens before the 200.

SELECT * FROM SAMPLE

WHERE ret_ref_no in
(
SELECT ret_ref_no FROM SAMPLE
GROUP BY ret_ref_no HAVING COUNT(*) > 1
)

Results of query

5111 12345 420 reversal
5112 12345 200 auths
5114 46587 200 auths
5115 46587 420 reversal

If someone could assist with only retreiving the above results in bold to the query analyser i will really appreciate it.

Regards

DeceptiveThe requirement "msg_type 420 happens before the 200" can be interpreted in more than one way. Did you mean that both 420 and 200 must exist and 420 must have a lower tran_nr. Or did you mean that if 200 exists for ret_ref_no , then it must be rpecede by 420?|||

Hi AKuz,

Your first thought was correct. Thats exactly what i require.

The requirement "msg_type 420 happens before the 200" can be interpreted in more than one way. Did you mean that

both 420 and 200 must exist and 420 must have a lower tran_nr.

Deceptive

|||

The possible query,

Assumption : The tran_nr are inserted in sequence. Suggestion, instead of using the id use the timestamp.

Code Snippet

Create Table #data (

[tran_nr] int ,

[ret_ref_no] int ,

[msg_type] int ,

[description] Varchar(100)

);

Insert Into #data Values('5111','12345','420','reversal');

Insert Into #data Values('5112','12345','200','auths');

Insert Into #data Values('5113','15236','200','auths');

Insert Into #data Values('5114','46587','200','auths');

Insert Into #data Values('5115','46587','420','reversal');

select * from #data main

where exists

(

select l.ret_ref_no from #data l

inner join #data r

on l.ret_ref_no = r.ret_ref_no

and l.tran_nr < r.tran_nr

where

l.msg_type='420'

and r.msg_type = '200'

and main.ret_ref_no = l.ret_ref_no

)

|||

Here are two more possible solutions. Thanks to Manivannan for the DDL and sample data.

Code Snippet

select

*

from

#data as a

where

exists (

select

*

from

#data as b

where

b.[ret_ref_no] = a.[ret_ref_no]

and b.[tran_nr] < a.[tran_nr]

and b.[msg_type] = 420 and a.[msg_type] = 200

)

or exists (

select

*

from

#data as b

where

b.[ret_ref_no] = a.[ret_ref_no]

and b.[tran_nr] > a.[tran_nr]

and b.[msg_type] = 200 and a.[msg_type] = 420

)

select

*

from

#data

where

[ret_ref_no] in (

select

a.[ret_ref_no]

from

#data as a

where

a.[msg_type] in (200, 420)

group by

a.[ret_ref_no]

having

min(case when a.[msg_type] = 200 then a.[tran_nr] end) > min(case when a.[msg_type] = 420 then a.[tran_nr] end)

)

AMB

|||Thanks Manivannan.D.Sekaran for your assistance.|||Thank you too hunchback |||

hi Manivannan

Looks like i have another issue that i need help with. I've i add a coloumn rsp_code to the database. This coloumn will contain two degit response codes consisting of values like 00, 25,91,01,05 etc. Each transaction will have its own rsp_code value.

Question:-

Together with the SQL already provided is it posible to only retrive the records where the rsp_code = '25' for the 420 and the rsp_code = '00' for the 200 transactions?

Thanks

Deceptive

extract data from sqlserver

hi all
how can I go about extracting data from a sql database and piping to a file,
lets say .csv format.... any ideas appreciated
thnx
tmWhile this is possible with Reporting Services (using the file share
delivery extension), you might look at using a tool like DTS.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"tmmm" <a@.a.com> wrote in message
news:eSzJTPFVEHA.2564@.TK2MSFTNGP11.phx.gbl...
> hi all
> how can I go about extracting data from a sql database and piping to a
file,
> lets say .csv format.... any ideas appreciated
> thnx
> tm
>