Select lowest two prices from item list grouped by item id

[kdmrr]

Hi. New to sql

I have an items table and I want to select the lowest two priced rows per item id.

i.e.

item        vendor        price
12            xyz         1
12            aaa         2
12            zzz         1.5
 
13            xyz         3
13            aaa         1.5
13            zzz         1
 
Would return: 
 
item        vendor        price
12            xyz         1
12            zzz         1.5
 
13            aaa         1.5
13            zzz         1

Also, how do I get the spaces between the items to show up as I type them? The table I type is easy to read, the one shown doesn't have the same spacing between fields.

Thanks in advance!

[jforbes]

The TOP Keyword can limit the results to two.
ORDER BY will sort the list before TOP is applied.

SELECT TOP 2 item, vendor, price
FROM tblPrice
ORDER BY price DESC

You can use the [CODE/] Button to Format Text on the page.

[mbizup]

What you're describing (lowest two PER ID) is a Top N Per Group query. Subqueries are one way to accomplish this. Take a look at Allen Browne's post here, under the heading "Top N Records Per Group":
Subquery Basics

You'll need the DESC Keyword to get the lowest.

{Edit}Relevant excerpt from Allen's article.
TOP n records per group

You want the three most recent orders for each client. Use a subquery to select the 3 top orders per client, and use it to limit which orders are selected in the main query:

SELECT   Orders.CustomerID, Orders.OrderDate, Orders.OrderID
FROM     Orders
WHERE    Orders.OrderID IN
   (SELECT TOP 3 OrderID                            
    FROM     Orders AS Dupe                              
    WHERE    Dupe.CustomerID = Orders.CustomerID        
    ORDER BY Dupe.OrderDate DESC
           , Dupe.OrderID DESC) 
ORDER BY Orders.CustomerID
       , Orders.OrderDate
       , Orders.OrderID;

Points to note:

• Since we have two copies of the same table, we need the alias.
• Like EXISTS in the first example above, there is no problem with the subquery returning multiple records. The main query does not have to show any value from the subquery.
• Adding the primary key field to the ORDER BY clause differentiates between tied values.

{/Edit}

[kdmrr]

Thanks, I'll try this tonight.

[kdmrr]

Thanks!! It worked perfectly

[NeoPa]

I just added the relevant part of that article to Miriam's post as it's been selected as Best Answer. Where possible, they should have the answer plainly visible so searchers can get a very quick hit when they land on a Bytes.com page.