• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 553
  • Last Modified:

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

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
BigDeer
Asked:
BigDeer
  • 5
  • 2
  • 2
  • +1
1 Solution
 
awking00Commented:
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
 
BigDeerAuthor Commented:
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
 
awking00Commented:
Is there any column that contains a date or timestamp that indicates the latest entry?
0
Industry Leaders: 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!

 
BigDeerAuthor Commented:
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
 
SharathData EngineerCommented:
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
 
BigDeerAuthor Commented:
Getting Could not find stored procedure 'View_DEP_ItemTags.ItemKey'
0
 
SharathData EngineerCommented:
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
 
BigDeerAuthor Commented:
I got it...switched View_DEP_ItemTags.ItemKey to DATABASE2.ItemKey and changed PK to DATABASE2.TrackID and it works perfectly.
0
 
BigDeerAuthor Commented:
Thanks
0
 
PortletPaulCommented:
>>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

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now