Select Distinct with mutiple columns and tables

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

strider814Asked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
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
 
cyberkiwiCommented:
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
 
strider814Author Commented:
Yes just the latest ID
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
cyberkiwiCommented:
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
 
strider814Author Commented:
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
 
strider814Author Commented:
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
 
strider814Author Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.