Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-05-28
10
Medium Priority
?
546 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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 2000 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

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!

Question has a verified solution.

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

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…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

609 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