dspavlik
asked on
DISTINCTROW Problem
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
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
ASKER
That is returning too many records.
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!
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!
ASKER
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
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
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?
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?
"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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT Distinctrow * From $tablename Where PRODUCTTYPE = 'widgets' ORDER BY SUBCAT