About Geoff Expertise I specialise in database analysis and design, SQL and database queries using QBE and VBA. In my work, I use MS Access together with MS SQL Server as ETL (Extraction - Transformation - Loading) tools for migrating data between business ERP systems and data stores. My forte is building bespoke functions and applications.
See my
website for example apps and downloads
Experience I am a chartered engineer with 30 years of engineering and business experience, member of the BCS and have been working specifically in database applications, including SQL Server (v7/8/2000) for the last 9 or so years. I previously taught a course in Database Analysis and Design, but am now a freelance consultant and systems analyst.
Commercial database design and development work undertaken.
Question Hello, was wondering if you could help, The problem is very simple, I have a Simple table that I am using, one of the fields is called Indicator, there are only two possible values that are the Number 1 and Number 2. What I want to do is run a query (or something) that returns only the first record where the value in the Indicator field = 1, then all of the remaining records regardless of the value of field Indicator.
Thank you very much for your help.
Answer Hi Jim
The query below should do what you want: you will need to substitute in the values where I have put in dummy elements.
SELECT TOP 1 Xx, Yy, Zz
FROM tblYourTable
WHERE [Indicator]=1
ORDER BY Xx, Yy, Zz
UNION
SELECT Xx, Yy, Zz
FROM tblYourTable
WHERE [Xx] Not In (
SELECT TOP 1 Xx, Yy, Zz
FROM tblYourTable
WHERE [Indicator]=1
ORDER BY Xx, Yy, Zz
)
ORDER BY Xx, Yy, Zz
Basically what we have is a UNION query, which is the joining of two similar queries. The top query returns the first record with an [Indicator] = 1 to your specified sort order (Xx, Yy, Zz)
The lower query returns all other records by you sort criteria, but excludes the record from the top query. Please note that this exclusion is based on the column [Xx], which I have assumed to be Primary or Unique Key.
If you have any problems getting this to work please revert with some sample data ( say 10 records).
