Grouping Records - 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)

Grouping Records

Author

Topic

chippyles
Yak Posting Veteran

68 Posts

Posted - 2006-07-07 : 10:02:40

I would like to group a table by the first occurrance of an object. In Ms Access, I could simply select First, but in SQL I can not. How can I do this when I group records?

Thanks!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-07 : 10:04:40

Select Top 1 columns from yourTable Order by yourCol DESC --or ASC

or

post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

chippyles
Yak Posting Veteran

68 Posts

Posted - 2006-07-07 : 10:36:28

TOP 1 gives me one record. I need to group records,


DATE_CREATED	TIME_CREATED	PRG	SID1	SID2	SID3		CU
7/7/2006	3:35:19		5/32-LW	56-1	DS	11240805	0.09
7/5/2006	16:23:47	3/32-LW	55-1	TO	11239330	0.133
7/1/2006	12:12:10	5/64-LW	54-1	ni	11233977	0.09
7/1/2006	5:54:35         5/64-LW	54-1	CZ	11233977	0.08
6/30/2006	20:18:20	1/8-LW	53-1	QK	11236106	0.058
6/30/2006	11:19:34	1/8-LW	53-5	pi	11231873	0.074
6/29/2006	22:53:58	1/8-LW	53-1	CZ	11231873	0.054
6/29/2006	3:02:27		5/32-LW	56-1	ZN	11235215	0.066
6/28/2006	23:25:49	3/32-LW	54-1	CZ	11236505	0.068
6/28/2006	13:57:23	3/32-LW	55-6	DS	150820		0.06

It should read this...


DATE_CREATED	TIME_CREATED	PRG	SID1	SID2	SID3		CU
7/7/2006	3:35:19		5/32-LW	56-1	DS	11240805	0.09
7/5/2006	16:23:47	3/32-LW	55-1	TO	11239330	0.133
7/1/2006	5:54:35         5/64-LW	54-1	CZ	11233977	0.08
6/30/2006	20:18:20	1/8-LW	53-1	QK	11236106	0.058
6/29/2006	22:53:58	1/8-LW	53-1	CZ	11231873	0.054
6/29/2006	3:02:27		5/32-LW	56-1	ZN	11235215	0.066
6/28/2006	23:25:49	3/32-LW	54-1	CZ	11236505	0.068
6/28/2006	13:57:23	3/32-LW	55-6	DS	150820		0.06

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-07 : 11:24:03

What datatype are DATE_CREATED and TIME_CREATED?
If they are datetimes then
select *
from tbl t1
where not exists (select * from tbl t2 where t1.PRG = t2.PRG and t1.SID1 = t2.SID1 and t1.SID2 = t2.SID2 and t1.SID3 = t2.SID3 and t2.DATE_CREATED + t2.TIME_CREATED < t1.DATE_CREATED + t1.TIME_CREATED)

if they are not datetimes then you will have to convert them.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-07 : 11:41:23

I doubt ur expected results are following any logic. (mainly last row and 3rd from last)

But here is what I have, assuming u want to group by Max Date-Times.
(Note that I purposely ommited ur last 4 columns as it takes time for me to key in all these.)


Create Table #t (DATE_CREATED datetime,	TIME_CREATED datetime, PRG varchar(30))

Insert Into #t values('7/7/2006','3:35:19','5/32-LW')	
Insert Into #t values('7/5/2006','16:23:47','3/32-LW')
Insert Into #t values('7/1/2006','12:12:10','5/64-LW')	
Insert Into #t values('7/1/2006','5:54:35','5/64-LW')	
Insert Into #t values('6/30/2006','20:18:20','1/8-LW')	
Insert Into #t values('6/30/2006','11:19:34','1/8-LW')	
Insert Into #t values('6/29/2006','22:53:58','1/8-LW')	
Insert Into #t values('6/29/2006','3:02:27','5/32-LW')	
Insert Into #t values('6/28/2006','23:25:49','3/32-LW')	
Insert Into #t values('6/28/2006','13:57:23','3/32-LW')	


Select * from #t where DATE_CREATED + TIME_CREATED in
(Select Max(DATE_CREATED + TIME_CREATED) from #t Group By DATE_CREATED)

Srinika

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-07 : 12:02:46

I think it's the latest row for each
PRG SID1 SID2 SID3
group.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-07 : 12:28:02

quote:
Originally posted by nr

I think it's the latest row for each
PRG SID1 SID2 SID3
group.

If that is the case
2 of the following should be in 2 seperate rows in the results :


7/1/2006 12:12:10 5/64-LW 54-1	ni 11233977 0.09
7/1/2006 5:54:35  5/64-LW 54-1	CZ 11233977 0.08

Also, if my assumption is considered (ie only max(date + time) ) group
should not get


6/29/2006 3:02:27  5/32-LW 56-1 ZN 11235215 0.066
6/28/2006 13:57:23 3/32-LW 55-6	DS 150820   0.06

Srinika

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-07 : 12:59:01

[code]
You're right.
just PRG SID1 would mean you shouldn't get
6/30/2006 20:18:20 1/8-LW 53-1 QK 11236106 0.058
6/29/2006 22:53:58 1/8-LW 53-1 CZ 11231873 0.054

maybe it's
just PRG SID3?
or PRG per day?

Anyway - my query can be altered easily (but not efficiently) to cope with any of those.

[/code]

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-07-07 : 14:04:03

If you are running SQL 2005, there is a very easy way to do this ......

- Jeff

chippyles
Yak Posting Veteran

68 Posts

Posted - 2006-07-07 : 14:11:35

sorry SQL 2000 here

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-10 : 03:07:05

See if point 2 helps you
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan

Failing to plan is Planning to fail

chippyles
Yak Posting Veteran

68 Posts

Posted - 2006-07-10 : 09:17:52

[code]
DATE_CREATED TIME_CREATED PRG SID1 SID2 SID3 CU
7/7/2006 3:35:19 5/32-LW 56-1 DS 11240805 0.09
7/5/2006 16:23:47 3/32-LW 55-1 TO 11239330 0.133
7/1/2006 12:12:10 5/64-LW 54-1 ni 11233977 0.09
7/1/2006 5:54:35 5/64-LW 54-1 CZ 11233977 0.08
6/30/2006 20:18:20 1/8-LW 53-1 QK 11236106 0.058
6/30/2006 11:19:34 1/8-LW 53-5 pi 11231873 0.074
6/29/2006 22:53:58 1/8-LW 53-1 CZ 11231873 0.054
6/29/2006 3:02:27 5/32-LW 56-1 ZN 11235215 0.066
6/28/2006 23:25:49 3/32-LW 54-1 CZ 11236505 0.068
6/28/2006 13:57:23 3/32-LW 55-6 DS 150820 0.06

It should read this...

DATE_CREATED TIME_CREATED PRG SID1 SID2 SID3 CU
7/7/2006 3:35:19 5/32-LW 56-1 DS 11240805 0.09
7/5/2006 16:23:47 3/32-LW 55-1 TO 11239330 0.133
7/1/2006 5:54:35 5/64-LW 54-1 CZ 11233977 0.08
6/30/2006 20:18:20 1/8-LW 53-1 QK 11236106 0.058
6/29/2006 22:53:58 1/8-LW 53-1 CZ 11231873 0.054
6/29/2006 3:02:27 5/32-LW 56-1 ZN 11235215 0.066
6/28/2006 23:25:49 3/32-LW 54-1 CZ 11236505 0.068
6/28/2006 13:57:23 3/32-LW 55-6 DS 150820 0.06
[/code]

Previously, I posted the above table and the expected results below. I guess I am not understanding what everyone is posting. I need to group every record by SID3. This number is what is important to me. If there is a duplicate, I need the earliest record. Or in Access this would be the FIRST record for SID3. Can someone please assist me figuring this out?

Thanks!!

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-10 : 09:32:14

See my first post

select *
from tbl t1
where not exists (select * from tbl t2 where t1.SID3 = t2.SID3 and t2.DATE_CREATED + t2.TIME_CREATED < t1.DATE_CREATED + t1.TIME_CREATED)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

chippyles
Yak Posting Veteran

68 Posts

Posted - 2006-07-10 : 09:46:56

NR --

That worked perfectly. I guess late in the week I lost focus.

Thanks!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources