SQL select command
Hi i hope all doing well,
i have a problem with this SQL select command :
****************************************************************
SELECT DISTINCT customers.first_name, customers.last_name, customers.email, customers.cellPhone, customers.phone, items.title, items.price, auction_items.auction_item_nr, auction_items.reserv_price, biddings.price
FROM customers, biddings, auction_items, items, auctions
WHERE customers.customer_nr = biddings.customer_nr
AND auction_items.auction_item_nr = biddings.auction_item_nr
AND items.item_nr = auction_items.item_nr
AND auction_items.auction_nr = auctions.auction_nr
AND auctions.auction_nr =7
ORDER BY auction_items.item_nr, biddings.price DESC
****************************************************************
the problem is that using this command am getting the result below ...
auction_item_nr reserv_price, Bprice
50 450 400
50 450 300
50 450 200
50 450 100
51 300 300
52 8000 299
52 8000 199
52 8000 99
48 1300 500
48 1300 400
48 1300 300
49 800 500
what i need is to make the same list but without the redundancy that means for every (auction_item_nr) the bigest price and others i don't need it, i trid to use DISTINCT to remove the others.
for ex. if you check the table you will see that their is many records been listed many times like the (auction_item_nr) number 50 four times.
auction_item_nr reserv_price, Bprice
50 450 400
50 450 300
50 450 200
50 450 100
so please if someone can help me to get the result like this :
auction_item_nr reserv_price, Bprice
50 450 400
51 300 300
52 8000 299
48 1300 500
49 800 500
the sql select shoud gets all the information for that auction_item_nr with biggest price only.
thanks in advance ...
Software/Hardware used:
Software/Hardware used:
ASKED:
July 31, 2009 12:51 PM
UPDATED:
August 3, 2009 6:56 AM
Answer Wiki:
Using DISTINCT didn't work, because it filters out records in which all fields are duplicated.
You need to use a GROUP BY statement.
I don't see a column "Bprice" in your query, so I will assume that it is "biddings.price"
<pre>SELECT customers.first_name, customers.last_name, customers.email, customers.cellPhone, customers.phone,
items.title, items.price, auction_items.auction_item_nr, auction_items.reserv_price, MAX (biddings.price)
FROM customers, biddings, auction_items, items, auctions
WHERE customers.customer_nr = biddings.customer_nr
AND auction_items.auction_item_nr = biddings.auction_item_nr
AND items.item_nr = auction_items.item_nr
AND auction_items.auction_nr = auctions.auction_nr
AND auctions.auction_nr =7
GROUP BY customers.first_name, customers.last_name, customers.email, customers.cellPhone, customers.phone,
items.title, items.price, auction_items.auction_item_nr, auction_items.reserv_price
ORDER BY auction_items.item_nr</pre>
Depending on the database, you could consider using the JOIN syntax for better readability, and also consider using some aliases for the table names.
Something like this:
<pre>SELECT c.first_name, c.last_name, c.email, c.cellPhone, c.phone, i.title,
i.price, ai.auction_item_nr, ai.reserv_price, MAX (b.price)
FROM customers c JOIN biddings b
ON c.customer_nr = b.customer_nr
JOIN auction_items ai
ON b.auction_item_nr = ai.auction_item_nr
JOIN items i
ON ai.item_nr = i.item_nr
JOIN auctions a
ON ai.auction_nr = a.auction_nr
WHERE a.auction_nr =7
GROUP BY c.first_name, c.last_name, c.email, c.cellPhone, c.phone, i.title,
i.price, ai.auction_item_nr, ai.reserv_price
ORDER BY ai.item_nr</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
You will want to switch away from the older JOIN syntax as it can cause incorrect data to be returned in some cases. Also support for that syntax will be dropped at some point in the future.
Hi again,
i tried to use Group By, its look like better but am still getting duplication on (auction_items.auction_item_nr), as am not getting the max(biddings.price):
result is:
auction_items.auction_item_nr, auction_items.reserv_price, MAX (biddings.price)
50 450 400
50 450 300
51 300 300
52 8000 299
52 8000 199
48 1300 500
48 1300 400
49 800 500
as you can notice that the (auction_items.auction_item_nr) been duplicated two times for (50, 52, also 48), i don’t know what is the problem but it must gets like below …
auction_items.auction_item_nr, auction_items.reserv_price, MAX (biddings.price)
50 450 400
51 300 300
52 8000 299
48 1300 500
49 800 500
so any suggestion !!!
thank you for advance …
Hi All again,
thank you Carlosdl, thank you all, its working now perfectly …
the thing is i just made the Group By according to the (auction_items.auction_item_nr) only … as below:
SELECT customers.first_name, customers.last_name, customers.email, customers.cellPhone, customers.phone,
items.title, items.price, auction_items.auction_item_nr, auction_items.reserv_price, MAX (biddings.price)
FROM customers, biddings, auction_items, items, auctions
WHERE customers.customer_nr = biddings.customer_nr
AND auction_items.auction_item_nr = biddings.auction_item_nr
AND items.item_nr = auction_items.item_nr
AND auction_items.auction_nr = auctions.auction_nr
AND auctions.auction_nr =7
GROUP BY auction_items.auction_item_nr
ORDER BY auction_items.item_nr