Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 View of distinct records w/ join from Top 1

Author  Topic 

walker1279
Starting Member

2 Posts

Posted - 2010-01-12 : 12:20:10
This is probably really easy and I'm just missing something. But I have 2 tables that I need to create a view on that bridges PO data and location codes.

The PO table can have multiple records with a common Order ID each with different locales but I need to return only one record.

Currently my query is:

SELECT DISTINCT po.ORDER_ID,po.ORDER_TITLE,po.ORDERED_DATE,po.ORDER_STATUS,po.PERSON_ID,po.VENDOR,loc.Facility,po.BUSINESS_UNIT,po.COST_CENTER
FROM POTable po
INNER JOIN Locations loc
ON po.SHIPTO_UNIQUENAME = loc.UNIQUE_NAME
WHERE loc.Facility IS NOT NULL

I'm getting multiple records in my return query because the PO has multiple records & some have unique facilities where stuff is being shipped to. I don't care which location gets returned...I just need one record when I bridge them.

help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 12:22:31
[code]SELECT DISTINCT po.ORDER_ID,po.ORDER_TITLE,po.ORDERED_DATE,po.ORDER_STATUS,po.PERSON_ID,po.VENDOR,loc.Facility,po.BUSINESS_UNIT,po.COST_CENTER
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY Order_ID ORDER BY NEWID()) AS Seq,* FROM POTable) po
INNER JOIN Locations loc
ON po.SHIPTO_UNIQUENAME = loc.UNIQUE_NAME
AND po.Seq=1
WHERE loc.Facility IS NOT NULL
[/code]
Go to Top of Page

walker1279
Starting Member

2 Posts

Posted - 2010-01-12 : 12:45:35
SEE! I knew it was something easy. Thnx Visakh :)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 12:47:21
welcome
Go to Top of Page
   

- Advertisement -