Sign in to follow this  
Followers 0

7 posts in this topic

How do you select multiple columns from a table while ensuring that one specific column doesn't contain duplicate values?

SELECT DISTINCT col1,col2,col3,col4 from table

This doesn't work, because DISTINCT here applies to all columns so columns as a whole are distinct. But I want a single column to have distinct values.

I'm using SQL Server. :)

Share this post


Link to post
Share on other sites

You're looking for GROUP BY. If you wanted to only have the value for col1 show up once in the dataset:

SELECT col1,col2,col3,col4 from table GROUP BY col1

Note that this will compact all the results that would have been display into one row. So you'll end up having to use sums or some other aggregate function to display the correct values if you can expect a different value in a row each time it shows up. Otherwise it will just pick the value from the first row.

1 person likes this

Share this post


Link to post
Share on other sites

Thanks for your answer, it actually does serve my purpose. :)

This is the query I ran:

SELECT col1,max(col2), max(col3), max(col4) from table GROUP BY col1

Max functions used here don't mean much, they'er just there to pick the biggest value of the column in the row set.

As I said, in this case it serves my purpose. But what if there were, let's say 4 or more rows with a common col1 value, and you wanted to retrieve columns of some specific middle row among the grouped rows; How would you go about that?

I'm thinking cursors can be one way to do that, right?

Share this post


Link to post
Share on other sites

Thanks for your answer, it actually does serve my purpose. :)

This is the query I ran:

SELECT col1,max(col2), max(col3), max(col4) from table GROUP BY col1

Max functions used here don't mean much, they'er just there to pick the biggest value of the column in the row set.

As I said, in this case it serves my purpose. But what if there were, let's say 4 or more rows with a common col1 value, and you wanted to retrieve columns of some specific middle row among the grouped rows; How would you go about that?

I'm thinking cursors can be one way to do that, right?

I believe you could (SQL Server is not my strongest database skill), but if you can it would be better if you can weed out the row you want using the WHERE clause of the SQL statement.

Share this post


Link to post
Share on other sites

this is no concept of 'middle' in MSSQL table, or any other? so it's a bit tricky..

can you give more information on your requirement?

where there's a will, there's a way! :D

Share this post


Link to post
Share on other sites

It sounds like you need to do something like the following:

select
* --should specify your specific columns
from table1 t1
inner join 
(select DISTINCT distinctColumn from table) t2
on t1.col = t2.distinctColumn

This code should get your distinct column and then join on it from another (or even the same) table.

Share this post


Link to post
Share on other sites

It sounds like you need to do something like the following:

select
* --should specify your specific columns
from table1 t1
inner join 
(select DISTINCT distinctColumn from table) t2
on t1.col = t2.distinctColumn

This code should get your distinct column and then join on it from another (or even the same) table.

No, doesn't work. It returns duplicate rows.

It doesn't matter tho, as my problem is solved, even ignoring the @kjordan2001's answer. I just needed to omit those non-duplicate valued columns from the select list of a distinct clause, which were trivial anyways. The result set would then only include entirely distinct rows. And FYI, the table in question is named Customer and is part of the AdventureWorks2008R2 sample databases.

Thanks for answers though. :)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Recently Browsing   0 members

    No registered users viewing this page.