SELECT  t.* 
FROM    sometable t
        INNER JOIN
        (
                SELECT  item_id, rem_qty = MIN(rem_qty)
                FROM    sometable
                GROUP BY item_id
        ) m     ON      t.item_id       = m.item_id
                AND     t.rem_qty       = m.rem_qty

SELECT	st.*
FROM	SomeTable AS st
WHERE	EXISTS (SELECT * FROM SomTable AS x GROUP BY x.Item_ID HAVING x.ItemID = st.Item_ID AND MIN(x.Rem_Qty) = st.Rem_Qty)

CREATE TABLE	#Sample
		(
			i INT,
			j INT
		)

--CREATE CLUSTERED INDEX IX_Sample ON #Sample (i, j)

INSERT	#Sample
SELECT	1, 1 UNION ALL
SELECT	1, 2 UNION ALL
SELECT	2, 9 UNION ALL
SELECT	2, 8 UNION ALL
SELECT	3, 0

-- Peso 1
SELECT	s.*
FROM	#Sample AS s
WHERE	EXISTS (SELECT * FROM #Sample AS w GROUP BY w.i HAVING w.i = s.i  AND MIN(w.j) = s.j)

-- Peso 2
SELECT	s.*
FROM	#Sample AS s
WHERE	EXISTS (SELECT * FROM #Sample AS w WHERE w.i = s.i GROUP BY w.i HAVING MIN(w.j) = s.j)

-- Khtan 1
SELECT		s.* 
FROM		#Sample AS s
INNER JOIN	(
			SELECT		i,
					MIN(j) AS j
			FROM		#Sample
			GROUP BY	i
		) AS w ON w.i = s.i
			AND w.j = s.j

-- Khtan 2
SELECT		s.* 
FROM		#Sample AS s
INNER JOIN	(
			SELECT		i,
					MIN(j) AS j
			FROM		#Sample
			GROUP BY	i
		) AS w ON w.i = s.i
WHERE		w.j = s.j

DROP TABLE	#Sample

quote:

---

Originally posted by Peso

Use EXISTS.

Try this

SELECT	st.*
FROM	SomeTable AS st
WHERE	EXISTS (SELECT * FROM SomTable AS x GROUP BY x.Item_ID HAVING x.ItemID = st.Item_ID AND MIN(x.Rem_Qty) = st.Rem_Qty)

It is not as efficient as the other suggestion, but maybe you are more familiar with the syntax.

---

E 12°55'05.25"
N 56°04'39.16"

---

INSERT		#Sample
SELECT		v1.Number,
		v2.Number
FROM		master..spt_values AS v1
INNER JOIN	master..spt_values AS v2 ON v2.Type = 'p'
WHERE		v1.Type = 'p'
		AND v1.Number BETWEEN 0 AND 99
		AND v2.Number BETWEEN 0 AND 99

INSERT		#Sample
SELECT		v1.Number,
		v2.Number
FROM		master..spt_values AS v1
INNER JOIN	master..spt_values AS v2 ON v2.Type = 'p'
WHERE		v1.Type = 'p'
		AND v1.Number BETWEEN 0 AND 999
		AND v2.Number BETWEEN 0 AND 999

quote:

---

Originally posted by mr_dayal

r innerjoin and selfjoin the same

Mr Dayal

---

quote:

---

Originally posted by mr_dayal

souupse I have a table billreturn having following data

bill_no item_id rem_qty
0608/1 JC/SER/SAL/KN/CHUR0001 1
0608/1 JC/SER/SAL/KM/PATI0001 1
0608/1 JC/HL/KUR/KN/FORM0001 1
0608/2 JC/HC/WH/KN/PICT0002 10
0608/6 JC/HC/WH/KN/PICT0001 10
0608/6 JC/HC/WH/KN/PICT0002 2
0608/6 JC/HC/WH/KN/PICT0001 9
0608/6 JC/HC/WH/KN/PICT0001 4
0608/6 JC/HC/WH/KN/PICT0001 0
0608/7 JC/HC/WH/KN/PICT0001 10
0608/7 JC/HC/WH/KN/PICT0001 9
0608/7 JC/HC/WH/KN/PICT0001 8
0608/7 JC/HC/WH/KN/PICT0001 7
0608/7 JC/HC/WH/KN/PICT0001 6
0608/7 JC/HC/WH/KN/PICT0001 5
0608/10 JC/HC/CLO/KM/CLOC0002 1
0608/11 JC/HC/CLO/KM/CLOC0001 1
0608/12 JC/HC/WH/KN/PICT0002 10
0608/12 JC/HL/KUR/KN/FORM0001 2
0608/12 JC/SER/SAL/KN/CHUR0001 1



Now I want to retrive all the records group by bill_no and items having minimum rem_qty for a particular Bill_no.



Mr Dayal

---

SELECT t.bill_no,t.item_id,t.rem_qty
FROM YourTable t
INNER JOIN (SELECT bill_no,MIN(rem_qty) AS minqty
            FROM YourTable
            GROUP BY bill_no) t1
ON t1.bill_no=t.bill_no
AND t1.minqty=t.rem_qty

quote:

---

Originally posted by Peso

Use EXISTS.

Try this

SELECT	st.*
FROM	SomeTable AS st
WHERE	EXISTS (SELECT * FROM SomTable AS x GROUP BY x.Item_ID HAVING x.ItemID = st.Item_ID AND MIN(x.Rem_Qty) = st.Rem_Qty)

It is not as efficient as the other suggestion, but maybe you are more familiar with the syntax.

---

E 12°55'05.25"
N 56°04'39.16"

---

quote:

---

Originally posted by mr_dayal

is there any able like DUAL used in Oracle to test arithmetic functions?


Mr Dayal

---