Select DISTINCT (This part of the query only)

DaveRook

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

 
SELECT     TOP (100) PERCENT b1.PartNo, b1.Gender, b1.NoRows, b1.MountType, b1.Pitch, b1.Elevated, s1.Height AS MaleHeight, s1.MinElev, s1.MaxElev, 

                      b2.PartNo AS StackPartNumber, b2.Gender AS StackGender, b2.NoRows AS StackRows, b2.MountType AS MountType2, s2.Height AS FemaleHeight, 

                      s1.Height + s2.Height AS TotalHeight

FROM         dbo.BoardToBoard AS b1 LEFT OUTER JOIN

                      dbo.BoardToBoard AS b2 ON b1.Gender <> b2.Gender INNER JOIN

                      dbo.BoardStacking AS s1 ON b1.PartNo = s1.PartNumber INNER JOIN

                      dbo.BoardStacking AS s2 ON b2.PartNo = s2.PartNumber

WHERE     (b1.Gender = 'Male') AND (b1.NoRows = b2.NoRows) AND (b1.MountType = b2.MountType) AND (b1.Pitch = b2.Pitch)

ORDER BY b1.Pitch

DaveRook

Just did more testing.

Expand|Select|Wrap|Line Numbers

 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

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

madankarmukta

Quote:

Originally Posted by DaveRook

Just did more testing.

Expand|Select|Wrap|Line Numbers

 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

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

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!

DaveRook

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

ck9663

You said you have the following table:

Expand|Select|Wrap|Line Numbers

  
PartNumber             Size

   A1                   5

   A1                   6

   A2                   4

   A2                   5  

   A2                   9

How did you get the following result:

Expand|Select|Wrap|Line Numbers

  
PartNumber1    PartNumber2    TotalHeight

   A1             A2             10

   A1             A2             10

-- CK

Select DISTINCT (This part of the query only)

re: Select DISTINCT (This part of the query only)

re: Select DISTINCT (This part of the query only)

re: Select DISTINCT (This part of the query only)

re: Select DISTINCT (This part of the query only)

Similar Microsoft SQL Server bytes

/bytes/development

/bytes/it

/bytes/about