expression the most, the least confusion

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

expression the most, the least confusion

Author

Topic

SGB19
Starting Member

10 Posts

Posted - 2007-03-05 : 12:06:44

I need to find the highest number sold in the non popular color of stove and should have this projection:

Employee Quantity Color
---------------------- -------- ------------
Fred Bailey 3 GRAY
(1 row(s) affected)

Here are my queries:
SELECT top 1 s.color, e.name, count(*)
FROM INV_LINE_ITEM inv, stove s, employee e, invoice i
where s.serialnumber = inv.fk_stovenbr and inv.fk_invoicenbr = invoicenbr and i.fk_empid = empid
and color <(
select top 1 color
from inv_line_item inv, stove s, invoice i)
group by e.name, s.color
order by count(*) asc;

my projection is wrong compare to the expected projection:
color name
------------ -------------------------------------------------- -----------
BLUE Fred Bailey 1

(1 row(s) affected)

If someone can review my query, I will appreciate it. I am new to this thing.

2nd question I want to find the employee who has sold the least number of stoves. I did try to start to write the queries because I do not have a clue how to express "the least" in SQL

Here is the table structure for CUSTOMER:
CustomerID Name StreetAddress
State
ZipCode
----------- ----------------------------------------------

Here is the table structure for INVOICE:
InvoiceNbr InvoiceDt
TotalPrice
CustomerID
EmpID

Here is the table structure for INV_LINE_ITEM
LineNbr
Quantity
InvoiceNbr
PartNbr
StoveNbr
ExtendedPrice

Here is the table structure for stove:
SerialNumber Type
Version DateOfManufacture
Color
EmpId

Thanks

********
***sg***
********

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-05 : 17:55:47

Duplicate http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79961

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources