Solved

DISTINCTROW Problem

Posted on 2004-08-17
7
504 Views
Last Modified: 2013-12-12
Here is my query:

SELECT Distinctrow SUBCAT From $tablename Where PRODUCTTYPE = 'widgets' ORDER BY SUBCAT

I am trying to select the entire row but this is only returning the SUBCAT column.  The query is in a .php file and pulling from a MySQL database
0
Comment
Question by:dspavlik
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 17

Expert Comment

by:akshah123
ID: 11827079
Try:

SELECT Distinctrow * From $tablename Where PRODUCTTYPE = 'widgets' ORDER BY SUBCAT
0
 

Author Comment

by:dspavlik
ID: 11827108
That is returning too many records.
0
 
LVL 9

Expert Comment

by:e-tsik
ID: 11827512
Hi :-)

Selecting all columns present a problem. Basically, it is:

SELECT * From $tablename Where PRODUCTTYPE = 'widgets'
GROUP BY SUBCAT

Read the GROUP BY reference on mysql, you may want to aggregate some of your columns with MIN,AVG,SUM, etc.

(just an example)
SELECT SUBCAT, MIN(PRICE), MAX(SCORE) From $tablename Where PRODUCTTYPE = 'widgets'
GROUP BY SUBCAT

Enjoy!
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:dspavlik
ID: 11829615
Ok maybe I am not being clear.

In my query:
SELECT Distinctrow SUBCAT From $tablename Where PRODUCTTYPE = 'widgets' ORDER BY SUBCAT
 
I am trying to select the distinctrow based on the what is in the SUBCAT column and only selet those distinctrows if the PRODUCTTYPE = 'widgets'

What is being returned is just the distinct values from just the SUBCAT column, not all the columns in the table.

There has got to be an easy way to do this.....


Thanks
0
 
LVL 9

Expert Comment

by:e-tsik
ID: 11834387
I read your sentence 3 times

SELECT * From $tablename Where PRODUCTTYPE = 'widgets'
GROUP BY SUBCAT ORDER BY SUBCAT

SELECT *   -->  Select all columns
FROM $tablename --> From $tablename (what's so secret about that table??)
WHERE PRODUCTTYPE='widgets'   --->  "only selet those distinctrows if the PRODUCTTYPE = 'widgets'"
GROUP BY SUBCAT --> "select the distinctrow based on the what is in the SUBCAT column"
ORDER BY SUBCAT --> If you fancy it, why should I stand in your way?
0
 
LVL 9

Expert Comment

by:AlanJDM
ID: 11835242
"Ok maybe I am not being clear."

No, you are being very clear. However, you are not reading the replys very well. Your question has been answerd correctly and then explained.... twice.

SELECT Distinctrow * From $tablename Where PRODUCTTYPE = 'widgets' ORDER BY SUBCAT

That query does exactly what you asked. However, I think you are not asking for what you actually need.

"That is returning too many records."

As explained by e-tsik, this is because you are asking for the entire row, which means you will get a record returned for every unique value of each field not being grouped by. The way to resolve this, also already explained by e-tsik, is to employ aggregate funtions such as min,max,avg,sum,etc. on said fields.

If you post a few of the records returned that caused you to make the statement "That is returning too many records.", then I will try to explain further using those records as a visual example to illustrate what is going on.


Alan


0
 
LVL 9

Accepted Solution

by:
AlanJDM earned 500 total points
ID: 11835290
try...

SELECT Distinctrow * From $tablename Where PRODUCTTYPE = 'widgets' group by SUBCAT

instead of

SELECT Distinctrow * From $tablename Where PRODUCTTYPE = 'widgets' ORDER BY SUBCAT

and see if that is what you are looking for.


Alan
0

Featured Post

Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

Question has a verified solution.

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

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

729 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