SQL query Parent-child distinct

Question

I have a pair of SQL server tables.

P contains id and name. PR contains id, interestrate, tiernumber, fromdate, todate and P.id. PR may contain many rows listed per p.id / tier. (tiers are a list of rates a product may have in any given date period.)

eg: Product 1 tier 1 starts 1/1/2008 to 1/1/2009 and has 6 rates shown 1 row per rate. Product 1 tier 2 starts 1/2/2009 etc etc etc

I need a view on this that shows the P.name and the PR.tiernumber and dates... BUT I want only one row to represent the tier.

This is easy:

SELECT DISTINCT P.ID, P.PRODUCTCODE, P.PRODUCTNAME, PR.TIERNO, 
  PR.FROMDATE, PR.TODATE, PR.PRODUCTID
FROM dbo.PRODUCTRATE AS PR INNER JOIN dbo.PRODUCT AS P 
  ON P.ID = PR.PRODUCTID
ORDER BY P.ID DESC

This gives me the exact right data... However: this disallows me to see the PR.ID as that would negate the distinct.

I need to limit the resultset because the user needs to just see just a list of tiers, I need to see the PR.ID displaying all of the data.

Any ideas?

Answer 1

SELECT P.ID, P.ACUPRODUCTCODE, P.PRODUCTNAME, PR.TIERNO, 
  PR.FROMDATE, PR.TODATE, PR.PRODUCTID, MIN(PR.ID)
FROM dbo.PRODUCTRATE AS PR INNER JOIN dbo.PRODUCT AS P 
  ON P.ID = PR.PRODUCTID
GROUP BY P.ID, P.ACUPRODUCTCODE, P.PRODUCTNAME, PR.TIERNO, 
  PR.FROMDATE, PR.TODATE, PR.PRODUCTID
ORDER BY P.ID DESC

Ought to do the job. GROUP BY in place of DISTINCT, with a summary function (MIN) to get a particular value back for the PR.ID.

Answer 2

It sounds like you want to accomplish two different things with the same query, which doesn't make sense. Either you want a list of product/tier/date information or you want a list of interest rates.

If you want to pick a particular PR.ID to go with your data then you need to decide on what the rule is for that - what determines which ID you want to get back?