jhardman  3,406
Recognized Expert Specialist
Say I have a hypothetical db table of images, one column might be a name, one a url with the source of the image (the images aren't blobs in the db, just referenced) and a column with a category such as "photoreali st", "abstract", "impression ist" etc. On a given search I could return records from one or multiple (or no) categories, depending on the search terms. With me so far?
What if I wanted to pull up one record (first record that comes up is fine) from each category? Say a particular search returns 50 records with a category of "photo-realist" and 20 records in the category "impressionist" . I could easily sort by category and filter the records programatically to return one record from each category, but is there a pure sql approach?
Jared
4 1886  ChipR 1,287
Recognized Expert Top Contributor
I think you could do a
Select Max(PrimaryKey) From HypotheticalTab le Group By Category
then JOIN that result with the same table to get the rest of the record.
NeoPa 32,579
Recognized Expert Moderator MVP
If you GROUP BY the category, then use the First() aggregate function for each of the fields in the record you require.
To illustrate, assume a table of : tblPRODUCT - Field Type
-
ProdCode Text
-
Category Text
-
ProdPrice Numeric
If you wanted to show the first record found, then use something like : - SELECT First(tP.ProdCode) AS ProdCode,
-
Category,
-
First(tP.ProdPrice) AS ProdPrice
-
-
FROM tblProduct AS tP
-
-
GROUP BY [Category]
jhardman 3,406
Recognized Expert Specialist
That sounds really useful, I'd never used first() before. Apparently it is Access-specific. This project is for Access (so this solves the problem) but I wonder if there is an equivalent in SQL Server (what I usually use) that I can use in the future.
Jared
NeoPa 32,579
Recognized Expert Moderator MVP
It would appear not I'm afraid.
I checked on my SQL 2K server at work, then on MSDN, and although there are some aggregate functions available, First() is not one of them.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Corky |
last post by:
I have a very simple database. It tracks service calls. It has fields
for two technicians; Tech1 and Tech2. It captures their hours against
a labor category (electrical, plumbing, carpentry, etc.) I created a
query to summarize the hours for each techncian by pay period. It
works great. When I merely add a linked database to the query design
screen, without any other changes to the query, I lose several records
(23 of 126, for instance)....
|
by: Roy Gourgi |
last post by:
Hi,
I am using VS.NET 2003 and I have made a connection to a MS Access database
using the Server Explorer. Is this connection permanent, meaning that it
will be there every time I reopen my program. Furthermore, how do I add,
delete, find, update a record?
TIA
Roy
|
by: Justin |
last post by:
Using SQL server and VS.NET with ASP.NET/C# how can I retrieve the Key ID of
a record right after creating that record in the same event? Do I need to
close and reopen the connection and requery?
Thanks, Justin.
|
by: Justin |
last post by:
I have created a dataset with two tables and an insert command, I need to be
able to retreive the Key Identity after inserting into table "A" for use in
table "B".
Should I use ExecuteScalar() or is there a better solution?
Thanks, Justin.
|
by: Neal |
last post by:
Hi All,
I used an article on XSLT and XML and creating a TOC written on the MSDN
CodeCorner.
ms-help://MS.VSCC.2003/MS.MSDNQTR.2003FEB.1033/dncodecorn/html/corner042699.htm
However, it did'nt quite answer all my questions.
How would one create a 3 level TOC when each item level / node was
differently named (They used Template match and for-each, but the template
match worked as on a 3 level structure they usedf the same named xml...
| | |
by: Duderino82 |
last post by:
I was wondering if there is a way to collect the names of the fields
from a specific table. I think the soluction is to be researched in the
sql code but maybe someone knows of a way to o so directly from php.
Example.
Table: Categories
Field1: type1
Field2: type2
Field3: type3
Field4: type4
|
by: Azzuron |
last post by:
Hello folks. I have a question regarding a database i am working with. I am no expert on the MSSQL server nor any other really i guess...but i do know some SQL, and i have been trying to find a solution to my problem on MSSQL to have little luck.
Here is my situation:
We have flat files of data that are downloaded once per day. These files are imported Via BCP into some holding tables. We then have a "Translation" database that holds all...
|
by: keri |
last post by:
Hi,
I am creating a table where I want to use the date as the primary key -
and to automatically create a record for each working date (eg Mon to
Fri) until 30 June 2007. Is this possible? I do not want my user to
have to create a record for each date.
Thanks,
|
by: fran7 |
last post by:
Hi, If anyone has any pointer as to how to do this that would be great. This code gets me a list of categories only where they have artists listed in them. Instead of displaying the "category name" from tblcategories I want to change " & rs("Categoryname") & " to a <src=""" & rs("pictureurl") & """> and display only one artist image from each category "pictureurl". That could be a random artist picture or simply the first or last added in ...
|
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...
|
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,...
| | |
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...
|
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,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
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
| |
