Solved

Need distinct SQL results as an ID list

Posted on 2009-05-13
12
1,147 Views
Last Modified: 2012-05-06
I query Bloomberg for a security master every morning, but bc i am getting my list from different sources there are dups.  The problem is that some columns have live data so its different and the inserted date is differnt also due to the few seconds between inserts (sql function to getdate).  The problem is, i need the ID column also (which is a row id), but the group by wont work if i add it.  How do i get just the list of IDs that are from the results returned from the query below?

SELECT count(ticker)
      ,[NAME]
      ,[TICKER]
      ,[ID_ISIN]      
      ,[ID_SEDOL1]
      ,[SEDOL]
      ,[TICKER_AND_EXCH_CODE]
      ,[ID_CUSIP]
      ,[EXCH_CODE]
      ,[EQY_PRIM_EXCH]
      
   FROM [ETF_Baskets].[dbo].[REF_BDL1]
 
group by  [NAME]
      ,[TICKER]
      ,[ID_ISIN]      
      ,[ID_SEDOL1]
      ,[SEDOL]
      ,[TICKER_AND_EXCH_CODE]
      ,[ID_CUSIP]
      ,[EXCH_CODE]
      ,[EQY_PRIM_EXCH]

Open in new window

0
Comment
Question by:AxiServices
12 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24380579
Can  you provide the sample result set of your current query along with your expected result set so that I can help you out
0
 
LVL 6

Expert Comment

by:bokist
ID: 24382462
I don't see ID_FIELD between fields, anyway I would do it in 3 steps with temporary table
assuming you have datetime field(which is unique) in group by clause.
something like this :

SELECT count(ticker)
      ,[NAME]
      ,[TICKER]
      ,[ID_ISIN]      
      ,[ID_SEDOL1]
      ,[SEDOL]
      ,[TICKER_AND_EXCH_CODE]
      ,[ID_CUSIP]
      ,[EXCH_CODE]
      ,[EQY_PRIM_EXCH], id_nr=0

   into #temp_table
     
 FROM [ETF_Baskets].[dbo].[REF_BDL1]  
 
group by  [NAME]
      ,[TICKER]
      ,[ID_ISIN]      
      ,[ID_SEDOL1]
      ,[SEDOL]
      ,[TICKER_AND_EXCH_CODE]
      ,[ID_CUSIP]
      ,[EXCH_CODE]
      ,[EQY_PRIM_EXCH]

update #temp_table set id_nr=ID_FIELD
  from FROM [ETF_Baskets].[dbo].[REF_BDL1]  
where [ETF_Baskets].[dbo].[REF_BDL1].date_time_field = #temp_table.date_time_field

select * from #temp_table

0
 

Author Comment

by:AxiServices
ID: 24389766
not sure how that works exactly, but the id (field) you mentioned is not in the query, bc then the results are not distinct as it is a unique value.  Basically if tom was in the DB twice and each had a different id, how could i get it to return one tom row and its id (i dont care which forst or second).
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24390885
Try it this way:

SELECT      count(ticker)
            ,MIN(ID) ID                  -- Add this
            ,[NAME]
            ,[TICKER]
            ,[ID_ISIN]      
            ,[ID_SEDOL1]
            ,[SEDOL]
            ,[TICKER_AND_EXCH_CODE]
            ,[ID_CUSIP]
            ,[EXCH_CODE]
            ,[EQY_PRIM_EXCH]
     
FROM [ETF_Baskets].[dbo].[REF_BDL1]
group by  
            [NAME]
            ,[TICKER]
            ,[ID_ISIN]      
            ,[ID_SEDOL1]
            ,[SEDOL]
            ,[TICKER_AND_EXCH_CODE]
            ,[ID_CUSIP]
            ,[EXCH_CODE]
            ,[EQY_PRIM_EXCH]
0
 

Author Comment

by:AxiServices
ID: 24391005

Will try it,  but think I did already.  I may have not mentioned that the I'd field is a GUID uniqu identifer which can't be sorted so min may fail.  Will post results.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24391082
>>I may have not mentioned that the I'd field is a GUID uniqu identifer which can't be sorted so min may fail. <<
Huh?  Check this out and become a believer:

Declare @Temp table (MyGUID uniqueidentifier)

SET NOCOUNT ON
Insert @Temp (MyGUID) VALUES (NEWID())
Insert @Temp (MyGUID) VALUES (NEWID())
Insert @Temp (MyGUID) VALUES (NEWID())
Insert @Temp (MyGUID) VALUES (NEWID())
Insert @Temp (MyGUID) VALUES (NEWID())
Insert @Temp (MyGUID) VALUES (NEWID())
Insert @Temp (MyGUID) VALUES (NEWID())
Insert @Temp (MyGUID) VALUES (NEWID())
Insert @Temp (MyGUID) VALUES (NEWID())
Insert @Temp (MyGUID) VALUES (NEWID())
Insert @Temp (MyGUID) VALUES (NEWID())
Insert @Temp (MyGUID) VALUES (NEWID())
Insert @Temp (MyGUID) VALUES (NEWID())
Insert @Temp (MyGUID) VALUES (NEWID())
Insert @Temp (MyGUID) VALUES (NEWID())

Select MyGuid
From @Temp
Order By MyGuid


0
 

Author Comment

by:AxiServices
ID: 24397356
hey, im not against it it just didnt work the first time i tried it and i was told it had to be a number.  Will try this today and post teh results.  Thanks.
0
 

Author Comment

by:AxiServices
ID: 24397376
FYI
-------------------------------------------
Msg 8117, Level 16, State 1, Line 1
Operand data type uniqueidentifier is invalid for min operator.
0
 

Author Comment

by:AxiServices
ID: 24398082
Check it out

select [name]  
            ,[TICKER]
            ,[ID_ISIN]      
            ,[ID_SEDOL1]
            ,[SEDOL]
            ,[TICKER_AND_EXCH_CODE]
            ,[ID_CUSIP]
            ,[EXCH_CODE]
            ,[EQY_PRIM_EXCH]
, max( cast(id as varchar(50)) ) 'id'
from [ETF_Baskets].[dbo].[REF_BDL1]
group by [name]
            ,[TICKER]
            ,[ID_ISIN]      
            ,[ID_SEDOL1]
            ,[SEDOL]
            ,[TICKER_AND_EXCH_CODE]
            ,[ID_CUSIP]
            ,[EXCH_CODE]
            ,[EQY_PRIM_EXCH]
order by [name]
            ,[TICKER]
            ,[ID_ISIN]      
            ,[ID_SEDOL1]
            ,[SEDOL]
            ,[TICKER_AND_EXCH_CODE]
            ,[ID_CUSIP]
            ,[EXCH_CODE]
            ,[EQY_PRIM_EXCH]

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24398176
>>Msg 8117, Level 16, State 1, Line 1
Operand data type uniqueidentifier is invalid for min operator.
<<
You are absolutely right.  I stand corrected.

0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 24398225
That will work.  You can also go one step further:

select [name]  
            ,[TICKER]
            ,[ID_ISIN]      
            ,[ID_SEDOL1]
            ,[SEDOL]
            ,[TICKER_AND_EXCH_CODE]
            ,[ID_CUSIP]
            ,[EXCH_CODE]
            ,[EQY_PRIM_EXCH]
, CAST(max( cast(id as varchar(50)) ) As uniqueidentifier) 'id'
from [ETF_Baskets].[dbo].[REF_BDL1]
group by [name]
            ,[TICKER]
            ,[ID_ISIN]      
            ,[ID_SEDOL1]
            ,[SEDOL]
            ,[TICKER_AND_EXCH_CODE]
            ,[ID_CUSIP]
            ,[EXCH_CODE]
            ,[EQY_PRIM_EXCH]
order by [name]
            ,[TICKER]
            ,[ID_ISIN]      
            ,[ID_SEDOL1]
            ,[SEDOL]
            ,[TICKER_AND_EXCH_CODE]
            ,[ID_CUSIP]
            ,[EXCH_CODE]
            ,[EQY_PRIM_EXCH]
0
 

Author Comment

by:AxiServices
ID: 24398300
ahh, so i am casting it to varchar to use min/max then recasting it back to uniqueidentifier for printing.  Makes sense.  Thanks.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

772 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