By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,426 Members | 1,689 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,426 IT Pros & Developers. It's quick & easy.

Select DISTINCT (This part of the query only)

100+
P: 147
Hello

I am bringing some results from SQL server 05 into my web page.

For simplicity, my database has 2 colums, PartNumber and Size, and has the following information:

PartNumber: A1
Size: 5

PartNumber: A1
Size: 6

PartNumber: A2
Size: 4

PartNumber: A2
Size: 5

PartNumber: A2
Size: 9

My databased is then joined to itself and I add the sizes into a new colum called Total size. This means I could find what parts offered me a total size of 10.

The following would bring back 10 as the total height and using alias's, 2 partnumber fields:
(sorry, I can't get them alined)

PartNo1 PartNo2 TotalHeight
A1 A2 10
A1 A2 10

As you can see, we have a repeated result (A1+A2 is displayed twice). I only want to display it once! I can't just do a TOP(1) as the number of results are varied dependant on the criteria.

Any ideas? I don't mind doing this in SQL level or web level (vbscript/asp)

What I would like is to do

SELECT DISTINCT (PartNo1, PartNo2) * From XXXXX

My actual code (which I don't think will be of any help) is:
Expand|Select|Wrap|Line Numbers
  1. SELECT     TOP (100) PERCENT b1.PartNo, b1.Gender, b1.NoRows, b1.MountType, b1.Pitch, b1.Elevated, s1.Height AS MaleHeight, s1.MinElev, s1.MaxElev, 
  2.                       b2.PartNo AS StackPartNumber, b2.Gender AS StackGender, b2.NoRows AS StackRows, b2.MountType AS MountType2, s2.Height AS FemaleHeight, 
  3.                       s1.Height + s2.Height AS TotalHeight
  4. FROM         dbo.BoardToBoard AS b1 LEFT OUTER JOIN
  5.                       dbo.BoardToBoard AS b2 ON b1.Gender <> b2.Gender INNER JOIN
  6.                       dbo.BoardStacking AS s1 ON b1.PartNo = s1.PartNumber INNER JOIN
  7.                       dbo.BoardStacking AS s2 ON b2.PartNo = s2.PartNumber
  8. WHERE     (b1.Gender = 'Male') AND (b1.NoRows = b2.NoRows) AND (b1.MountType = b2.MountType) AND (b1.Pitch = b2.Pitch)
  9. ORDER BY b1.Pitch
Sep 8 '09 #1
Share this Question
Share on Google+
4 Replies


100+
P: 147
Just did more testing.

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT PartNo, StackPartNumber, Elevated, StackGender, StackRows, TotalHeight, Gender, NoRows, Pitch, MaleHeight, FemaleHeight, MountType FROM view_BoardStacking
This gives me repeated columns

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT PartNo, StackPartNumber FROM view_BoardStacking
Above gives me what I want!

Is there a way to do SELECT DISTINCT (PartNo, StackPartNumber) BUT STILL BRING THROUGH Elevated, StackGender, StackRows etc? Almost like SELECT DISTINCT (PartNo, StackPartNumber) * FROM XXX

Thanks

Dave
Sep 8 '09 #2

100+
P: 293
@DaveRook

Hi,

There can't be any solution the way u r expecting.However, if you wann to know what are the different values of "Elevated, StackGender, StackRows" for distinct value pair of "PartNo, StackPartNumber".. then there is a solution over these .Please let me know if you want the same.

Thanks!
Sep 10 '09 #3

100+
P: 147
Hello.

No, if there is no solution, then that is the answer! Thank you for your help, I will look at a design change!

Thank you for your help

Dave
Sep 10 '09 #4

ck9663
Expert 2.5K+
P: 2,878
You said you have the following table:
Expand|Select|Wrap|Line Numbers
  1.  
  2. PartNumber             Size
  3.    A1                   5
  4.    A1                   6
  5.    A2                   4
  6.    A2                   5  
  7.    A2                   9
  8.  
  9.  
How did you get the following result:

Expand|Select|Wrap|Line Numbers
  1.  
  2. PartNumber1    PartNumber2    TotalHeight
  3.    A1             A2             10
  4.    A1             A2             10
  5.  
  6.  

-- CK
Sep 10 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.