Solved

Need distinct SQL results as an ID list

Posted on 2009-05-13
12
1,127 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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 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

13 Experts available now in Live!

Get 1:1 Help Now