Complicated 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

General SQL Server Forums

New to SQL Server Programming

Complicated distinct

Author

Topic

nagham
Starting Member

2 Posts

Posted - 2008-03-20 : 12:40:53

Hello,can anyone help me on this:
I have a table with 3 columns:id,time,description
let's say that I have this set of records:
ID TIME DESC
1 8:04 aa
1 8:05 aa
1 8:06 aa
2 8:07 ab
2 8:08 --
1 8:09 bb

I need a query that return
1 8:04 aa
2 8:07 ab
1 8:09 bb
means that from every set of same ID a need the distinct one,coz the first set of "1" refere to same person,the 4th "1" refere to different person.

Help me plz

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-20 : 13:43:33

There is nothing complex about this. Just use GROUP BY.

SELECT id, MIN(time) as time, description
FROM YourTable
GROUP BY id, description

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

modi_sanjay
Starting Member

9 Posts

Posted - 2008-03-20 : 14:16:40

i think your query not return the desire result
its return
2 8:08 --
1 8.05 aa
2 8:07 ab
1 8:09 bb

quote:
Originally posted by tkizer

There is nothing complex about this. Just use GROUP BY.

SELECT id, MIN(time) as time, description
FROM YourTable
GROUP BY id, description

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Thanks & Regards

Sanjay Modi

modi_sanjay
Starting Member

9 Posts

Posted - 2008-03-20 : 14:19:20

I have one quick question

How you recognize the first set of id "1" is different from the 4th "1"

first set of "1" refere to same person,the 4th "1" refere to different person.

quote:
Originally posted by nagham

Hello,can anyone help me on this:
I have a table with 3 columns:id,time,description
let's say that I have this set of records:
ID TIME DESC
1 8:04 aa
1 8:05 aa
1 8:06 aa
2 8:07 ab
2 8:08 --
1 8:09 bb

I need a query that return
1 8:04 aa
2 8:07 ab
1 8:09 bb
means that from every set of same ID a need the distinct one,coz the first set of "1" refere to same person,the 4th "1" refere to different person.

Help me plz

Thanks & Regards

Sanjay Modi

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-20 : 16:04:43

What version of SQL server are you using?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-20 : 18:51:31

You have to convert the varchar time to a real time first, then it will work.
Something like
CONVERT(VARCHAR(5),CONVERT(DATETIME,'01/01/1900 ' + 'field that looks like a time but isn't),121)
WHERE DESC <> '--'

Jim

nagham
Starting Member

2 Posts

Posted - 2008-03-21 : 12:16:27

quote:
Originally posted by modi_sanjay

I have one quick question

How you recognize the first set of id "1" is different from the 4th "1"

first set of "1" refere to same person,the 4th "1" refere to different person.

quote:
Originally posted by nagham

Hello,can anyone help me on this:
I have a table with 3 columns:id,time,description
let's say that I have this set of records:
ID TIME DESC
1 8:04 aa
1 8:05 aa
1 8:06 aa
2 8:07 ab
2 8:08 --
1 8:09 bb

I need a query that return
1 8:04 aa
2 8:07 ab
1 8:09 bb
means that from every set of same ID a need the distinct one,coz the first set of "1" refere to same person,the 4th "1" refere to different person.

Help me plz

Thanks & Regards

Sanjay Modi

well I have nothing help me to know that the first set is different from the other one,I just want that if at the next record the id is the same I don't want to take it when the id is different I take it,that is why I need
1 8:04
2 8:07
1 8:09

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-21 : 13:00:20

DECLARE @Table TABLE (ID int, [TIME] varchar(5),[DESC] varchar(5))

INSERT INTO @Table
SELECT 1, '8:04', 'aa' union all
SELECT 1,' 8:05', 'aa' union all
SELECT 1, '8:06', 'aa' union all
SELECT 2, '8:07', 'ab' union all
SELECT 2,'8:08', '--' union all
SELECT 1,'8:09', 'bb'

SELECT ID, CONVERT(varchar(5),MIN(CONVERT(DATETIME,'01/01/1900 ' + [TIME])),108),[DESC]

FROM @table
WHERE [DESC] <> '--'

GROUP BY ID,[DESC]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources