DISTINCT PROBLEM - 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)

DISTINCT PROBLEM

Author

Topic

duplicity
Starting Member

4 Posts

Posted - 2006-09-08 : 15:17:25

I have a table having columns:

ID | Phone | Date | Position | Status

Only the ID field is identity. What i want to do is to have DISTINCT Phones with latest Date, and if dates are same, with the latest ID.
Moreover, I like the data to be sorted by Position.

I tried to write a lot of statements but couldn't succeed.

Please help me with it. Below is an example, in which red lines show the data i am after. Thanks

1 , 1111111 , 20060917 , 5 , active
2 , 1111111 , 20060918 , 3 , passive
3 , 1111111 , 20060918 , 7 , active
4 , 2222222 , 20060917 , 5 , passive
5 , 1111111 , 20060917 , 3 , active
6 , 3333333 , 20060917 , 5 , active
7 , 3333333 , 20060915 , 6 , active
8 , 5555555 , 20060912 , 4 , passive
9 , 5555555 , 20060912 , 3 , passive
10, 4444444 , 20060917 , 8 , active
11, 6666666 , 20060913 , 2 , passive
12, 4444444 , 20060917 , 1 , passive
13, 5555555 , 20060911 , 1 , active
14, 6666666 , 20060917 , 4 , active

The sorted data should look like this:

12, 4444444 , 20060917 , 1 , passive
9 , 5555555 , 20060912 , 3 , passive
14, 6666666 , 20060917 , 4 , active
4 , 2222222 , 20060917 , 5 , passive
6 , 3333333 , 20060917 , 5 , active
3 , 1111111 , 20060918 , 7 , active

Is it hard ??

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-08 : 15:44:48

SELECT PHONE, MAX(DATE), MAX(ID) FROM myTable99 GROUP BY PHONE

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-08 : 16:00:30

assuming you want the attributes from the same row


create table #junk (ID int, Phone int, Date datetime, Position int, Status varchar(15))
insert #junk 
select 1 , 1111111 , '20060917' , 5 , 'active' union all
select 2 , 1111111 , '20060918' , 3 , 'passive' union all
select 3 , 1111111 , '20060918' , 7 , 'active' union all
select 4 , 2222222 , '20060917' , 5 , 'passive' union all
select 5 , 1111111 , '20060917' , 3 , 'active' union all
select 6 , 3333333 , '20060917' , 5 , 'active' union all
select 7 , 3333333 , '20060915' , 6 , 'active' union all
select 8 , 5555555 , '20060912' , 4 , 'passive' union all
select 9 , 5555555 , '20060912' , 3 , 'passive' union all
select 10, 4444444 , '20060917' , 8 , 'active' union all
select 11, 6666666 , '20060913' , 2 , 'passive' union all
select 12, 4444444 , '20060917' , 1 , 'passive' union all
select 13, 5555555 , '20060911' , 1 , 'active' union all
select 14, 6666666 , '20060917' , 4 , 'active'


select	a.*
from	#junk a
join	(
	select phone, max(convert(varchar, date,120)+str(id,12)) concatStr
	from 	#junk
	group by phone
	) b
		on b.phone = a.phone
		and convert(datetime, substring(b.concatStr, 1, 19)) = a.date
		and convert(int, substring(b.concatStr, 20, 12)) = a.id
order by a.position

output:
ID          Phone       Date                                                   Position    Status          
----------- ----------- ------------------------------------------------------ ----------- --------------- 
12          4444444     2006-09-17 00:00:00.000                                1           passive
9           5555555     2006-09-12 00:00:00.000                                3           passive
14          6666666     2006-09-17 00:00:00.000                                4           active
4           2222222     2006-09-17 00:00:00.000                                5           passive
6           3333333     2006-09-17 00:00:00.000                                5           active
3           1111111     2006-09-18 00:00:00.000                                7           active

Be One with the Optimizer
TG

duplicity
Starting Member

4 Posts

Posted - 2006-09-08 : 16:01:56

quote:
Originally posted by X002548

SELECT PHONE, MAX(DATE), MAX(ID) FROM myTable99 GROUP BY PHONE

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Gives me a result like:

11111111 20060918 5
22222222 20060917 4
33333333 20060917 7
44444444 20060917 12
55555555 20060912 13
66666666 20060917 14

which as you see is not correct. I don't want the maximum values of the group. I want all the row selected, where DATE is max, and if date is same ID is max.

duplicity
Starting Member

4 Posts

Posted - 2006-09-08 : 16:15:11

quote:

Originally posted by TG

assuming you want the attributes from the same row


create table #junk (ID int, Phone int, Date datetime, Position int, Status varchar(15))
insert #junk 
select 1 , 1111111 , '20060917' , 5 , 'active' union all
select 2 , 1111111 , '20060918' , 3 , 'passive' union all
select 3 , 1111111 , '20060918' , 7 , 'active' union all
select 4 , 2222222 , '20060917' , 5 , 'passive' union all
select 5 , 1111111 , '20060917' , 3 , 'active' union all
select 6 , 3333333 , '20060917' , 5 , 'active' union all
select 7 , 3333333 , '20060915' , 6 , 'active' union all
select 8 , 5555555 , '20060912' , 4 , 'passive' union all
select 9 , 5555555 , '20060912' , 3 , 'passive' union all
select 10, 4444444 , '20060917' , 8 , 'active' union all
select 11, 6666666 , '20060913' , 2 , 'passive' union all
select 12, 4444444 , '20060917' , 1 , 'passive' union all
select 13, 5555555 , '20060911' , 1 , 'active' union all
select 14, 6666666 , '20060917' , 4 , 'active'


select	a.*
from	#junk a
join	(
	select phone, max(convert(varchar, date,120)+str(id,12)) concatStr
	from 	#junk
	group by phone
	) b
		on b.phone = a.phone
		and convert(datetime, substring(b.concatStr, 1, 19)) = a.date
		and convert(int, substring(b.concatStr, 20, 12)) = a.id
order by a.position

output:
ID          Phone       Date                                                   Position    Status          
----------- ----------- ------------------------------------------------------ ----------- --------------- 
12          4444444     2006-09-17 00:00:00.000                                1           passive
9           5555555     2006-09-12 00:00:00.000                                3           passive
14          6666666     2006-09-17 00:00:00.000                                4           active
4           2222222     2006-09-17 00:00:00.000                                5           passive
6           3333333     2006-09-17 00:00:00.000                                5           active
3           1111111     2006-09-18 00:00:00.000                                7           active

Be One with the Optimizer
TG

GREAT TG

THAT SOLVED MY PROBLEM. I JUST NEEDED TO CHANGE LIKE THIS:

SELECT a.ID, a.PHONE, a.date, a.Position, a.Status
FROM table99 AS a INNER JOIN
(SELECT PHONE, MAX(date + STR(ID, 12)) AS concatStr
FROM table99
GROUP BY PHONE) AS b ON b.PHONE = a.PHONE AND CONVERT(int, SUBSTRING(b.concatStr, 10, 12)) = a.ID
ORDER BY a.Position

Cause I store dates as varchar already.

Thanks a lot dude

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-09-10 : 19:12:16

quote:
Originally posted by duplicity

Cause I store dates as varchar already.

Gah! Why do people do this?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-10 : 20:43:12

>>Gah! Why do people do this?

Most newbies use varchar to store dates to store them in the format they want which will cause problems when retreiving data

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources