Select Serial Number in a SQL query

I was recently asked this question about listing records from a database and even though this is unrelated with most of this blog, I thought it has earned its place as part of this blog.

The question was:

How do I select records from a database table and display the results as a list in the UI (which uses a SQL query to access the DB)

Consider a simple 'Grocery Shopping' database from which we want to display information in the UI/web page based on selected Shopper as follows:

Shopper1 bought

1. Eggs -$3

2. Bread - $2.5

3. Cream -$6

OR

Shopper2 bought

1. Cheese -$5

2. Eggs -$3

3. Ham -$9

4. Bread -$2.5

We have two tables:

To create the above result, using either Oracle or SQL server, you can use the following query:

SELECT
l.Shopper_name,
row_number() over (order by i.item#) as srno,
i.item_name,
i.price
FROM
Shopping_list as l, Inventory as i
WHERE l.shopper_name :=selected_shopper --Prompted value through SQL
GROUP BY l.shopper_name,i.item_name,i.price
ORDER BY l.shopper_name




After getting the results as a list, you can use your code to format it appropriately in the UI.



Another use for this type of query could be to see the number of times a given item appeared in the 'Shopping_List' table Eg: List the Shoppers who bought Eggs with Serial Numbers attached.



Anisha