Solved

Select Distinct with mutiple columns and tables

Posted on 2010-11-17
7
426 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
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 

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

Technology Partners: 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

Suggested Solutions

Title # Comments Views Activity
calculate days away 11 61
TSQL Assignining CTE column in to a variable 3 27
Insert multiple records into a table 4 37
how to use ROW_NUMBER() correctly 8 44
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

751 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