Can you give us an example with data? |
Tom Moreau posted on Wednesday, March 07, 2007 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? |
 |
|
Which date do you want? The earliest, the latest? |
Aaron Bertrand [SQL Server MVP] posted on Wednesday, March 07, 2007 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 |
 |
|
Select DISTINCT columns along with a NON-DISTINCT column |
Techhead posted on Wednesday, March 07, 2007 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. |
 |
|
Sorry, I don't do word problems very well. |
Aaron Bertrand [SQL Server MVP] posted on Wednesday, March 07, 2007 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 |
 |
|
I need this answered also. |
Jeff posted on Tuesday, March 13, 2007 8: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 |
 |
|
Maybe this can help |
Gilad posted on Tuesday, October 23, 2007 6: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 |
 |
|
Maybe this can help |
Gilad posted on Tuesday, October 23, 2007 6: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 |
 |
|