How do I select 1 from duplicate rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How do I select 1 from duplicate rows

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-03-21 : 08:11:11

Tony writes "I have a query that creates a table (Table C) from two others (A & B). There is a one to many relationship between tables A & B. I can not have duplicate records in Table C. Obviously, there are no Dupes in table A. Table A has Listing information from a MLS. Table B contains openhouse information. Again obviously, there maybe 1 or more openhouses scheduled for each MLSID.

If I join Tables A & B I get duplicate records in TableC when there are more than one openhouses for any MLSID. I would like to pull only one of the records from tableB. If possible I would always like to pull the next date that is greater than today.

I can not figure out how to do this. Any ideas?
Distinct works if I am only selecting the MLSID field and fields that the equal to what is in the duplicate record. BUt when I include the date it Distinct does not work.

What am I missing?"

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-21 : 08:25:54

select ...otherfields... min(date)
from table
where date > getdate()

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-03-21 : 08:32:04

If it is just the date that you are inserting from the many side of the join, then:
1. add to the where clause to constrain the resultset to dates greater than today.
2. group by all other columns and use the MIN aggregate function to return the first available date in the select clause.

I.e.

SELECT   MIN(Date), 
         OtherCol1, 
         OtherCol2
FROM     TableA AS a
JOIN     TableB AS b
ON       a.JoinCol = b.JoinCol
WHERE    b.Date > CURRENT_TIMESTAMP  -- Might need to account for time element
GROUP BY OtherCol1,
         OtherCol2

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources