Solved

Select Distinct with mutiple columns and tables

Posted on 2010-11-17
7
410 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
  • 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

772 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