Solved

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

Posted on 2013-05-28
10
522 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 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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 40

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 40

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 48

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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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 extract information from SQL Server on Database, Connection and Server properties

829 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