Select with multiple column and Distinct

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

General SQL Server Forums

New to SQL Server Programming

Select with multiple column and Distinct

Author

Topic

brandyballz
Starting Member

12 Posts

Posted - 2008-06-22 : 04:28:52

Hi guys, I'm hoping there's someone out there with more sql knowledge and experience than me. I'll try to explain everything.

I'm trying to create a select statement but i'm not gettting the required results mainly because i think its a very complicated select.

Here is the scenario.

The table has 12 columns

ProductID Colour MD01 MD02 MD03 MD04 MD05 MD06 MD07 MD08 MD09 MD010

The ProductID is naturally the unique key.

There is always a colour value. But there is not always a value in the MD columns. For example one Product may have values in MD01 MD02 MD03 MD04 MD05 whilst another has values in all MD columns.

My problem is thatI am trying to create a results list based upon selecting distinct values from the colour and md columns

In otherwords i can't have more than only one instance of a word appearing in the recordset list

I'm really struggling with this because there are only 6 colours so if i set distinct purely on 6 colours i only get back 6 rows.

When I try to set disctinct across all the MD columns it seems to ignore it and lists results for example in the table

ProductID Colour MD01 MD02 MD03 MD04 MD05 MD06 MD07 MD08 MD09 MD010
1 red car bike
2 blue bike car train

my select lists results as
red
car
bike
blue
bike
car
train

and it is as if it only carries out the distinct command across the row not across all columns for all rows if you see what i mean?

I need to be able to list all data from all rows that have values in the MD columns and colour column but not list the values more than once and not list "empty" (NULL) columns. Does this make sense?

This is the select statement i wrote.

Select DISTINCT md00, md01, md02, md03, md04, md05, md06, md07, md08, md09, md10, colour FROM TEMPLATES WHERE md00 IS NOT NULL or md01 IS NOT NULL or md02 IS NOT NULL or md03 IS NOT NULL or md04 IS NOT NULL or md05 IS NOT NULL or md06 IS NOT NULL or md07 IS NOT NULL or md08 IS NOT NULL or md09 IS NOT NULL or md10 IS NOT NULL

But it returns empty columns and it returns every instance of the same word so in other words the distinct command doesn't seem to be working at all?

I don't know if this is because of my asp code I am trying to list results with the rescordset?

<%
While ((Repeat1__numRows <> 0) AND (NOT template_rs.EOF))
%>

<%=(template_rs.Fields.Item("md01").Value)%>
<%=(template_rs.Fields.Item("md02").Value)%>
<%=(template_rs.Fields.Item("md03").Value)%>
<%=(template_rs.Fields.Item("md04").Value)%>
<%=(template_rs.Fields.Item("md05").Value)%>
<%=(template_rs.Fields.Item("md06").Value)%>
<%=(template_rs.Fields.Item("md07").Value)%>
<%=(template_rs.Fields.Item("md08").Value)%>
<%=(template_rs.Fields.Item("md09").Value)%>
<%=(template_rs.Fields.Item("md10").Value)%>
<%=(template_rs.Fields.Item("colour").Value)%>

<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
template_rs.MoveNext()
Wend
%>

I have one more problem. How can I also in addition to being able to list all distinct results list only results for a specific letter

for example

Select DISTINCT md00, md01, md02, md03, md04, md05, md06, md07, md08, md09, md10, colour FROM TEMPLATES WHERE md00 IS NOT NULL or md01 IS NOT NULL or md02 IS NOT NULL or md03 IS NOT NULL or md04 IS NOT NULL or md05 IS NOT NULL or md06 IS NOT NULL or md07 IS NOT NULL or md08 IS NOT NULL or md09 IS NOT NULL or md10 IS NOT NULL WHERE FIRST LETTER ='A'?

I am so far out of my depth here guys I am hoping that someone who has real knowledge of SQL can help me with this statement. I've been pulling my hair out for days now and getting just more and more frustrated listing the same results :(

-BB

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-22 : 05:05:39

Can you provide some sample data from your tables and then give what output you're expecting out of them?

brandyballz
Starting Member

12 Posts

Posted - 2008-06-22 : 05:46:38

quote:
Originally posted by visakh16

Can you provide some sample data from your tables and then give what output you're expecting out of them?

Hi visakh16! :) thank you so much for replying to my thread.

Of course.

An example is like this: http://www.accepta.no/results.gif

What I need visakh is to be able to list the results (based on what you see in the table like this)

red
car
vehicle
motor
transport
blue
truck
lorry
passenger
green
bike
cycle
motorcyle
wheels
orange
engine
yellow
train

showing only one instance of each word from the data.

In addition i then need to be able to select from the table data based only on letter. For example "select distinct from all the columns in all the rows where the first letter ='b'

which would then only return from the table

blue
bike

I'm using ASP as you can tell and MS SQL 2000 if that is important?

Thanks again visakh for any help you can give me. I'm really grateful to you.

-BB

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-22 : 06:04:30

[code]SELECT DISTINCT t.Value
FROM
(SELECT ProductID,Color AS Value,0 AS Order
UNION ALL
SELECT ProductID,MD01 AS Value,1 AS Order
UNION ALL
SELECT ProductID,MD02 AS Value,2 AS Order
UNION ALL
SELECT ProductID,MD03 AS Value,10 AS Order
....
UNION ALL
SELECT ProductID,MD10 AS Value,1 AS Order)t
WHERE t.Value IS NOT NULL
ORDER BY t.ProductID,t.Order[/code]

brandyballz
Starting Member

12 Posts

Posted - 2008-06-22 : 07:39:20

Hi again visakh :)

thank you so much for helping me. I was wondering if you could just help explain a little more to me?

When you say t.Value does this mean table and value? for example select distinct md01.TEMPLATES?

I'm just a little confused over t.?

Thank you visakh. I really really appreciate everything and all your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-22 : 12:46:15

quote:
Originally posted by brandyballz

Hi again visakh :)

thank you so much for helping me. I was wondering if you could just help explain a little more to me?

When you say t.Value does this mean table and value? for example select distinct md01.TEMPLATES?

I'm just a little confused over t.?

Thank you visakh. I really really appreciate everything and all your help.

value is just an alias name. It is the value that involves both your colour and MD01 to 10 fields. Im first putting them together and them using an Order field to make sure they always arrive in order of precedence from the table and then taking distinct to remove duplicate values.

brandyballz
Starting Member

12 Posts

Posted - 2008-06-24 : 10:15:59

Hi again visakh!

I'm sorry I can't seem to make your select statement function.

The table is called HOLDING, and so whenever I try for exmaple to reference a table column to the table, for example,

SELECT DISTINCT t.Value
FROM
(SELECT ProductID.HOLDING,Color.HOLDING AS Value,0 AS Order

etc.,

I only get the HTTP 500 Internal Server error :(

Am I doing something wrong here? I've tried many combinations but can't seem to get anything functioning? Just wondered if I am missing something?

Thanks again visakh

-BB

brandyballz
Starting Member

12 Posts

Posted - 2008-06-24 : 11:19:44

Can anyone assist?

-BB

brandyballz
Starting Member

12 Posts

Posted - 2008-06-24 : 14:30:12

quote:

Originally posted by visakh16

SELECT DISTINCT t.Value
FROM
(SELECT ProductID,Color AS Value,0 AS Order
 UNION ALL
 SELECT ProductID,MD01 AS Value,1 AS Order
 UNION ALL
 SELECT ProductID,MD02 AS Value,2 AS Order
 UNION ALL
 SELECT ProductID,MD03 AS Value,10 AS Order
....
 UNION ALL
  SELECT ProductID,MD10 AS Value,1 AS Order)t
WHERE t.Value IS NOT NULL
ORDER BY t.ProductID,t.Order

I reckon you meant

SELECT DISTINCT t.Value
FROM
(SELECT ProductID,Colour AS Value,0 AS Order
UNION ALL
SELECT ProductID,MD01 AS Value,1 AS Order
UNION ALL
SELECT ProductID,MD02 AS Value,2 AS Order
UNION ALL
SELECT ProductID,MD03 AS Value,3 AS Order
UNION ALL
SELECT ProductID,MD04 AS Value,4 AS Order
UNION ALL
SELECT ProductID,MD05 AS Value,5 AS Order
UNION ALL
SELECT ProductID,MD06 AS Value,6 AS Order
UNION ALL
SELECT ProductID,MD07 AS Value,7 AS Order
UNION ALL
SELECT ProductID,MD08 AS Value,8 AS Order
UNION ALL
SELECT ProductID,MD09 AS Value,9 AS Order
UNION ALL
SELECT ProductID,MD10 AS Value,10 AS Order
UNION ALL
SELECT ProductID,MD10 AS Value,1 AS Order)t
WHERE t.Value IS NOT NULL
ORDER BY t.ProductID,t.Order

visakh?

The problem is when I run this is Query Analyzer it changes the code to:

SELECT DISTINCT t .Value
FROM (SELECT ProductID, Colour AS Value, 0 AS ORDER
UNION ALL
SELECT ProductID, MD01 AS Value, 1 AS ORDER
UNION ALL
SELECT ProductID, MD02 AS Value, 2 AS ORDER
UNION ALL
SELECT ProductID, MD03 AS Value, 3 AS ORDER
UNION ALL
SELECT ProductID, MD04 AS Value, 4 AS ORDER
UNION ALL
SELECT ProductID, MD05 AS Value, 5 AS ORDER
UNION ALL
SELECT ProductID, MD06 AS Value, 6 AS ORDER
UNION ALL
SELECT ProductID, MD07 AS Value, 7 AS ORDER
UNION ALL
SELECT ProductID, MD08 AS Value, 8 AS ORDER
UNION ALL
SELECT ProductID, MD09 AS Value, 9 AS ORDER
UNION ALL
SELECT ProductID, MD10 AS Value, 10 AS ORDER
UNION ALL
SELECT ProductID, MD10 AS Value, 1 AS ORDER) t
WHERE t .Value IS NOT NULL
ORDER BY t .ProductID, t . ORDER

putting a space after every instance of 't' and creates an error message that states incorrect syntax near the keyword 'ORDER'

Any ideas? And does this need to refer to the HOLDING table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 00:46:19

quote:
Originally posted by brandyballz

quote:
Originally posted by visakh16

SELECT DISTINCT t.Value
FROM
(SELECT ProductID,Color AS Value,0 AS Order
UNION ALL
SELECT ProductID,MD01 AS Value,1 AS Order
UNION ALL
SELECT ProductID,MD02 AS Value,2 AS Order
UNION ALL
SELECT ProductID,MD03 AS Value,10 AS Order
....
UNION ALL
SELECT ProductID,MD10 AS Value,1 AS Order)t
WHERE t.Value IS NOT NULL
ORDER BY t.ProductID,t.Order

I reckon you meant

SELECT DISTINCT t.Value
FROM
(SELECT ProductID,Colour AS Value,0 AS Order
UNION ALL
SELECT ProductID,MD01 AS Value,1 AS Order
UNION ALL
SELECT ProductID,MD02 AS Value,2 AS Order
UNION ALL
SELECT ProductID,MD03 AS Value,3 AS Order
UNION ALL
SELECT ProductID,MD04 AS Value,4 AS Order
UNION ALL
SELECT ProductID,MD05 AS Value,5 AS Order
UNION ALL
SELECT ProductID,MD06 AS Value,6 AS Order
UNION ALL
SELECT ProductID,MD07 AS Value,7 AS Order
UNION ALL
SELECT ProductID,MD08 AS Value,8 AS Order
UNION ALL
SELECT ProductID,MD09 AS Value,9 AS Order
UNION ALL
SELECT ProductID,MD10 AS Value,10 AS Order
UNION ALL
SELECT ProductID,MD10 AS Value,1 AS Order)t
WHERE t.Value IS NOT NULL
ORDER BY t.ProductID,t.Order

visakh?

The problem is when I run this is Query Analyzer it changes the code to:

SELECT DISTINCT t .Value
FROM (SELECT ProductID, Colour AS Value, 0 AS ORDER
FROM HOLDING
UNION ALL
SELECT ProductID, MD01 AS Value, 1 AS ORDER
FROM HOLDING
UNION ALL
SELECT ProductID, MD02 AS Value, 2 AS ORDER
FROM HOLDING
UNION ALL
SELECT ProductID, MD03 AS Value, 3 AS ORDER
FROM HOLDING
UNION ALL
SELECT ProductID, MD04 AS Value, 4 AS ORDER
FROM HOLDING
UNION ALL
SELECT ProductID, MD05 AS Value, 5 AS ORDER
FROM HOLDING
UNION ALL
SELECT ProductID, MD06 AS Value, 6 AS ORDER
FROM HOLDING
UNION ALL
SELECT ProductID, MD07 AS Value, 7 AS ORDER
FROM HOLDING
UNION ALL
SELECT ProductID, MD08 AS Value, 8 AS ORDER
FROM HOLDING
UNION ALL
SELECT ProductID, MD09 AS Value, 9 AS ORDER
FROM HOLDING
UNION ALL
SELECT ProductID, MD10 AS Value, 10 AS ORDER
FROM HOLDING
UNION ALL
SELECT ProductID, MD10 AS Value, 1 AS ORDER
FROM HOLDING) t
WHERE t .Value IS NOT NULL
ORDER BY t .ProductID, t . ORDER

putting a space after every instance of 't' and creates an error message that states incorrect syntax near the keyword 'ORDER'

Any ideas? And does this need to refer to the HOLDING table?

It needs to .please add FROM HOLDING to each select statement as above

brandyballz
Starting Member

12 Posts

Posted - 2008-06-25 : 06:52:13

Hi viaskh! you're out there!!! :)))

I tried adding the HOLDING as directed however, its still the same error in the SQL statement. "Incorrect syntax near the keyword 'Order'.

When I remove the ORDER BY t.ProductID, t.ORDER at the end of the statement the error still occurs so it doesnt seem to be due to sorting or listing.

Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 06:56:41

quote:
Originally posted by brandyballz

Hi viaskh! you're out there!!! :)))

I tried adding the HOLDING as directed however, its still the same error in the SQL statement. "Incorrect syntax near the keyword 'Order'.

When I remove the ORDER BY t.ProductID, t.ORDER at the end of the statement the error still occurs so it doesnt seem to be due to sorting or listing.

Any ideas?

enclose ORDER with [] everywhere and try. Order is a reserved word and can cause such problems.

brandyballz
Starting Member

12 Posts

Posted - 2008-06-25 : 07:15:52

Hi again visakh,

ok...there seems to be some progress but something weird is happening. I enclose all instances of AS ORDER
FROM HOLDING

to AS [ORDER]
FROM HOLDING

when I then remove everything after the final )

i.e. t
WHERE t .Value IS NOT NULL
ORDER BY t .ProductID, t . ORDER

then results are returned.

So it seems that something is happening, but the results are not being sorted alphabetically in any order.

Once I remove everything after the final )

the SQL statement seems to change to:

SELECT DISTINCT [Value]
FROM (SELECT ProductID, Colour AS Value, 0 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD01 AS Value, 1 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD02 AS Value, 2 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD03 AS Value, 3 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD04 AS Value, 4 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD05 AS Value, 5 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD06 AS Value, 6 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD07 AS Value, 7 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD08 AS Value, 8 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD09 AS Value, 9 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD10 AS Value, 10 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD10 AS Value, 1 AS [ORDER]
FROM HOLDING) DERIVEDTBL

?

How can I list the results automatically my friend? And by the way..thank you so much for all your help so far. You are a wizard with SQL!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 07:21:03

quote:
Originally posted by brandyballz

Hi again visakh,

ok...there seems to be some progress but something weird is happening. I enclose all instances of AS ORDER
FROM HOLDING

to AS [ORDER]
FROM HOLDING

when I then remove everything after the final )

i.e. t
WHERE t .Value IS NOT NULL
ORDER BY t .ProductID, t . ORDER

then results are returned.

So it seems that something is happening, but the results are not being sorted alphabetically in any order.

Once I remove everything after the final )

the SQL statement seems to change to:

SELECT DISTINCT [Value]
FROM (SELECT ProductID, Colour AS Value, 0 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD01 AS Value, 1 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD02 AS Value, 2 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD03 AS Value, 3 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD04 AS Value, 4 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD05 AS Value, 5 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD06 AS Value, 6 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD07 AS Value, 7 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD08 AS Value, 8 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD09 AS Value, 9 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD10 AS Value, 10 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD10 AS Value, 1 AS [ORDER]
FROM HOLDING) DERIVEDTBL

?

How can I list the results automatically my friend? And by the way..thank you so much for all your help so far. You are a wizard with SQL!

Why you removed last part no need just give
WHERE DERIVEDTBL.[Value] IS NOT NULL
ORDER BY DERIVEDTBL.ProductID, DERIVEDTBL.[ORDER]

also plese ensure you dont put any spaces between . and tablename/columnname.

brandyballz
Starting Member

12 Posts

Posted - 2008-06-25 : 07:33:53

Hi visakh,

I removed the last part because SQL Query Analyzer will not carryout the query with the last part. It returns an error statement. It returns the error "Incorrect Syntax near the keyword 'ORDER'

SQL Query Analyzer then inserts some spaces itself it seems into the last part and creates this

) t
WHERE t .Value IS NOT NULL
ORDER BY t .ProductID, t . ORDER

Now if I remove

) t
WHERE t .Value IS NOT NULL
ORDER BY t .ProductID, t . ORDER

and replace it simply with

)

it completes the query and generates the results as I say but adds DERIVEDTBL after the end of the final )

so it looks like this

SELECT DISTINCT [Value]
FROM (SELECT ProductID, Colour AS Value, 0 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD01 AS Value, 1 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD02 AS Value, 2 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD03 AS Value, 3 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD04 AS Value, 4 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD05 AS Value, 5 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD06 AS Value, 6 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD07 AS Value, 7 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD08 AS Value, 8 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD09 AS Value, 9 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD10 AS Value, 10 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD10 AS Value, 11 AS [ORDER]
FROM HOLDING) DERIVEDTBL

when I add

WHERE DERIVEDTBL.[Value] IS NOT NULL
ORDER BY DERIVEDTBL.ProductID, DERIVEDTBL.[ORDER]

so the statement now looks like

SELECT DISTINCT [Value]
FROM (SELECT ProductID, Colour AS Value, 0 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD01 AS Value, 1 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD02 AS Value, 2 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD03 AS Value, 3 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD04 AS Value, 4 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD05 AS Value, 5 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD06 AS Value, 6 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD07 AS Value, 7 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD08 AS Value, 8 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD09 AS Value, 9 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD10 AS Value, 10 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD10 AS Value, 11 AS [ORDER]
FROM HOLDING) DERIVEDTBL WHERE DERIVEDTBL.[Value] IS NOT NULL ORDER BY DERIVEDTBL.ProductID, DERIVEDTBL.[ORDER]

then SQL Query Analyzer generates another error.

Order By items must appear in the select list if SELECT DISTINCT is specified.

then SQL Query Analyzer rewrites the code to

SELECT DISTINCT [Value]
FROM (SELECT ProductID, Colour AS Value, 0 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD01 AS Value, 1 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD02 AS Value, 2 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD03 AS Value, 3 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD04 AS Value, 4 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD05 AS Value, 5 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD06 AS Value, 6 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD07 AS Value, 7 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD08 AS Value, 8 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD09 AS Value, 9 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD10 AS Value, 10 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD10 AS Value, 11 AS [ORDER]
FROM HOLDING) DERIVEDTBL
WHERE ([Value] IS NOT NULL)
ORDER BY ProductID, [ORDER]

and still will not execute the query. So it seems there some problem in sorting/ ordering the data?

brandyballz
Starting Member

12 Posts

Posted - 2008-06-25 : 07:47:09

I think if I replace the final part with

)DERIVEDTBL
WHERE ([Value] IS NOT NULL)
ORDER BY [Value]

Then it seems to work visakh?

I think it may be because the Select value is [Value] and not DERIVEDTBL.ProductID, DERIVEDTBL.[ORDER]?

brandyballz
Starting Member

12 Posts

Posted - 2008-06-25 : 07:48:20

I have one more question my friend regarding how to select for example only words beginning with the letter 'A' from all the columns to return results?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 07:50:24

quote:
Originally posted by brandyballz

I have one more question my friend regarding how to select for example only words beginning with the letter 'A' from all the columns to return results?

where columnname LIKE 'A%'

or LEFT(Columnname,1)='A'

brandyballz
Starting Member

12 Posts

Posted - 2008-06-25 : 07:51:09

I got it my friend.

) DERIVEDTBL
WHERE ([Value] IS NOT NULL) AND ([Value] LIKE 'A%')
ORDER BY [Value]

:)))

I just want to thank you for all your help visakh. Truely you have been extremely kind and I dont forget this.

Many many many thanks my friend.

-BB

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 07:54:07

quote:
Originally posted by brandyballz

I think if I replace the final part with

)DERIVEDTBL
WHERE ([Value] IS NOT NULL)
ORDER BY [Value]

Then it seems to work visakh?

I think it may be because the Select value is [Value] and not DERIVEDTBL.ProductID, DERIVEDTBL.[ORDER]?

wat about this

SELECT [Value] 
FROM
(
SELECT DISTINCT [Value]
FROM (SELECT ProductID, Colour AS Value, 0 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD01 AS Value, 1 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD02 AS Value, 2 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD03 AS Value, 3 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD04 AS Value, 4 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD05 AS Value, 5 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD06 AS Value, 6 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD07 AS Value, 7 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD08 AS Value, 8 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD09 AS Value, 9 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD10 AS Value, 10 AS [ORDER]
FROM HOLDING
UNION ALL
SELECT ProductID, MD10 AS Value, 11 AS [ORDER]
FROM HOLDING) DERIVEDTBL 
WHERE DERIVEDTBL.[Value] IS NOT NULL 
AND DERIVEDTBL.[Value] LIKE 'A%'
)t
ORDER BY t.ProductID, t.[ORDER]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 07:54:52

quote:
Originally posted by brandyballz

I got it my friend.

) DERIVEDTBL
WHERE ([Value] IS NOT NULL) AND ([Value] LIKE 'A%')
ORDER BY [Value]

:)))

I just want to thank you for all your help visakh. Truely you have been extremely kind and I dont forget this.

Many many many thanks my friend.

-BB

You're welcome

. Always happy to help.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources