Solved

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

Posted on 2013-05-28
10
514 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
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
Is there any column that contains a date or timestamp that indicates the latest entry?
0
 
LVL 3

Author Comment

by:BigDeer
Comment Utility
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 40

Accepted Solution

by:
Sharath earned 500 total points
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 3

Author Comment

by:BigDeer
Comment Utility
Getting Could not find stored procedure 'View_DEP_ItemTags.ItemKey'
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
>>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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

763 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

12 Experts available now in Live!

Get 1:1 Help Now