manukaucouncil
asked on
Select distinct column
Hi,
I have a database with where a value in a column may appear many times. It would be easy to select the distinct values from this column on its own but I need to also return other columns. Therefore the distinct clause will apply to all the columns being returned (so distinct is where column a, b, c and d are distinct). I suppose then my question is simply how do I return distinct values from column a and also return column b and c (therefore just taking say the distinct values from column a and ignoring the fact that value may appear again in the column).
Hope you can understand me.
thanks in advance
I have a database with where a value in a column may appear many times. It would be easy to select the distinct values from this column on its own but I need to also return other columns. Therefore the distinct clause will apply to all the columns being returned (so distinct is where column a, b, c and d are distinct). I suppose then my question is simply how do I return distinct values from column a and also return column b and c (therefore just taking say the distinct values from column a and ignoring the fact that value may appear again in the column).
Hope you can understand me.
thanks in advance
ASKER
when you use "select distinct colA, colB, colC, colD from sourceTable" does that mean that colA, colB, colC and colD need to be distinct together or is it just distinct colA? Say for example I want to just return distinct colB values... how would I do this as select colA, distinct colB....... does not work.
If you just want distinct colB values then you can do
select distinct colB from sourceTable
for distinct colA then
select distinct colA from sourceTable
for every distinct combination of colA and colB you
select distinct colA, colB from sourceTable
What are your columns? What are you trying to achieve?
select distinct colB from sourceTable
for distinct colA then
select distinct colA from sourceTable
for every distinct combination of colA and colB you
select distinct colA, colB from sourceTable
What are your columns? What are you trying to achieve?
ASKER
This is my script:
select amount_id, adjust_id, amttyp_id, description, order_no, essbase_account_id, essbase_entity_id, ltrim(STR(adjust_id)) + 'a' + ltrim(STR(order_no)) AS adjust_order, amount_id from atm_amount where essbase_account_id in (select dimension_1_node_key from dss_dimension_1 where node_desc = 'PS3001') and adjust_id in (select distinct adjust_id from atm_adjustment where adjtyp_id in (1,2) and status_id <7) order by adjust_id,order_no
Bascically I just want to return the distinct adjust_id's in the outer select statement but with the other information still attached to it.
select amount_id, adjust_id, amttyp_id, description, order_no, essbase_account_id, essbase_entity_id, ltrim(STR(adjust_id)) + 'a' + ltrim(STR(order_no)) AS adjust_order, amount_id from atm_amount where essbase_account_id in (select dimension_1_node_key from dss_dimension_1 where node_desc = 'PS3001') and adjust_id in (select distinct adjust_id from atm_adjustment where adjtyp_id in (1,2) and status_id <7) order by adjust_id,order_no
Bascically I just want to return the distinct adjust_id's in the outer select statement but with the other information still attached to it.
do you mean you want
adjust_id in (select distinct adjust_id from atm_adjustment where adjtyp_id in (1,2) and status_id <7) order by adjust_id,order_no
to
adjust_id in (select distinct adjust_id, other1,other2 from atm_adjustment where adjtyp_id in (1,2) and status_id <7) order by adjust_id,order_no
if you add other information in the outer select you cannot use it with "in" condition.
why do you need other information in that outer sql? can you explain what you are trying to do?
adjust_id in (select distinct adjust_id from atm_adjustment where adjtyp_id in (1,2) and status_id <7) order by adjust_id,order_no
to
adjust_id in (select distinct adjust_id, other1,other2 from atm_adjustment where adjtyp_id in (1,2) and status_id <7) order by adjust_id,order_no
if you add other information in the outer select you cannot use it with "in" condition.
why do you need other information in that outer sql? can you explain what you are trying to do?
ASKER
Say for example I have this data:
ClassName ClassDescription StudentName Mark
SQL Server All about SQL Jeremy A
VB All about VB Jeremy A+
SQL Server All about SQL 2 Bob B
Now I want to do something like this: "select distinct classname, classdescription, studentname, mark from table"
The problem with this script is that it will recognise rows 1 and 3 as distinct because they have a different description.
So I really only want the distinct clause to apply to classname so the results return as follows:
ClassName ClassDescription StudentName Mark
SQL Server All about SQL Jeremy A
VB All about VB Jeremy A+
So it just ignores the second occurance of SQL Server in classname.
I hope that makes it a bit clearer.
ClassName ClassDescription StudentName Mark
SQL Server All about SQL Jeremy A
VB All about VB Jeremy A+
SQL Server All about SQL 2 Bob B
Now I want to do something like this: "select distinct classname, classdescription, studentname, mark from table"
The problem with this script is that it will recognise rows 1 and 3 as distinct because they have a different description.
So I really only want the distinct clause to apply to classname so the results return as follows:
ClassName ClassDescription StudentName Mark
SQL Server All about SQL Jeremy A
VB All about VB Jeremy A+
So it just ignores the second occurance of SQL Server in classname.
I hope that makes it a bit clearer.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ahhh that certainly makes sense. Thanks
or
select colA, colB, colC, colD
from sourceTable
group by colA, colB, colC, colD
Not sure if that answers your question mind, as I'm not really sure what you are asking.
David