Solved

Select distinct column

Posted on 2006-11-26
8
482 Views
Last Modified: 2007-08-27
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
0
Comment
Question by:manukaucouncil
  • 4
  • 2
  • 2
8 Comments
 
LVL 3

Expert Comment

by:dave_gr
ID: 18015474
select distinct colA, colB, colC, colD from sourceTable

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
0
 

Author Comment

by:manukaucouncil
ID: 18015527
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.
0
 
LVL 3

Expert Comment

by:dave_gr
ID: 18015703
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?

0
 

Author Comment

by:manukaucouncil
ID: 18015922
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.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 39

Expert Comment

by:appari
ID: 18016031
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?
0
 

Author Comment

by:manukaucouncil
ID: 18016244
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.
0
 
LVL 39

Accepted Solution

by:
appari earned 250 total points
ID: 18016377
in case of class name repeatition which record you want to show?
but studentname is different in your sample data. even if studentname is different you want to show only one record?
in that case you have to use group by classname and use some aggregate function on the remaining columns.
decide on what to use max or min .

SQL will be something like this

select ClassName, min(ClassDescription) ClassDescription, min(StudentName) StudentName, max(Mark) Mark
from yourtable
0
 

Author Comment

by:manukaucouncil
ID: 18016380
Ahhh that certainly makes sense. Thanks
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now