Select first row

[Marko]

Hello,

I'm a newbie in Access so if someone could help:



I have a table that is a result of an another query:

seg_name	Soll	Soll2
3DFL0402KS	4	MSG
3DFL0402KS	6	OR
3DFL0601KS	4	MSG
3DFL0601KS	6	AND
3DFL0602KS	4	MSG
3DFL0602KS	6	OR
3DFL0701KS	4	MSG
3DFL0701KS	6	AND
3DFL0702KS	4	MSG
3DFL0702KS	6	OR
3DFL0801KS	4	MSG
3DFL0801KS	6	OR
AND SO ON

As a result I need one row of the mentioned table for each seg_name, with the lowest number in the "Soll" field.


Thanks, Marko

[recyan]

what happens when you GROUP ON on seg_name and use MIN on Soll.

Thanks

[Marko]

Nothing happens...

SELECT [block-prioritet1].seg_name, Min([block-prioritet1].Soll) AS MinOfSoll, [block-prioritet1].Soll2
FROM [block-prioritet1]
GROUP BY [block-prioritet1].seg_name, [block-prioritet1].Soll2;

Result:

seg_name	MinOfSoll	Soll2
3DFL0101KS	4	MSG
3DFL0101KS	6	OR
3DFL0102KS	4	MSG
3DFL0102KS	6	OR
3DFL0401KS	4	MSG
3DFL0401KS	6	OR
3DFL0402KS	4	MSG
3DFL0402KS	6	OR
3DFL0601KS	6	AND
3DFL0601KS	4	MSG

[recyan]

SELECT [block-prioritet1].seg_name, Min([block-prioritet1].Soll) AS MinOfSoll 
 FROM [block-prioritet1]
GROUP BY [block-prioritet1].seg_name;

SELECT 
	[block-prioritet1].seg_name, 
	[block-prioritet1].Soll, 
	[block-prioritet1].Soll2
FROM 
	[block-prioritet1] 
	INNER JOIN 
	(
		SELECT 
			[block-prioritet1].seg_name, 
			Min([block-prioritet1].Soll) AS MinOfSoll
		FROM 
			[block-prioritet1]
		GROUP BY 
			[block-prioritet1].seg_name
	)
	AS Query1 
	ON 
	([block-prioritet1].seg_name = Query1.seg_name) 
	AND 
	([block-prioritet1].Soll = Query1.MinOfSoll);

Thanks

[Marko]

Thank you it was very helpful!