selecting distinct with a non-distinct row

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 2000 Forums

SQL Server Development (2000)

selecting distinct with a non-distinct row

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-01-20 : 08:38:17

Alistair writes "Dear SQLTeam,

Hoping this one is suitable hard enough (yeah right) for you to cast an eye across.

Cheers
Alistair

I am selecting rows from a client supplied database with client supplied view which basically joins all the relational tables into one big table.

productGroup is like a product range, ie body_panels, interior_trim

productItem is the individual item under a productGroup

I want to be able to select a distinct productGroup_id, productGroup_description and the first productItem_code that appears under that productGroup_id.

example data

<TD>productGroup_id</TD><TD>productGroup_description</TD><TD>productItem_code</TD>
<TD>1</TD><TD>body panels</TD><TD>456.44</TD>
<TD>1</TD><TD>body panels</TD><TD>456.45</TD>
<TD>1</TD><TD>body panels</TD><TD>556.45</TD>
<TD>2</TD><TD>interior trim</TD><TD>483.33</TD>
<TD>2</TD><TD>interior trim</TD><TD>583.33</TD>
<TD>3</TD><TD>suspension</TD><TD>225.67</TD>
<TD>3</TD><TD>suspension</TD><TD>226.54</TD>

wanted result

<TD>productGroup_id</TD><TD>productGroup_description</TD><TD>productItem_code</TD>
<TD>1</TD><TD>body panels</TD><TD>456.44</TD>
<TD>2</TD><TD>interior trim</TD><TD>483.33</TD>
<TD>3</TD><TD>suspension</TD><TD>225.67</TD>"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-20 : 08:44:20

Select productGroup_id,productGroup_description,Min(productItem_code) from yourTable
Group by productGroup_id,productGroup_description

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources