SQL Server - Select DISTINCT columns along with a NON-DISTINCT column

Asked By Techhead

07-Mar-07 11:04 AM

I am trying to select distinct columns and add a non-disticnt column
to my list.

This query works great in finding distinct records:


SELECT DISTINCT
firstname,
lastname,
middleinitial,
address1,
address2,
city,
state,
zip,
age,
gender,

FROM table
ORDER by zip

However, I have a column called DATE that I want to add to the results
of my query but I DO NOT want this to be a DISTINCT column. How do I
go by excluding a column from being DISTINCT in my select distinct
statement but adding it after the fact?

07-Mar-07 11:08 AM

Can you give us an example with data?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON   Canada
.
I am trying to select distinct columns and add a non-disticnt column
to my list.

This query works great in finding distinct records:


SELECT DISTINCT
firstname,
lastname,
middleinitial,
address1,
address2,
city,
state,
zip,
age,
gender,

FROM table
ORDER by zip

However, I have a column called DATE that I want to add to the results
of my query but I DO NOT want this to be a DISTINCT column. How do I
go by excluding a column from being DISTINCT in my select distinct
statement but adding it after the fact?

07-Mar-07 11:11 AM

Which date do you want?  The earliest, the latest?

SELECT
firstname, lastname, etc.,
MIN(date_column),
MAX(date_column)
FROM
table
GROUP BY
firstname, lastname, etc.,
ORDER BY zip

--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

07-Mar-07 11:28 AM

I am not doing a WHERE function on my date column, I just want to add
this column to my DISTINCT results but do not want my date column to
be DISTINCT.

07-Mar-07 11:35 AM

Sorry, I do not do word problems very well.  Can you show some sample data
and desired results?  Just 10-20 rows with firstname and date should be
sufficient.

A

13-Mar-07 08:37 AM

Bank, Account, and Note must be distinct however, I want the distinct row returned to contain the most recent plan date.

Bank	Account	Note 	Plan Date

1	123	1	5/6/2001

1	123	1	3/13/2007

1	123	1	3/3/2005

1	123	1	5/8/2006

2	555	5	8/4/2006

2	555	5	9/8/2007

3	999	2	5/8/2006

3	999	2	5/8/2001

4	888	1	3/2/2005

4	888	1	3/9/2006

4	888	1	3/9/2007

23-Oct-07 06:46 AM

I tried to do the same thing myself and finally this is how I worked it out :



SELECT

rowCode <---- (Some unique numeral key)

firstname,lastname,address1 <---- distinct columns

plandate,plandate2 <---- non distinct columns



FROM table



WHERE rowCode IN (SELECT Min(rowCode) FROM table GROUP BY firstname,lastname,address1)



ORDER by firstname



This will return unique values for your distinct columns and will the first (min) value of your non distinct columns .

---------------------------------------------------



Actually in your case, because your 'non distinct' column has a 'date' type, you can solve it more easily by using the following code :



SELECT Bank, Account, Note, MAX(PlanDate)

FROM table

GROUP BY Bank, Account, Note

23-Oct-07 06:47 AM

I tried to do the same thing myself and finally this is how I worked it out :



SELECT

rowCode <---- (Some unique numeral key)

firstname,lastname,address1 <---- distinct columns

plandate,plandate2 <---- non distinct columns



FROM table



WHERE rowCode IN (SELECT Min(rowCode) FROM table GROUP BY firstname,lastname,address1)



ORDER by firstname



This will return unique values for your distinct columns and will the first (min) value of your non distinct columns .

---------------------------------------------------



Actually in your case, because your 'non distinct' column has a 'date' type, you can solve it more easily by using the following code :



SELECT Bank, Account, Note, MAX(PlanDate)

FROM table

GROUP BY Bank, Account, Note

08-Jan-11 11:50 AM

thank you worked well SQL SERVER 2008