Solved

Select Distinct with mutiple columns and tables

Posted on 2010-11-17
7
389 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now