473,721 Members | 2,002 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Need help with SQL statement!

I am having difficulty writing a SQL statement and I was wondering if one of
you gurus could help me. I have a table that has three fields: InvoiceID,
Item, ItemPrice. Each InvoiceID is associated with one or more Items. I am
trying to write a SQL statement that returns the record (all three fields)
for the least expensive item for each invoice (so the total number of
records returned will be equal to the total number of unique Invoice IDs.
How would I write this SQL statement?

Thank you,

Alex
Jul 20 '05 #1
7 1970
D
I would like to humbly state that, if I understand you correctly that you
want the cheapest item of every order, this is no simple matter in a Totals
Query. I had a similar need once where I wanted the cheapest (Min) of a
product price but I also wanted all the details of that price (Order #,
Date, Vendor, etc...). The problem you will have is with the Item field, by
introducing this third field you will wind up getting all the items. I do
not claim to be an expert but this is the problem that I had. I therefore
decided to use a PivotChart instead and found it to be very nice and neat.
I hope this helps and maybe there is someone out there that knows a better
way.

Dave

"Nic" <nicpayre[junk]@sympatico.ca> wrote in message
news:qo******** ***********@new s20.bellglobal. com...

"Alex" <sh****@yahoo.c om> wrote in message
news:_5******** *************@t wister.austin.r r.com...
I am having difficulty writing a SQL statement and I was wondering if one
of
you gurus could help me. I have a table that has three fields:

InvoiceID, Item, ItemPrice. Each InvoiceID is associated with one or more Items. I am trying to write a SQL statement that returns the record (all three fields) for the least expensive item for each invoice (so the total number of
records returned will be equal to the total number of unique Invoice IDs. How would I write this SQL statement?

What you need is a group function...i let you guess wich one it is.


Thank you,

Alex


Jul 20 '05 #2
"Alex" <sh****@yahoo.c om> wrote in message
news:_5******** *************@t wister.austin.r r.com...
I am having difficulty writing a SQL statement and I was wondering if one of you gurus could help me. I have a table that has three fields: InvoiceID,
Item, ItemPrice. Each InvoiceID is associated with one or more Items. I am
trying to write a SQL statement that returns the record (all three fields)
for the least expensive item for each invoice (so the total number of
records returned will be equal to the total number of unique Invoice IDs.
How would I write this SQL statement?


SELECT tblInvoiceA.Inv oiceID, tblInvoiceA.Ite m, tblInvoiceA.Ite mPrice
FROM tblInvoice AS tblInvoiceA
WHERE (((tblInvoiceA. ItemPrice)
IN
(SELECT MIN(ItemPrice)
FROM tblInvoice
WHERE InvoiceID = tblInvoiceA.Inv oiceID)));

or an = would do just as well as the IN.

Problem - what if you have 2 items on the same invoice with the same price,
which is also the lowest price on that invoice? Which do you want to choose?
You could try this:

SELECT tblInvoiceA.Inv oiceID, First(tblInvoic eA.Item) AS FirstOfItem,
tblInvoiceA.Ite mPrice
FROM tblInvoice AS tblInvoiceA
GROUP BY tblInvoiceA.Inv oiceID, tblInvoiceA.Ite mPrice
HAVING (((tblInvoiceA. ItemPrice)
IN
(SELECT MIN(ItemPrice)
FROM tblInvoice
WHERE InvoiceID = tblInvoiceA.Inv oiceID)));

Which will give you one 'lowest price' item.

But your table design is wrong. You want one table tblInvoices, in a one to
many relationship with tblInvoiceItems . Or something. The way you've got it
at the moment I can't see what your Primary Key is. InvoiceID + Item? What
if they buy 2 of them on the same invoice?

Yours, Mike MacSween

Jul 20 '05 #3
"Alex" <sh****@yahoo.c om> wrote in message news:_5******** *************@t wister.austin.r r.com...
I am having difficulty writing a SQL statement and I was wondering if one of
you gurus could help me. I have a table that has three fields: InvoiceID,
Item, ItemPrice. Each InvoiceID is associated with one or more Items. I am
trying to write a SQL statement that returns the record (all three fields)
for the least expensive item for each invoice (so the total number of
records returned will be equal to the total number of unique Invoice IDs.
How would I write this SQL statement?

Thank you,

Alex


CREATE TABLE Invoices
(
invoice_id INT NOT NULL,
item INT NOT NULL,
item_price DECIMAL(6, 2) NOT NULL,
PRIMARY KEY (invoice_id, item)
)

-- Sample data
INSERT INTO Invoices (invoice_id, item, item_price)
VALUES (1, 1, 10)
INSERT INTO Invoices (invoice_id, item, item_price)
VALUES (1, 2, 15.50)
INSERT INTO Invoices (invoice_id, item, item_price)
VALUES (1, 3, 9.99)
INSERT INTO Invoices (invoice_id, item, item_price)
VALUES (2, 1, 99.99)
INSERT INTO Invoices (invoice_id, item, item_price)
VALUES (2, 2, 149.99)

SELECT I1.invoice_id, I1.item, I1.item_price
FROM Invoices AS I1
LEFT OUTER JOIN
Invoices AS I2
ON I1.invoice_id = I2.invoice_id AND
I2.item_price < I1.item_price
WHERE I2.item_price IS NULL
ORDER BY I1.invoice_id

invoice_id item item_price
1 3 9.99
2 1 99.99

Regards,
jag
Jul 20 '05 #4
"Alex" <sh****@yahoo.c om> wrote in message news:<_5******* **************@ twister.austin. rr.com>...
I am having difficulty writing a SQL statement and I was wondering if one of
you gurus could help me. I have a table that has three fields: InvoiceID,
Item, ItemPrice. Each InvoiceID is associated with one or more Items. I am
trying to write a SQL statement that returns the record (all three fields)
for the least expensive item for each invoice (so the total number of
records returned will be equal to the total number of unique Invoice IDs.
How would I write this SQL statement?

Thank you,

Alex

Hi,
I think you should try with Group by clauseand MIN function. it might help you.

Thanks
Hoque
Jul 20 '05 #5
"Alex" <sh****@yahoo.c om> wrote in message news:<_5******* **************@ twister.austin. rr.com>...
I am having difficulty writing a SQL statement and I was wondering if one of
you gurus could help me. I have a table that has three fields: InvoiceID,
Item, ItemPrice. Each InvoiceID is associated with one or more Items. I am
trying to write a SQL statement that returns the record (all three fields)
for the least expensive item for each invoice (so the total number of
records returned will be equal to the total number of unique Invoice IDs.
How would I write this SQL statement?

Thank you,

Alex


It's not clear what the best way to do this is, without knowing what
your table structure is, what your keys are etc. For example, what
happens if one invoice has two items with the same 'minimum' price -
which item should appear on the output?

If one invoice has many items, you would usually normalize your design
to have an Invoices table and an InvoiceItems table, with a foreign
key linking them. You'd probably also need to link to some sort of
Items table which has all the items that could appear on an invoice.
This is a lot more efficient - invoice tables are a common task, so
you can probably find some information on the web about implementing
them.

Having said all that, here's one query that _might_ work, but it won't
be consistent and may be inefficient, depending on what indexes you
have on the table. The real solution is almost certainly to redesign
your tables, but without more background information it's not clear.

select
it.InvoiceID,
it.Item,
it.ItemPrice
from
InvoiceTable it
join ( select InvoiceID, min(ItemPrice) as ItemPrice
from InvoiceTable
group by InvoiceID ) dt
on it.InvoiceID = dt.InvoiceID
and it.ItemPrice = dt.ItemPrice
order by
it.InvoiceID

Simon
Jul 20 '05 #6
Thank you all for your help, particularly Mike MacSween's.

"Alex" <sh****@yahoo.c om> wrote in message
news:_5******** *************@t wister.austin.r r.com...
I am having difficulty writing a SQL statement and I was wondering if one of you gurus could help me. I have a table that has three fields: InvoiceID,
Item, ItemPrice. Each InvoiceID is associated with one or more Items. I am
trying to write a SQL statement that returns the record (all three fields)
for the least expensive item for each invoice (so the total number of
records returned will be equal to the total number of unique Invoice IDs.
How would I write this SQL statement?

Thank you,

Alex

Jul 20 '05 #7
Mike,

Thank you so much for your reply. Your solution is exactly what I was
looking for. You definitely know your SQL.

Alex

"Mike MacSween" <mi************ ***********@bti nternet.com> wrote in message
news:3f******** *************** @pubnews.gradwe ll.net...
"Alex" <sh****@yahoo.c om> wrote in message
news:_5******** *************@t wister.austin.r r.com...
I am having difficulty writing a SQL statement and I was wondering if one
of
you gurus could help me. I have a table that has three fields:
InvoiceID, Item, ItemPrice. Each InvoiceID is associated with one or more Items. I am trying to write a SQL statement that returns the record (all three fields) for the least expensive item for each invoice (so the total number of
records returned will be equal to the total number of unique Invoice IDs. How would I write this SQL statement?


SELECT tblInvoiceA.Inv oiceID, tblInvoiceA.Ite m, tblInvoiceA.Ite mPrice
FROM tblInvoice AS tblInvoiceA
WHERE (((tblInvoiceA. ItemPrice)
IN
(SELECT MIN(ItemPrice)
FROM tblInvoice
WHERE InvoiceID = tblInvoiceA.Inv oiceID)));

or an = would do just as well as the IN.

Problem - what if you have 2 items on the same invoice with the same

price, which is also the lowest price on that invoice? Which do you want to choose? You could try this:

SELECT tblInvoiceA.Inv oiceID, First(tblInvoic eA.Item) AS FirstOfItem,
tblInvoiceA.Ite mPrice
FROM tblInvoice AS tblInvoiceA
GROUP BY tblInvoiceA.Inv oiceID, tblInvoiceA.Ite mPrice
HAVING (((tblInvoiceA. ItemPrice)
IN
(SELECT MIN(ItemPrice)
FROM tblInvoice
WHERE InvoiceID = tblInvoiceA.Inv oiceID)));

Which will give you one 'lowest price' item.

But your table design is wrong. You want one table tblInvoices, in a one to many relationship with tblInvoiceItems . Or something. The way you've got it at the moment I can't see what your Primary Key is. InvoiceID + Item? What
if they buy 2 of them on the same invoice?

Yours, Mike MacSween

Jul 20 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
521
by: Gary Stollman | last post by:
My Father is a Clone//ETs plotting to overthrow the human race! On August 19, 1987 a gun-carrying Gary Stollman entered the studio of Los Angeles's KNBC television, crashing consumer reporter David Horowitz's live newscast. Stollman handed Horowitz a written statement and ordered him to read it while holding a gun on him. Unbeknownst to Stollman, KNBC immediately switched to a commercial, not permitting the statement to be transmitted. ...
5
3015
by: Alex | last post by:
I am having difficulty writing a SQL statement and I was wondering if one of you gurus could help me. I have a table that has three fields: InvoiceID, Item, ItemPrice. Each InvoiceID is associated with one or more Items. I am trying to write a SQL statement that returns the record (all three fields) for the least expensive item for each invoice (so the total number of records returned will be equal to the total number of unique Invoice IDs....
1
2071
by: Jill | last post by:
I am trying to store the current date to an ms access database on my server. I set it up with a dsnless connection. Here is the statement: Insert Into employees(timestamp) Values ('" & date() & "')" the timestamp field is of type date/time in the ms access database. I get an error saying the insert statement is invalid. Cannot get you the exact error, my server is down. But it seems that the syntax is correct.
1
2152
by: Tom Rahav | last post by:
Hello all! I develop application in Visual Basic .NET and ORACLE database. My question is how do I "send" script file to the database using visual basic .net. Other words, is there any way to send to the database a script file to run, as done by: "@FullPath\FileName" in SQL PLUS ? If not, then I need your help with something else: I tried to send to the database the script file's content, in order to run the commands in it (create...
1
3177
by: JP | last post by:
I just installed db2 V8.1 and I am trying to create a simple compound statement in the command center script area and every time I would get the following error. what am i doing wrong? (note: my statement termination character is set to ";") i.e. of compound statement: begin atomic declare v_test SMALLINT; set v_test = 1;
7
2178
by: Buck Rogers | last post by:
Hi all! Newbie here. Below is an example from Teach Yourself C in 21 Days. My apologies if it is a bit long. What I don't understand is how the "get_data" function can call the "continue_function", and if NO is returned to "get_data", display_report executes and the program ends? Basically I am having trouble understanding the program flow within the "if" loop in the "main" function.
2
4719
by: clinttoris | last post by:
Hello, If someone could help me it would be appreciated as I am not having much luck. I'm struggling with my asp code and have some questions relating to asp and oracle database. First question. I have a web survey that I am working on and have successfully dynamically taken the info from a database, displayed it on the screen and then taken the users answers and inserted them into a
14
1582
by: Pep | last post by:
I have a method in a class like this class myClass { ... ctros, dtor, etc ... string myClassMethod() { string myString = "";
6
2983
by: zaina | last post by:
hi everybody i am nwebie in this forum but i think it is useful for me and the member are helpful my project is about connecting client with the server to start exchanging messages between them. to be more clear we process this purpose we serve this to the student in the university. how?? student will send a message that contains his name,id and request by format the server want such as zaina-20024008-grade. the grade is the request...
2
8148
by: kya2 | last post by:
I am not able to create following store procedure. CREATE PROCEDURE DBSAMBA.InsertDeleteBatch(OUT norows INT ) RESULT SETS 1 LANGUAGE SQL BEGIN part1 DECLARE TOTAL_LEFT INT DEFAULT 0; SELECT COUNT(*)INTO TOTAL_LEFT FROM DBSAMBA.REPORTEDTRANSACTION_S; WHILE (TOTAL_LEFT > 0) DO
0
8851
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9373
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9227
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9143
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
6676
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5992
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4497
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4761
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2588
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.