min() with extra field on side

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 2008 Forums

Transact-SQL (2008)

min() with extra field on side

Author

Topic

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2011-02-07 : 06:02:55

i got 1 table with these fields
id: just autoincrement
regelid: a groupid
disciplineid: id that refers to specific interest
order: order of importance

i need to get all disciplineids where the order is the lowest
grouped by that groupid/regelid.

SELECT regelid,MIN(order)
from table

group by regelid

this is the closest i get.... but i only need the disciplineids....
the rest of the fields may stay there but i need the ...

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-07 : 06:05:49

SELECT disciplineid,MIN(order)
from table
group by disciplineid

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2011-02-07 : 06:11:30

got it !

select VD.regelid,VD.disciplineid

from tblvolgordedisciplines VD

where VD.volgorde in (

SELECT MIN( SUB.volgorde ) AS volgorde

FROM tblvolgordedisciplines SUB

WHERE SUB.regelid = VD.regelid
)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-07 : 23:14:05

you might want to also check out the row_number() window function in the Books On Line.

KH
[spoiler]Time is always against us[/spoiler]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-02-08 : 06:08:40

like this




SELECT
	results.[regelid]
	, results.[disciplineid]
FROM
	(
	SELECT
		v.[regelid] AS [regelid]
		, v.[disciplineid] AS [disciplineID]
		, ROW_NUMBER () OVER (
			PARTITION BY v.[groupid], v.[regelid]
			ORDER BY v.[order] ASC
			)
			AS [rowPos]
	FROM
		tblvolgordedisciplines AS v
	)
	AS results
WHERE
	results.[rowPos] = 1

-- FYI, it's generally considered bad form to have columns named after SQL KEYWORDS ([order]). It gets confusing.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources