SQL select command

70 pts.
Tags:
SQL
SQL commands
SQL Select
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 ...

Answer Wiki

Thanks. We'll let you know when a new response is added.

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>

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    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.
    66,190 pointsBadges:
    report
  • Abrahm75
    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 ...
    70 pointsBadges:
    report
  • Abrahm75
    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
    70 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following