Using DISTINCT - SQL Server Forums

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

Transact-SQL (2000)

Using DISTINCT

Author

Topic

d473566
Starting Member

23 Posts

Posted - 2004-03-12 : 14:06:11

Hi

I need some help selecting from a table...I think DISTINCT will do it, but not sure how...

Here is a typical recordset I would have. I get to this point in a stored proc by creating a temp table and inserting into it, but dont want to bore you with those details. Just know that the end result is that the temp table will look just like the table created below....

CREATE TABLE TestTable([ID] INT NOT NULL ,
[Name] VARCHAR(100),
Version INT,
IsPreviouslyApproved INT)

INSERT TestTable ([ID],[Name],Version,IsPreviouslyApproved)
VALUES (1,'Test1',1,1)

INSERT TestTable ([ID],[Name],Version,IsPreviouslyApproved)
VALUES (1,'Test1',2,0)

INSERT TestTable ([ID],[Name],Version,IsPreviouslyApproved)
VALUES (2,'Test2',1,1)

INSERT TestTable ([ID],[Name],Version,IsPreviouslyApproved)
VALUES (3,'Test3',1,1)

INSERT TestTable ([ID],[Name],Version,IsPreviouslyApproved)
VALUES (3,'Test3',2,0)

SELECT [ID],
[Name],
Version,
IsPreviouslyApproved
FROM TestTable

DROP TABLE TestTable

What I need to be able to do is select 1 row for each ID. So using the above example, I would have a result consisting of:

ID | Name | Version | IsPreviouslyApproved
-------------------------------------------
1 | Test1 | 2 | 0
2 | Test2 | 1 | 1
3 | Test3 | 2 | 0

There must be a easy way to do this that I am just having a brain cramp on...right?

Thanks again for any help...

Derek

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-12 : 14:33:06

Nice post....I appreciate the DDL and the DML...



SELECT    [ID]
	, [Name]
	, Version
	, IsPreviouslyApproved 
  FROM TestTable o
 WHERE EXISTS (   SELECT ID FROM TestTable i
		   WHERE i.[ID] = o.[ID]
		GROUP BY ID
		  HAVING o.Version= MAX(i.Version))

Brett

8-)

d473566
Starting Member

23 Posts

Posted - 2004-03-12 : 14:55:37

Thanks Brett...works beautifully.

So the logic is that you select the highest version number, and then join the table to itself based on the version number and the [ID]?

...or something like that?

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-12 : 15:02:31

That's what I guessed...

And I gotta say again...DDL sample data and Expected results...

Don't need more of an explination...

great post!

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources