[SQL] Select multiple columns with only one distinct column

[Code149]

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. :)

[kjordan2001]

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.

[Code149]

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?

[kjordan2001]

  On 07/02/2011 at 19:32, Code149 said:

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.

[BGM]

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

[spartyjohnson]

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.

[Code149]

  On 13/02/2011 at 00:05, spartyjohnson said:

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. :)