Solved

Need distinct SQL results as an ID list

Posted on 2009-05-13
12
1,138 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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 UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

912 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

19 Experts available now in Live!

Get 1:1 Help Now