[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Distinct combined with asterisk?

Posted on 2006-05-29
23
Medium Priority
?
1,011 Views
Last Modified: 2013-12-24
Hi,

 I want to pull a distinct parameter, and also use an asterisk. Is that possible?

In other words,

SELECT  distinct name, *
FROM table

So  get a distinct name, but it also pulls everything else?
0
Comment
Question by:mel150
  • 14
  • 7
  • 2
23 Comments
 
LVL 7

Accepted Solution

by:
aseusainc earned 1500 total points
ID: 16786923
Not really, because you are only pulling a distict name, how would you know WHICH row to get the rest of the data from?
0
 

Author Comment

by:mel150
ID: 16786947
Ah.... good question. It won't allow me to pull the other parameter distinctly, because it is datatype text. Can I make that column a different datatype? It is quite long, as it is a description of a product. Thanks!!
0
 

Author Comment

by:mel150
ID: 16786991
Hi- I guess I could change it to varchar? I will try that.
0
Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

 

Author Comment

by:mel150
ID: 16787012
Hi- Guess what? Not sure how to do that :)

Would it be:

Currentdb.Execute "ALTER TABLE products ALTER COLUMN description varchar"

?
0
 
LVL 7

Expert Comment

by:aseusainc
ID: 16788803
I dont think it has anything to do w/ column type.  Pretty sure you cannot do what you are trying to do.
0
 

Author Comment

by:mel150
ID: 16789455
Sorry about that, I was not clear about what I meant. That happens often. :)

I meant that I understand what you mean about not being able to pull only one column as distinct. In that case, I have 2 columns I am trying to pull, name and description. Column 'name' is varchar, column 'description' is text. If I try to say
SELECT Distinct name, description

I get an error that says datatype text cannot be distinct.

So, as much as it makes me nervous, I wondered if chaging the datatype on the column description to varcahr would solve my problem.
0
 
LVL 7

Expert Comment

by:ExpertAdmin
ID: 16789642
True. Text is a weird one. You can change it to VARCHAR() and then use distinct on it....

OR you may be able to do this:

SELECT DISTINCT(CONVERT(VARCHAR(100),{myTextColumn}))
FROM {MyTable}

In this example, replace {MyTextColumn} with your column name and MyTable with your table name

Then if you want to do a distinct on more than one column, try this:

SELECT DISTINCT(CONVERT(VARCHAR(1000),{myTextColumn})) , CONVERT(VARCHAR(1000), {myOtherColumn}))
FROM {MyTable}


M@
0
 

Author Comment

by:mel150
ID: 16789772
Wow! Interesting! Thanks, ExpertAdmin, I'll try that.
0
 

Author Comment

by:mel150
ID: 16789857
Hi again..here is my query now:
<CFQUERY DATASOURCE="#request.maindsn#" NAME="get_products">
    SELECT    Distinct products.name , (CONVERT(VARCHAR(1000),products.description)), sizes.size2_price, sizes.size4_price  
    FROM      products INNER JOIN sizes
      ON  products.id=sizes.product_id
      WHERE products.active=1
      ORDER BY products.name
</CFQUERY>

And then I have:
<tr><td><cfoutput query="get_products" startrow=1 maxrows=1><b>#name#:</b>
</cfoutput>
<cfoutput query="get_products" startrow=1 maxrows=1>#description#</cfoutput></td>
<td><cfoutput query="get_products" startrow=1 maxrows=1>#size2_price#</cfoutput></td><td><cfoutput query="get_products" startrow=1 maxrows=1>#size4_price#</cfoutput></td>
</tr>

The name is being pulled, but then it says Variable Desciption is undefined..any ideas? Thanks!
0
 
LVL 7

Expert Comment

by:ExpertAdmin
ID: 16790152
Oh...


Sorry, should have been this:

SELECT DISTINCT(CONVERT(VARCHAR(100),{myTextColumn})) AS MyTextColumn
FROM {MyTable}

The way it was did not give it a column name to return the conversion in.

M@
0
 

Author Comment

by:mel150
ID: 16790401
Thanks!! I will try that on my break in a few hours.
0
 

Author Comment

by:mel150
ID: 16794554
Interesting... unless I'm nuts, which is a possibilty, this query:

<CFQUERY DATASOURCE="#request.maindsn#" NAME="get_products">
    SELECT  DISTINCT (CONVERT(VARCHAR(100), products.description)) AS description, (CONVERT(VARCHAR(100), products.name)) AS name, (CONVERT(VARCHAR(100), sizes.size2_price)) AS size2_price, (CONVERT(VARCHAR(100), sizes.size4_price)) AS size4_price  
    FROM      products INNER JOIN sizes
      ON  products.id=sizes.product_id
      WHERE products.active=1
      ORDER BY products.name
</CFQUERY>

Gives me the same results as this query:

<CFQUERY DATASOURCE="#request.maindsn#" NAME="get_products">
    SELECT     *
    FROM      products INNER JOIN sizes
      ON  products.id=sizes.product_id
      WHERE products.active=1
      ORDER BY products.name
</CFQUERY>

When I am calling:

<tr><td><cfoutput query="get_products" startrow=1 maxrows=1><b>#name#:</b>
</cfoutput>
<cfoutput query="get_products" startrow=1 maxrows=1>#description#</cfoutput></td>
<td><cfoutput query="get_products" startrow=1 maxrows=1>#size2_price#</cfoutput></td><td><cfoutput query="get_products" startrow=1 maxrows=1>#size4_price#</cfoutput></td>
</tr>

In other words, it isn't pulling the columns distinctly. Does that make sense? Thanks!!
0
 
LVL 7

Expert Comment

by:ExpertAdmin
ID: 16794856
Just for clarification, the distinct will be distinct for ALL COLUMNS together. In other words, all of the following are distinct:

X Y Z
X Y Y
Y X Z
Y Y Y
Z Z Z

The fact that there are not two rows with the same identical values in the same columns makes them distinct. Is this consistent with what you are thinking?

M@
0
 

Author Comment

by:mel150
ID: 16798467
hmm.. maybe I'm misunderstanding Distinct after all. What I thought I would get was:

Name of menu item| description of item|price for 2 servings of item| price for 4 servings of item

But what I'm getting is:
First row:
Name of menu item|description of item|price for 2 servings of item|blank column
next row:
Name of same menu item|description of item|blank column|price for 4 servings of item

Which is the same thing I pull if I use this query:

<CFQUERY DATASOURCE="#request.maindsn#" NAME="get_products">
    SELECT     *
    FROM      products INNER JOIN sizes
     ON  products.id=sizes.product_id
     WHERE products.active=1
     ORDER BY products.name
</CFQUERY>

I'm sure I'm missing something- thanks for your patience!



0
 
LVL 7

Expert Comment

by:ExpertAdmin
ID: 16798767
Yea...Distinct just means "Don't repeat the rows if more than one row is identical". Since you apparently only have them listed once, then SELECT * and SELECT DISTINCT * will yield the same list.

Tell me a little more about what you are attempting to do and maybe we can figure it out.

M@
0
 

Author Comment

by:mel150
ID: 16799489
Ok, thanks!- I have two tables. One is products, one is sizes. The relevant columns are:

products: name, description
sizes: size2_price, size 4_price

and the linking columns are: products.id and sizes.product_id

Now, sizes has each product_id in two rows, because there are two sizes for each product. And each row has a price for that size:

product_id    code        price
2060            size_2      5.50
2060            size_4      11.00


Because I want my table to pull the price for each size, I thought I needed to add two columns, and maybe I made a mistake here- I added two columns to the table sizes, size2_price and size4_price.

I want my results to be:

The name (only once), the description (only once) the price for size_2, the price for size_4.

Does that make sense? Do you need more info? Thanks very much for your help!
0
 
LVL 7

Expert Comment

by:ExpertAdmin
ID: 16799794
I see what you are saying.


This will get the results you want, but it is not very good code:

SELECT Product.Name, (SELECT Price FROM Sizes WHERE code = 'size_2') AS size2_price, (SELECT Price FROM Sizes WHERE code = 'size_4') AS size4_price)

I think my main question at this point is: why do you only want to return one row? That is not really the way a relational database is designed to work. Normally you would return multiple rows, then iterate through them in your application to pull the relevant information from each row. Like this:

Product:    My Product

Pricing
 
  QTY        PRICE
     2             5.50
     4           11.00
    ...etc...

In this scenerio, you wouldn't even need to return the product name...it would be in a different query. You would simply pass in the product ID to get the list of prices.

M@
0
 

Author Comment

by:mel150
ID: 16799829
Hmm.. the one row is for aesthetics. What I am really doing is creating a menu, an interactive menu. Part of that query will be where products.active=1. So when a product becomes inactive, it drops from the menu, or conversely if it becomes active, it appears on the menu.

 But I don't want "Meatloaf with Tomato Glaze" to appear in two rows. Make sense?
0
 
LVL 7

Expert Comment

by:ExpertAdmin
ID: 16799901
Yes...but what I think what one of us is missing is this: it seems to me that in your thinking the results of the query is coupled directly to the menu. This would make sense if the menu were (or is) based on XML. But if it is in an application (like ASP or ColdFusion), you can create the menu items in code any way you like.

But that is no big deal. The query with the subselects works, but the problem with it is that you have to know ahead of time what the quantities will be in order to do the subselect properly. This may or may not solve your problem.

Just to help me understand the constraints you are working with...how are you building these menus?

M@
0
 

Author Comment

by:mel150
ID: 16799979
I hope this is the answer you need: I am building them in coldfusion. Let me see if I can simplify: This code below gives me exactly what I want, EXCEPT the prices aren't linked to the product, they are arbitrary because I haven't joined the tables:

<CFQUERY DATASOURCE="#request.maindsn#" NAME="get_products">
      SELECT      *
      FROM       products
      WHERE active='1'
      ORDER BY name
</CFQUERY>
<CFQUERY DATASOURCE="#request.maindsn#" NAME="get_prices">
      SELECT      *
      FROM       sizes
</CFQUERY>

<table width="100%" border="1" cellspacing="2" cellpadding="2" align="left">
<tr><td><b>Menu Item</b></td><td><b>2 servings</b></td><td><b>4 servings</b></td></tr>

<tr><td><cfoutput query="get_products" startrow=1 maxrows=1><b>#name#:</b>
</cfoutput>
<cfoutput query="get_products" startrow=1 maxrows=1>#description#</cfoutput></td><td><cfoutput query="get_prices" startrow=1 maxrows=1>$#price#</cfoutput></td><td><cfoutput query="get_prices" startrow=1 maxrows=1>$#price#</cfoutput></td></tr>

Then I get:
Menu Item                                              Price for 2                Price for 4
Meatloaf -Meatloaf description                   8.00                         15.50              But those prices are incorrect.

Does that make it clearer or muddier? Thanks!!
0
 
LVL 7

Expert Comment

by:ExpertAdmin
ID: 16800516
OK. Here is how I would go about it:


First, your prices table should look like this:

ProductID
Quantity
Price

So for a single product, you would have more than one row (as you stated):

Product     Qty         Price
------------------------------------------
0001           2          5.50
0001           4          11.00
0002           2          2.45
0002           4          4.90


With this structure, you could do this:


<CFQUERY DATASOURCE="#request.maindsn#" NAME="get_products">
     SELECT     *
     FROM      products
     WHERE active='1'
     ORDER BY name
</CFQUERY>

<CFOUTPUT QUERY = "get_products">   <!---First, loop through the products list--->
   
   <CFQUERY DATASOURCE="#request.maindsn#" NAME="get_prices">  <!--- For each product, get the list of quantities and prices--->
            SELECT     *
            FROM      sizes
            WHERE Product = '#Product#'
    </CFQUERY>


    <TR>        <!--- Start a new row for each product.--->
           <TD>
                    #Product#              <!---Add the product name --->
           </TD>
          <CFOUTPUT QUERY ="get_prices">   <!---Loop through the prices, adding a cell for each one --->        
                        <TD>#Quantity#</TD>
                        <TD>#Price#</TD>
          </CFOUTPUT>

    </TR>       <!--- Close out this product's row--->    

</CFOUTPUT>


With this setup you can specify as many prices as you need, and you don't have to name your columns to match the entry.

M@
0
 

Author Comment

by:mel150
ID: 16801386
Thanks!! I'll give this a try tonight.
0
 

Author Comment

by:mel150
ID: 16828368
Hi, Folks,

 I've been working the SQL query side of this, and I have a successful query, but I am still getting errors.

If I run this query in Query Analyzer:

 SELECT prod.name, prod.description, size2.Price AS [Size 2 Price], size4.Price AS [Size 4 Price]
FROM [products] prod

INNER JOIN [sizes] size2
 ON prod.ID = size2.Product_ID
 AND size2.code = 'Size2'

I get the response I want:

Name   Description   size2_price  size4_price

But if I try to pull it to a webpage:

<table width="100%" border="1" cellspacing="2" cellpadding="2" align="left">
<tr><td><b>Menu Item</b></td><td><b>2 servings</b></td><td><b>4 servings</b></td></tr>

<tr><td><cfoutput query="get_products" startrow=1 maxrows=1><b>#name#:</b>
</cfoutput>
<cfoutput query="get_products" startrow=1 maxrows=1>#description#</cfoutput></td>
<td><cfoutput query="get_products" startrow=1 maxrows=1>#size2_price#</cfoutput></td>
<td><cfoutput query="get_products" startrow=1 maxrows=1>#size4_price#</cfoutput></td></tr>


</table>

I get an error: Variable SIZE2_PRICE is undefined.

Any thoughts? Do you need more information? Or should I have opened this as a new question? Thanks!!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question