Need distinct SQL results as an ID list

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

AxiServicesAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Anthony PerkinsConnect With a Mentor Commented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
bokistCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
AxiServicesAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
AxiServicesAuthor Commented:

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
 
Anthony PerkinsCommented:
>>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
 
AxiServicesAuthor Commented:
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
 
AxiServicesAuthor Commented:
FYI
-------------------------------------------
Msg 8117, Level 16, State 1, Line 1
Operand data type uniqueidentifier is invalid for min operator.
0
 
AxiServicesAuthor Commented:
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
 
Anthony PerkinsCommented:
>>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
 
AxiServicesAuthor Commented:
ahh, so i am casting it to varchar to use min/max then recasting it back to uniqueidentifier for printing.  Makes sense.  Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.