Hi Pinkesh,
> I've got item table as follwoing:
>
> itemno |item_name
> 1 item one
> 2 item two
> 3 item three
> 4 item four
> 2 item two
> 3 item three
>
>
> I want to right a query so I get three rows of each item I specify in WHERE
>
> for example
> SELECT itemname from item WHERE itemno IN (1,2,3);
>
> Results should return first three rows of each itemno
>
> How do I write such query?
Your question is slightly ambiguous, but I assume that you want a resultset containing
a maximum of three rows
WHERE itemno = 1, a maximum of three rows WHERE itemno = 2, and a maximum of three
rows WHERE itemno = 3. In
which case the answer is: I don't think you can. Unfortunately the (above) query
realises the entire sample tbl
excepting the single row itemno=4 and item_name="item four", and there are not more
than three rows which share
any particularly itemno value. (so in abstracting your real problem, you have left no
'hints' for us to be able
to offer more than a clinical observation!?)
RTFM 6.4.1 SELECT Syntax
[LIMIT [offset,] rows]
The LIMIT clause allows the control of output to be limited to a range of row numbers
out of the complete
resultset table. Thus you could limit to nine rows (for example), but there's no
guarantee that the resultset
would contain an equal number of 1s, 2s, and 3s (assuming that they are available in
the original table). At
this time, there is no facility to combine (say) the GROUP BY and LIMIT clauses -
which might be the answer you
seek.
Beyond that, what you appear to be asking is not part of the relational model/the
design of SQL. Your only other
approach would be to post-process the resultset using a scripting/programming
language/application.
Regards,
=dn
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
