Select lowest number from multiple rows in SQL

cs8404

I cannot quite figure out how to accomplish the following results.
My table is "Products" with the following fields:
ID Item Metal Size Price
1 Ring 18ctGold 4-7 $23.00
67 Ring 18ctGold 8-11 $30.00
70 Ring Silver 4-7 $10.00
75 Ring Silver 8-11 $15.00
I simply want to be able to show unique metal AND lowest price for each
metal; so, for above I would want to show rows with ID 1 and 70 only.
SELECT DISTINCT comes close but returns all distinct prices.

Would something like this call for sub-queries?
Any help from experts out there to figure this one is most appreciated.

Nov 13 '05 #1

Subscribe Reply

5487

Allen Browne

1. Create a query into this table.

2. Depress the Total button on the toobar (upper sigma icon.)
Access adds a Total row to the design grid.

3. Drag the Metal field into the grid.
Accept "Group By" in the Total row.

4. Drag the Price field into the grid.
In the Total row under this field, choose Min.

That's it.

If you actually wanted to bring in other fields as well, this article
explains 4 approaches:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<cs****@hotmail .com> wrote in message
news:11******** **************@ g47g2000cwa.goo glegroups.com.. .

I cannot quite figure out how to accomplish the following results.
My table is "Products" with the following fields:
ID Item Metal Size Price
1 Ring 18ctGold 4-7 $23.00
67 Ring 18ctGold 8-11 $30.00
70 Ring Silver 4-7 $10.00
75 Ring Silver 8-11 $15.00
I simply want to be able to show unique metal AND lowest price for each
metal; so, for above I would want to show rows with ID 1 and 70 only.
SELECT DISTINCT comes close but returns all distinct prices.

Would something like this call for sub-queries?
Any help from experts out there to figure this one is most appreciated.

Nov 13 '05 #2

cs8404

Many thanks, Allen - my query is now finally working the way that I
needed it to!

Nov 13 '05 #3

Similar topics

6270

Select Distinct Statement Help

by: Ralph Freshour | last post by:

I have a question about the following PHP script - I got it off a web site tutorial on how to count users logged into your site - my question is the $PHP_SELF variable - it writes the name of the web page to the 'file' field in the table - I don't understand why it is doing that - I mean, isn't the SELECT DISTINCT statement only pulling those records from that one web page? I guess I just don't follow what it is doing with that SELECT...

PHP

1392

Row number in SELECT

by: Christopher Key | last post by:

Hi, I currently have a fairly complex query that returns a variable number of rows, sorted by various criteria. I am trying to insert the result of this query into an additional table, along with a row number. Ideally, I would like to be able to do this with an INSERT SELECT statement, although I'm not sure quite how to achieve this. An example of what I'm trying to achieve might be: INSERT INTO tblfinishingpositions (raceid,...

MySQL Database

10774

SELECT * not returning any rows, but SELECT COL_NAME does!

by: Rowland Hills | last post by:

I have a table which is returning inconsistent results when I query it! In query analyzer: If I do "SELECT * FROM TABLE_NAME" I get no rows returned. If I do "SELECT COL1, COL2 FROM TABLE_NAME" I get 4 rows returned. In Enterprise manager:

Microsoft SQL Server

4305

Adding up combinations with select-multiple boxes

by: Ben Hallert | last post by:

Hi guys, I'm trying to figure out what bone headed mistake I made on something I put together. I've got a form (named 'context') that has a variable number of select-multiple inputs on it. Based on the number of variables passed through a GET string, I want to multiply the total number of selected items for each together to see how many possible combinations the selected items are generating. The following snippet of code...

Javascript

3989

poor select performance

by: AC Slater | last post by:

I have 1 table (out of many) that has very poor performance when performing a select into on. The select statement is called multiple times. We've found each call to take almost 1 second... we were expecting less than .1 seconds. The select stmt looks like: select key,data into key_buff, data_buff from tablename where key > curr_key AND key < max_key ORDER by key FETCH FIRST 1 ROW ONLY;

DB2 Database

1956

Select next item in DB without knowing what ID it has

by: Neo Geshel | last post by:

I have an Access DB, from which I am going to pull images. Each image has an associated ID, but the ID's are not necessarily sequential (some images may have been deleted, leaving gaps in the list of ID's). I am looking to be able to call an ID and its Image from the database, but also have returned the previous ID and the next ID, even when those actual ID's are not necessarily +1 and -1 to the ID being called. For example, I want to...

ASP.NET

4095

select for update & lock contention

by: Ed L. | last post by:

I think I'm seeing table-level lock contention in the following function when I have many different concurrent callers, each with mutually distinct values for $1. Is there a way to reimplement this function using select-for-update (or equivalent) in order to get a row-level lock (and thus less contention) while maintaining the function interface? The docs seem to suggest so, but it's not clear how to return the SETOF queued_item and also...

PostgreSQL Database

15898

php/mysql multiple select list box(beginner).

by: sathyashrayan | last post by:

Dear group, I have a task where I have to fetch datas from mysql and display it in the multiple select list box. I have fetched the datas and it is getting diplayed correctly in the multiple select box. I have used some thing like this. print "<select MULTIPLE SIZE='6' name='db_test'>"; while($row = mysql_fetch_object($handle)) {

PHP

9722

What is ONU?

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...

General

9603

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

10391

The easy way to turn off automatic updates for Windows 10/11

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,...

Windows Server

10121

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

6881

Couldn’t get equations in html when convert word .docx file to html file in C#.

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();...

C# / C Sharp

5550

Trying to create a lan-to-lan vpn between two differents networks

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...

Networking - Hardware / Configuration

5690

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

4333

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

3015

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General