Solved

Select Distinct with mutiple columns and tables

Posted on 2010-11-17
7
433 Views
Last Modified: 2012-05-10
I am having some trouble with aggregate functions in SQL. Please take a look at my example and tell me what I am doing wrong. I am using MS SQL 2005.

Table = T_PLIST
ProdCode
------
ADC113
ADC141
ADC181


Table = T_PRODUCTS
ID      ProdCode       Location      Status
-----------------------------------------------
1      ADC131            NY            InStock
2      ADC141            CA            BackOrderd
3      ADC222            FL            InStock
4      ADC222            FL            BackOrderd
5      ADC181            PA            InStock
6      ADC181            PA            BackOrderd
7      ADC131            CA            InStock
8      ADC141            AL            InStock



What I want to see is.

ID      ProdCode       Location      Status
-----------------------------------------------
6      ADC181            PA            BackOrderd
7      ADC131            CA            InStock
8      ADC141            AL            InStock

Here's what I tried (see code)  that did not work.

Thanks



--This will not work
SELECT	T_PRODUCTS.*
FROM	T_PRODUCTS
	 INNER JOIN [T_PLIST] ON T_PRODUCTS.ProdCode = [T_PLIST].ProdCode

WHERE (SELECT DISTINCT T_PRODUCTS.ProdCode FROM T_PRODUCTS)
ORDER BY T_PRODUCTS.ProdCode, T_PRODUCTS.ID DESC

Open in new window

0
Comment
Question by:strider814
[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
  • 4
  • 3
7 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34160828
Question is, what do you want to see, just the latest ID?

select P.ID, T.ProdCode, P.Location, P.Status
from T_PLIST T LEFT JOIN (
select p.ID, p.ProdCode, p.Location, p.Status, rn=row_number() over (partition by p.ProdCode order by p.ID desc) P on P.ProdCode=T.ProdCode and P.rn=1
0
 

Author Comment

by:strider814
ID: 34160836
Yes just the latest ID
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34160898
Okay, fixed here.

select P.ID, T.ProdCode, P.Location, P.Status
from T_PLIST T LEFT JOIN (
select p.ID, p.ProdCode, p.Location, p.Status, rn=row_number() over (partition by p.ProdCode order by p.ID desc) from t_PRODUCTS) P on P.ProdCode=T.ProdCode and P.rn=1
0
AWS Certified Solutions Architect - Associate

This course has been developed to provide you with the requisite knowledge to not only pass the AWS CSA certification exam but also gain the hands-on experience required to become a qualified AWS Solutions architect working in a real-world environment.

 

Author Comment

by:strider814
ID: 34161055
This is really close but I get back some rows that are completely NULL. Is there anyway to eliminate them? I should also tell you that I want to SELECT INTO a new table if possible

Thanks for your help.
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 34161163
select P.ID, T.ProdCode, P.Location, P.Status
INTO newTableName
from T_PLIST T INNER JOIN (
select p.ID, p.ProdCode, p.Location, p.Status, rn=row_number() over (partition by p.ProdCode order by p.ID desc) from t_PRODUCTS) P on P.ProdCode=T.ProdCode and P.rn=1
0
 

Author Comment

by:strider814
ID: 34161179
I got what i wanted with an INNER JOIN. Thank You very much!

I rewrote the query a little to help newbie DBA's like myself understand it.

 Thank You very much!
SELECT Products.ID, Products.ProdCode,
 Products.Location, Products.Status
FROM Plist INNER JOIN
  (
  SELECT Products.ID, Products.ProdCode,Products.Location, Products.Status,
  rn=ROW_NUMBER() OVER (PARTITION BY Products.ProdCode ORDER BY Products.ID DESC)
  FROM Products
  ) Products ON Plist.ProdCode = Products.ProdCode AND Products.rn=1

Open in new window

0
 

Author Closing Comment

by:strider814
ID: 34161182
Thanks
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

617 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