select distinct columns, nondistinct column from table

Question

select distinct columns, nondistinct column from table

rahulsony111

Hall of Fame

Points: 3865
More actions
June 29, 2010 at 10:03 am

#226268

Hi,
I have a table with columns proj_id char(5), tenant_id char(6), lease_id char(8), Sales_yr numeric(4)
i want to distinct only proj_id, tenant_Id, Lease_id but not sales_yr how can we do that
Thanks,

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

rahulsony111 (6/29/2010)
Hi,
I have a table with columns proj_id char(5), tenant_id char(6), lease_id char(8), Sales_yr numeric(4)
i want to distinct only proj_id, tenant_Id, Lease_id but not sales_yr how can we do that
Thanks,

To get distinct of what you asked, do:

SELECT DISTINCT proj_id, tenant_Id, Lease_id FROM Table

Nice question, could you please clarify a bit how you want to show sales_yr? Do you want to show maximum, minimum or average value of sales_yr or may be you want to sum it up?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 2

Eugene Elutin (6/29/2010)
rahulsony111 (6/29/2010)
Hi,
I have a table with columns proj_id char(5), tenant_id char(6), lease_id char(8), Sales_yr numeric(4)
i want to distinct only proj_id, tenant_Id, Lease_id but not sales_yr how can we do that
Thanks,
To get distinct of what you asked, do:
SELECT DISTINCT proj_id, tenant_Id, Lease_id FROM Table
Nice question, could you please clarify a bit how you want to show sales_yr? Do you want to show maximum, minimum or average value of sales_yr or may be you want to sum it up?

Just a guess, but you might be wanting the following:

SELECT

proj_id,

tenant_Id,

Lease_id,

sum( sales_yr ) [sales_yr]

group by proj_id, tenant_Id, Lease_id

Converting oxygen into carbon dioxide, since 1955.

rahulsony111 Hall of Fame Points: 3865 More actions · Answer 3

Thank you for the reply,

the Sales_yr is a Year column it has values like 2009, 2010, 2011 etc,...

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

rahulsony111 (6/29/2010)
Thank you for the reply,
the Sales_yr is a Year column it has values like 2009, 2010, 2011 etc,...

Cool! It explains everything!

So, you want an average value, isn't it? 😀

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]