Solved

Help with SQL Query to SELECT a DISTINCT value from a single table within multiple joins

Posted on 2013-05-28
10
535 Views
Last Modified: 2013-05-28
I have a rather involved SQL statement where I want to select only the latest distinct value of DATABASE2.ItemTag. I've mucked around with SELECT DISTINCT in various ways and I'm getting nowhere so far. Here's my SQL query:

SELECT View_DEP_ItemTags.ItemKey, View_DEP_ItemTags.ClientKey, View_DEP_ItemTags.ClientSite, View_DEP_ItemTags.ItemClass, View_DEP_ItemTags.Location, View_DEP_ItemTags.ItemStatus, View_DEP_ItemTags.Manufacturer, View_DEP_ItemTags.Model, View_DEP_ItemTags.SerialNumber, View_DEP_ItemTags.SystemName, View_DEP_ItemTags.ClientID, View_DEP_ItemTags.CompanyName, View_DEP_ItemTags.SiteName, View_DEP_ItemTags.ItemType, View_USR_UserList.FirstName, View_USR_UserList.FullName, View_USR_UserList.LastName, DATABASE2.TrackID, DATABASE2.TicketID, DATABASE2.ItemTag, DATABASE2.Notes, DATABASE2.UserKey, DATABASE2.DATABASE2Num, List_Reason.ReasonText, List_Carrier.CarrierName, DATABASE2.CarrierID, DATABASE2.ReasonID, DATABASE2.StatusID, List_Status.Status 

FROM DATABASE1.dbo.View_DEP_ItemTags AS View_DEP_ItemTags WITH (NOLOCK) 

INNER JOIN List_Reason INNER JOIN DATABASE2 ON List_Reason.ReasonID = DATABASE2.ReasonID 
INNER JOIN List_Carrier ON DATABASE2.CarrierID = List_Carrier.CarrierID 
INNER JOIN List_Status ON DATABASE2.StatusID = List_Status.StatusID 
INNER JOIN DATABASE1.dbo.View_USR_UserList AS View_USR_UserList ON DATABASE2.UserKey = View_USR_UserList.UserKey ON View_DEP_ItemTags.ItemKey = DATABASE2.ItemKey 

WHERE View_DEP_ItemTags.ClientID = 'HOS' AND View_DEP_ItemTags.ItemStatus = 'ACTIVE' ORDER BY DATABASE2.ItemTag

Open in new window

0
Comment
Question by:BigDeer
[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
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 32

Expert Comment

by:awking00
ID: 39202392
What defines "latest" value of item_tag? Can you provide some sample results from your current query and what you would like them to be?
0
 
LVL 3

Author Comment

by:BigDeer
ID: 39202456
Latest would be the most recent row containing ITEMKEY ( I see now I wrote ItemTag above)

ALong with all the other columns above I receive these results for ItemKey and ItemTag:

ItemKey      ItemTag
9375      38305
42160      44144
42160      44144
43077      47591
43077      47591
35069      50231
46840      53804

I want the results for ItemKey to be unique for ItemKey and only show the most recently entered rows that contain that ItemKey since the data prior to that would be old data associated with that particular item.

IE, user fills out the form and inserts an item that points to ItemKey 35069, I only want that newest entry to show for that item.

Thanks
0
 
LVL 32

Expert Comment

by:awking00
ID: 39202471
Is there any column that contains a date or timestamp that indicates the latest entry?
0
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
LVL 3

Author Comment

by:BigDeer
ID: 39202712
The highest PK in the table would be the latest entry. So, if DATABASE2.TrackID for 43077 is 10 and the next one is 11 TrackID of 11 would be the newest one.
0
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 39202970
try this.
SELECT * 
  FROM (SELECT View_DEP_ItemTags.ItemKey, 
               View_DEP_ItemTags.ClientKey, 
               View_DEP_ItemTags.ClientSite, 
               View_DEP_ItemTags.ItemClass, 
               View_DEP_ItemTags.Location, 
               View_DEP_ItemTags.ItemStatus, 
               View_DEP_ItemTags.Manufacturer, 
               View_DEP_ItemTags.Model, 
               View_DEP_ItemTags.SerialNumber, 
               View_DEP_ItemTags.SystemName, 
               View_DEP_ItemTags.ClientID, 
               View_DEP_ItemTags.CompanyName, 
               View_DEP_ItemTags.SiteName, 
               View_DEP_ItemTags.ItemType, 
               View_USR_UserList.FirstName, 
               View_USR_UserList.FullName, 
               View_USR_UserList.LastName, 
               DATABASE2.TrackID, 
               DATABASE2.TicketID, 
               DATABASE2.ItemTag, 
               DATABASE2.Notes, 
               DATABASE2.UserKey, 
               DATABASE2.DATABASE2Num, 
               List_Reason.ReasonText, 
               List_Carrier.CarrierName, 
               DATABASE2.CarrierID, 
               DATABASE2.ReasonID, 
               DATABASE2.StatusID, 
               List_Status.Status, 
               ROW_NUMBER() 
                 OVER ( 
                   partition BY View_DEP_ItemTags.ItemKey 
                   ORDER BY PK DESC) rn 
          FROM database1.dbo.View_DEP_ItemTags AS View_DEP_ItemTags WITH (nolock) 
               INNER JOIN List_Reason 
                          INNER JOIN DATABASE2 
                                  ON List_Reason.ReasonID = DATABASE2.ReasonID 
                          INNER JOIN List_Carrier 
                                  ON DATABASE2.CarrierID = List_Carrier.CarrierID 
                          INNER JOIN List_Status 
                                  ON DATABASE2.StatusID = List_Status.StatusID 
                          INNER JOIN database1.dbo.View_USR_UserList AS View_USR_UserList 
                                  ON DATABASE2.UserKey = View_USR_UserList.UserKey 
                       ON View_DEP_ItemTags.ItemKey = DATABASE2.ItemKey 
         WHERE View_DEP_ItemTags.ClientID = 'HOS' 
           AND View_DEP_ItemTags.ItemStatus = 'ACTIVE') t1 
 WHERE rn = 1 
 ORDER BY ItemTag 

Open in new window

0
 
LVL 3

Author Comment

by:BigDeer
ID: 39203078
Getting Could not find stored procedure 'View_DEP_ItemTags.ItemKey'
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39203088
I did not see any issue. What is your SQL Server version?
 At which line, its throwing error?
 
 I hope you have ItemKey column in database1.dbo.View_DEP_ItemTags and also the PK column.
0
 
LVL 3

Author Comment

by:BigDeer
ID: 39203212
I got it...switched View_DEP_ItemTags.ItemKey to DATABASE2.ItemKey and changed PK to DATABASE2.TrackID and it works perfectly.
0
 
LVL 3

Author Closing Comment

by:BigDeer
ID: 39203214
Thanks
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39203422
>>I've mucked around with SELECT DISTINCT in various ways and I'm getting nowhere
Really glad you got your solution here, but just on this 'select distinct' comment please note that 'distinct' works across the entire row (all values in a row of every column) to arrive at a unique row. It also does this after all other parts of the query are complete (so it can assess those whole rows) and hence it can be a considerable performance overhead as it has to sort all rows to locate duplicates.

As shown here, you wanted a "distinct result" but qualified by the condition of being the latest. Perhaps keep this in mind? "select distinct is not conditional"
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

717 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