hi all i have a table inLi1 conta

[jawadfrhad]

hi all
i have a table inLi1 contain many records...
itemid name uprice
1 ja 9
2 fa 10
1 ko 9
1 ja 20
and i write SQL Statement (MIN):
SELECT inLi1.itemid,inLi1.name,inLi1.uprice as uprice1 FROM inLi1 INNER JOIN;
(select itemid,Min(uprice) as uprice FROM inLi1 GROUP BY itemid)ctmp ;
ON inLi1.itemid=ctmp.itemid AND inLi1.uprice=ctmp.uprice ORDER BY inLi1.itemid INTO CURSOR wv1

the result
itemid name uprice
1 ja 9
2 fa 10
1 ko 9

i need the result
itemid name uprice
1 ja 9
2 fa 10
(OR)
itemid name uprice
1 ko 9
2 fa 10
I need one record in case the name is different, the important thing is itemid
thank you

 

[Chris Miller]

If the name is irrelevant, then don't include it in the result, join it later.

A simple way to do it is using a unique index.

Create Cursor result (id int, name c(5), uprice int)
index on id tag id unique

Insert Into result;
[highlight #FCE94F]SELECT inLi1.itemid,inLi1.name,inLi1.uprice as uprice1 FROM inLi1 INNER JOIN;
(select itemid,Min(uprice) as uprice FROM inLi1 GROUP BY itemid)ctmp ;
ON inLi1.itemid=ctmp.itemid AND inLi1.uprice=ctmp.uprice ORDER BY inLi1.itemid[/highlight]

With the index set you will only see the first pair of id + name found, if the id is found with another name that's not removed, but suppresed.


Chriss

 

[jawadfrhad]

great idea
thank you