How to make ID unique? - 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

SQL Server Development (2000)

How to make ID unique?

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2006-10-04 : 14:35:55

I have a table as below.
Now, I need make ID unique and latest ORDERDATE.
I use a view to group ID and select Max Distinct for ORDERDATE.
How to select CODE? Either Max Distinct and Min Distinct did not select right data.

ID ORDERDATE CODE
11 10/11/2006 3
11 10/12/2006 1
11 10/13/2006 2

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-04 : 14:51:51

What is the "right data". If you're grouping rows you have to tell SQL Server what to give you, it cannot guess what the right data might be.

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2006-10-04 : 15:07:02

This is my question.
Once I group ID, and take Max Distinct for ORDERDATE, I do not care CODE. I want to take whatever it is. For this example, it is 2

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-10-04 : 15:46:18

What is the primary key for this table? Using only the info provided thus far you might be looking for this:

declare @table table (ID int, OrderDate datetime, Code int)
insert into @table
	select 11, '10/11/2006', 3 union all
	select 11, '10/12/2006', 1 union all
	select 11, '10/13/2006', 2

select * from @table

select t.ID, t.OrderDate, t.Code
from @table t
join	(	select ID, max(OrderDate) OrderDate
			from @Table
			group by ID)d
on t.ID = d.ID and t.OrderDate = d.OrderDate

* But what would the desired resultset be given this input data:

	select 11, '10/11/2006', 3 union all
	select 11, '10/12/2006', 1 union all
	select 11, '10/13/2006', 2 union all
	select 11, '10/13/2006', 4

Nathan Skerl

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2006-10-04 : 16:46:34

There is no primary key. That is why I need to make ID as primary key. But before that, I need to get rid of duplicate ID.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-04 : 17:53:57

quote:
Originally posted by Sun Foster

This is my question.
Once I group ID, and take Max Distinct for ORDERDATE, I do not care CODE. I want to take whatever it is. For this example, it is 2

So I think what you're saying is that you want the code from the row with the max date? If so then there is a correct code, and you do care. If you didn't care then min code or max code would have been OK.

So assuming that you want the code for the max date, you'll need to do this

SELECT [ID], max(OrderDate), 
    (SELECT Code 
         FROM MyTable 
         WHERE [ID] = t.[ID] AND OrderDate = max(t.OrderDate)) AS Code
FROM MyTable t
GROUP BY [ID]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources