query help needed - 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)

query help needed

Author

Topic

ronin2307
Posting Yak Master

126 Posts

Posted - 2006-10-30 : 10:18:47

I'm sure this is a no-brainer but because of the lack of experience I'll ask anyways:

imagine a table that looks like this:

COL_A COL_B
1 A
2 A
1 B
2 B
1 C
2 C

I would like the query all DISTINCT col_b entries and their respective MAX col_a entries so that my query returns this

2 A
2 B
2 C

what does the query look like for this?
Thanx

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-30 : 10:22:01

[code]select max(COL_A), COL_B
from table
group by COL_B
[/code]

ronin2307
Posting Yak Master

126 Posts

Posted - 2006-10-30 : 10:27:57

yeah I thought so, but unfortunately I oversimplified the above example for what I need. I'll try to work on it a little more before posting the full query. thanx anyway

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-30 : 10:30:31

Sure. Please post the table structure, some sample data and the expected result. And also try to explain what are you trying to achieve. It will help to speed things up.

ronin2307
Posting Yak Master

126 Posts

Posted - 2006-10-30 : 10:34:20

[code]SELECT hwk_taskdates.fCycleCount,hwk_taskdates.fTotalHrs,hwk_taskdates.fDate,
hwk_machinedata.fCycleCount,hwk_machinedata.fTotalHrs,
hwk_Tasks.fCycle,hwk_Tasks.fRunTime,hwk_Tasks.fDayCount,
hwk_taskdates.fGUID
FROM hwk_taskdates
left outer join hwk_machinedata on
hwk_machinedata.fMachineID = hwk_taskdates.fMachineID
left outer join hwk_tasks on
hwk_tasks.fGUID = hwk_taskdates.ftaskid
WHERE (cast(hwk_taskdates.fDate as varchar(50)) like dbo.fx_GetLastCompletedDate(dbo.fx_GetTaskGUID('%%')))
AND (hwk_taskdates.fMachineID = dbo.fx_GetMachineGUID('VROC'))[/code]

the result is:
[code]
0 .00 2006-06-30 00:00:00.000 10000 4300.00 NULL NULL 90.00 764C3C4C-C4A0-4610-9489-296BE18C6D03
10000 4300.00 2006-10-27 16:49:26.900 10000 4300.00 500.00 NULL NULL EF025E99-218B-4E0E-89D9-EC7872B33B31
0 .00 2006-10-01 00:00:00.000 10000 4300.00 NULL NULL 1.00 5B7279A9-3950-45C8-821A-416606E96B04
9900 .00 2006-10-27 00:00:00.000 10000 4300.00 500.00 NULL NULL EF025E99-218B-4E0E-89D9-EC7872B33B31
[/code]

as you can see in the GUID col row 2 and 4 have the same GUID. what I want is only the entries for the latest date for each distinct GUID to be returned.

I hope it is somewhat clear what I need
Thanx

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-30 : 10:41:54

"I hope it is somewhat clear what I need"
Yes. Sorry but i don't quite understand the logic behind your query.

Anyway this is another method you can use instead of the

select max(COL_A), COL_B from table group by COL_B

declare @table table
(
	COL_A	int, 
	COL_B	char(1)
)
insert into @table
select	1, 'A'	union all
select	2, 'A'	union all
select	1, 'B'	union all
select	2, 'B'	union all
select	1, 'C'	union all
select	2, 'C'

select	*
from	@table t
where	t.COL_A = (select max(COL_A) from @table x where x.COL_B = t.COL_B)

/*
COL_A       COL_B 
----------- ----- 
2           C
2           B
2           A

(3 row(s) affected)
*/

ronin2307
Posting Yak Master

126 Posts

Posted - 2006-10-30 : 10:48:54

the logic is somewhat complicated but I was hoping that it is still doable:
basically:
If given an existing description of a task, the query would pull the LATEST entries for that task from the taskdates table (along with some other stuff from other tables)
If not then it would pick out the rows for each distinctive task in the taskdates table where fdate is the latest date

hope that makes sense

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources