| Author |
Topic |
|
WelshPunk
Yak Posting Veteran
67 Posts |
 Posted - 2008-10-22 : 05:51:53
|
| Hello there I have a select statement that brings back 17 rows. Within the recordset there is a field called order_no. Does anyone know how to return all the same fields but somehow making the order number distinctSQL SELECT maint_plan_id, planner_group, planned_dte, actual_dte, area, func_loc, description, short_text, prep_status, job_status, importance, prep_ready, prep_required, comment, order_noFROM dbo.maint_planWHERE (is_deleted <>'Y')ORDER BY actual_dte |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-10-22 : 06:00:16
|
| what if you have more than one maint_plan_id or planner_group for the same order_no? |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-22 : 06:07:08
|
| for that you need to apply GROUP BY on order_no. but obviously you will be able to return only one value out of all record values which exists for order_no |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-10-22 : 06:18:28
|
quote:
Originally posted by visakh16
for that you need to apply GROUP BY on order_no. but obviously you will be able to return only one value out of all record values which exists for order_no
I was asking for the kind of grouping Welshpunk would want if there are more than 1 rows for the same order. Not sure if he actually wants a grouping done, going by the name of the columns ,,,descriptions etc. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-10-22 : 06:35:30
|
| use row_number() over (order by order_no) which will create a sequence based on the order number (something like that anyway - check the syntax). The question I have is, what use is the actual order number if you can't use it? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-22 : 06:44:08
|
quote:
Originally posted by LoztInSpace
use row_number() over (order by order_no) which will create a sequence based on the order number (something like that anyway - check the syntax). The question I have is, what use is the actual order number if you can't use it?
only if you've sql 2005 or later you can use row_number() function. |
|
|
|
WelshPunk
Yak Posting Veteran
67 Posts |
Posted - 2008-10-22 : 07:45:50
|
| Thanks for all the suggestions. I used the grouped by order no but then had to use Max for all other fields to get the required detailSELECT MAX(DISTINCT maint_plan_id) AS ID, order_no AS [Order No], MAX(planner_group) AS [When], MAX(planned_dte) AS Planned, MAX(actual_dte) AS Actual, MAX(area) AS Area, MAX(func_loc) AS [Func Loc], MAX(description) AS Description, MAX(short_text) AS [Short Text], MAX(importance) AS Importance, MAX(prep_ready) AS [Prep Ready], MAX(prep_required) AS [Prep Req], MAX(comment) AS CommentFROM dbo.maint_planWHERE (is_deleted <> 'Y')GROUP BY order_no |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-10-22 : 07:49:00
|
quote:
Originally posted by WelshPunk
Thanks for all the suggestions. I used the grouped by order no but then had to use Max for all other fields to get the required detailSELECT MAX(DISTINCT maint_plan_id) AS ID, order_no AS [Order No], MAX(planner_group) AS [When], MAX(planned_dte) AS Planned, MAX(actual_dte) AS Actual, MAX(area) AS Area, MAX(func_loc) AS [Func Loc], MAX(description) AS Description, MAX(short_text) AS [Short Text], MAX(importance) AS Importance, MAX(prep_ready) AS [Prep Ready], MAX(prep_required) AS [Prep Req], MAX(comment) AS CommentFROM dbo.maint_planWHERE (is_deleted <> 'Y')GROUP BY order_no
Does it make sense for you to pick up the max value ??? |
|
|
|
WelshPunk
Yak Posting Veteran
67 Posts |
Posted - 2008-10-22 : 07:56:14
|
| When I use group by on order_no it complains that all the other fields are not valid as they are not contained in a aggregate function. I chose Max as it doesnt change the returned text fields |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-10-22 : 08:05:30
|
| right,, You're fine with the query. But is the result actually what you're looking for ?You could have chosen any other grouping function like min,count,.. etc. Did you chose max just to make the query run good?? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-22 : 08:21:54
|
quote:
Originally posted by WelshPunk
When I use group by on order_no it complains that all the other fields are not valid as they are not contained in a aggregate function. I chose Max as it doesnt change the returned text fields
please note that when you take MAX() on all columns, the field values you get may not always be belonging to same record. MAX() just looks for values available in all the records in each group and takes the maximum value .If you're expecting a random value, then its fine. |
|
|
|
WelshPunk
Yak Posting Veteran
67 Posts |
Posted - 2008-10-22 : 08:29:03
|
| I tried a few like count etc first - but this returned a count on the text fields where I only wanted the text and not the number of chars etc. Cant understand why there is not an aggregate datatype called 'billy' which tells the system to use it as an aggregate function - but just leave the data alone. That way you could use a group by to get the rows you want - without having to jump thru hoops to get the returned values you need. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-22 : 08:30:17
|
quote:
Originally posted by WelshPunk
I tried a few like count etc first - but this returned a count on the text fields where I only wanted the text and not the number of chars etc. Cant understand why there is not an aggregate datatype called 'billy' which tells the system to use it as an aggregate function - but just leave the data alone. That way you could use a group by to get the rows you want - without having to jump thru hoops to get the returned values you need.
what as per your requirement will be data you are interested in? latest or first member of each group? |
|
|
|
WelshPunk
Yak Posting Veteran
67 Posts |
Posted - 2008-10-22 : 08:41:48
|
| It does not matter which latest or oldest. I will create a hyperlink text field into the grid (on order-no) When the user selects this they have the abillity to say that this job is complete. When this gets done I will run an update procedure that will update all order_no's to completed.This sollution is one step in four gridviews. There are four different user groups that each need to see different fields from the table. For instance the shif managers need to see all individual order_no's . The operators(this query) only need to see if the order_no is complete or still outstanding. If that makes sense. So even though there are multiple entries in one gridview - in the opreators view we only need one row for each order_no |
|
|
|
|
Select Distinct