DISTINCT problem

[Styles]

Hi all,

I posted a question a while back about trying to get a table to return only distinct values for an ID field (not autoincrement) and have the rest of the data come with it. I'm posting the code here in hopes this will help.

Here's the table:
CREATE TABLE [tblHardware] (
[ID] [int] NULL ,
[CodexYN] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Justification] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DeskVert] [int] NULL ,
[DeskHor] [int] NULL ,
[DeskRec] [int] NULL ,
[LapSm] [int] NULL ,
[LapBig] [int] NULL ,
[LapRec] [int] NULL ,
[Comments] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IDauto] [int] IDENTITY (1, 1) NOT NULL ,
[Number] [bit] NULL ,
[txtSoftware] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[approve] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CommentsBack] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ApproveDS] [datetime] NULL ,
[Reclaim] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_tblHardware_Reclaim] DEFAULT (N'Yes')
) ON [PRIMARY]
GO


I want to query the table, something like this:
SELECT DISTINCT ID AS Expr1, dbo.tblHardware.*
FROM dbo.tblHardware

But the dups in the table still come up.

Thanks in advance for the help!

[MAK]

create table mytabley (id int, name varchar(100), salary int, bonus int)
insert into mytabley select 1,'MAK',7800,0
insert into mytabley select 2,'Claire',7800,0
insert into mytabley select 3,'Sam',7800,0
insert into mytabley select 4,'Eiko Koike',7820,0
insert into mytabley select 4,'Eiko Koike',7800,0
insert into mytabley select 5,'MAK',7800,0
insert into mytabley select 5,'MAK',7800,0

select distinct * from mytabley

--result
1,MAK,7800,0
2,Claire,7800,0
3,Sam,7800,0
4,Eiko Koike,7800,0
4,Eiko Koike,7820,0
5,MAK,7800,0

--Here you can see that rows with id=5 has all the columns are duplicated but not in ID=4.
--In ID=4 you can see the salaries are different

[Styles]

select distinct * from mytabley

--result
1,MAK,7800,0
2,Claire,7800,0
3,Sam,7800,0
4,Eiko Koike,7800,0
4,Eiko Koike,7820,0
5,MAK,7800,0

--Here you can see that rows with id=5 has all the columns are duplicated but not in ID=4.
--In ID=4 you can see the salaries are different [/B][/QUOTE]


***Ok, I see what you're saying, and unfortunately, that's what I'm getting too. What I'm trying to get (using your example) would be:

1,MAK,7800,0
2,Claire,7800,0
3,Sam,7800,0
4,Eiko Koike,7800,0
5,MAK,7800,0

I need that ID field to be distinct, and for the rest of the record to follow it. In my table, records are dups, so where as your #4 above had 2 diff saleries, mine wouldn't.

thanks!

[MAK]

--You can use Update statement to solve this distinct issue
--drop table mytabley
--drop table mytablez
create table mytabley (id int, name varchar(100), salary int, bonus int)
insert into mytabley select 1,'MAK',7801,0
insert into mytabley select 2,'Claire',7802,0
insert into mytabley select 3,'Sam',7820,0
insert into mytabley select 4,'Eiko Koike',7820,0
insert into mytabley select 4,'Eiko Koike',7800,0
insert into mytabley select 5,'MAK',7800,0
insert into mytabley select 5,'MAK',7800,0

--step1
select * into Mytablez from mytabley where 1=0
--step2
insert into Mytablez (id) select distinct ID from mytabley
--step3
update A set A.name=b.name,a.salary=b.salary,a.bonus=b.bonus
from mytablez A, mytabley b where a.id =b.id
--step4
select * from mytablez

[Styles]

[QUOTE]Originally posted by MAK
[B]--You can use Update statement to solve this distinct issue

This isn't going to work. The App. is running live, I can't delete tables and recreate them whenever a dup appears, and it will keep happening.

I need to make a view that will pull out unique IDs from a table, and the rest of the record that goes with that ID.

So if the info in the table looks like this:

ID__First___Last
1 John Doe
1 John Doe
2 Jane Doe
3 Hal Doe
3 Hal Doe


I would need to get this:

1 John Doe
2 Jane Doe
3 Hal Doe

Thanks again..

[MAK]

--Another way to solve it is using correlated subquery


Select distinct ID, (Select top 1 name from mytabley x where x.id=y.id) as name,
(Select top 1 salary from mytabley x where x.id=y.id) as salary,
(Select top 1 salary from mytabley x where x.id=y.id) as bonus
from mytabley y

[MAK]

--In your case...
Create table mytablew (id int, first varchar(100), last varchar(100))
insert into mytablew select 1, 'John', 'Doe'
insert into mytablew select 1, 'John', 'Doe'
insert into mytablew select 2, 'Jane', 'Doe'
insert into mytablew select 3, 'Hal', 'Doe'
insert into mytablew select 3, 'Hal', 'Doe'

Select distinct ID, (Select top 1 first from mytablew x where x.id=y.id) as First,
(Select top 1 Last from mytablew x where x.id=y.id) as Last
from mytablew y