Solved

Need distinct SQL results as an ID list

Posted on 2009-05-13
12
1,167 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Syntax to get a value associated with most recent date 16 60
mssql 7 32
SQL Query 9 22
SQL Server 2012 and core licensing 5 22
I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

739 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