Hi,
I'm working on a simple performance-program, where I need to extract
information from the 2 newest periods for every performance-indicator
- And from there calculate a trend between these results.
The problem is, that I can't find a simple way to extract the 2 latest
results.
The Table (Table1) looks like this:
kpiID periodID Actual
Acceleration 2 3
Acceleration 5 4
Speed 1 100
Speed 4 200
Speed 7 220
Speed 9 180
Weight 1 22
Weight 3 32
Weight 7 21
Weight 10 33
If I want to extract the newest I use something like this (made it in
MS Access, so the syntax might differ slightly from SQLServer):
SELECT table1.kpiID, table1.periodID, table1.Actual
FROM table1 WHERE table1.periodID = (SELECT max(t.periodID) from
table1 as t WHERE t.kpiID=table1.kpiID);
BUT - how how do I get the second-newest period as well?
Preferably I would like the final result to be a View with the
following fields:
kpiID, periodID_newest, Actual_newest, periodID_sec_newest,
Actual_sec_newest
Alternatively a View with 2 posts for each performace-indicator.
Thanks in advance
Ryan 10 1318
On Fri, 24 Mar 2006 23:08:18 +0100, Ryan Dahl wrote: Hi,
I'm working on a simple performance-program, where I need to extract information from the 2 newest periods for every performance-indicator - And from there calculate a trend between these results.
The problem is, that I can't find a simple way to extract the 2 latest results.
The Table (Table1) looks like this: kpiID periodID Actual Acceleration 2 3 Acceleration 5 4 Speed 1 100 Speed 4 200 Speed 7 220 Speed 9 180 Weight 1 22 Weight 3 32 Weight 7 21 Weight 10 33
If I want to extract the newest I use something like this (made it in MS Access, so the syntax might differ slightly from SQLServer):
SELECT table1.kpiID, table1.periodID, table1.Actual FROM table1 WHERE table1.periodID = (SELECT max(t.periodID) from table1 as t WHERE t.kpiID=table1.kpiID);
BUT - how how do I get the second-newest period as well?
Hi Ryan,
SELECT a.kpiID, a.periodID, a.Actual
FROM table1 AS a
WHERE (SELECT COUNT(*)
FROM table1 AS b
WHERE b.kpiID = a.kpiID
AND b.periodID >= a.periodID) <= 2 Preferably I would like the final result to be a View with the following fields: kpiID, periodID_newest, Actual_newest, periodID_sec_newest, Actual_sec_newest
In that case, try this instead:
SELECT a.kpiID, a.periodID, a.Actual, b.periodID, b.Actual
FROM table1 AS a
LEFT JOIN table1 AS b
ON b.kpiID = a.kpiID
AND b.periodID = (SELECT MAX(c.periodID)
FROM table1 AS c
WHERE c.kpiID = a.kpiID
AND c.periodID < a.periodID)
WHERE a.periodID = (SELECT MAX(t.periodID)
FROM table1 AS t
WHERE t.kpiID = a.kpiID)
(Both queries above are untested - see www.aspfaq.com/5006 if you prefer
a tested reply).
--
Hugo Kornelis, SQL Server MVP
Hi Hugo,
Thanks a lot. I got them both working without any hassle. SELECT a.kpiID, a.periodID, a.Actual FROM table1 AS a WHERE (SELECT COUNT(*) FROM table1 AS b WHERE b.kpiID = a.kpiID AND b.periodID >= a.periodID) <= 2
I find this to be quite clever - had to look at it some time to figure
out how it works. Preferably I would like the final result to be a View with the following fields: kpiID, periodID_newest, Actual_newest, periodID_sec_newest, Actual_sec_newest
In that case, try this instead:
SELECT a.kpiID, a.periodID, a.Actual, b.periodID, b.Actual FROM table1 AS a LEFT JOIN table1 AS b ON b.kpiID = a.kpiID AND b.periodID = (SELECT MAX(c.periodID) FROM table1 AS c WHERE c.kpiID = a.kpiID AND c.periodID < a.periodID) WHERE a.periodID = (SELECT MAX(t.periodID) FROM table1 AS t WHERE t.kpiID = a.kpiID)
Works as well - minor adjustment needed: Move lines 5-8 to the end.
Regards
Ryan
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
I am a little confused on this. Aren't "acceleration", "speed", and
"weight" attributes and not values? Surely you are not mixing
meteadata and data.
celko, have you actually ever HELPED anyone on this list?????
i'd be curious to review a link where your original SQL code, written
in the past 10 years, is deomonstrated.
thx,
doug
On Sat, 25 Mar 2006 08:21:38 +0100, Ryan Dahl wrote: SELECT a.kpiID, a.periodID, a.Actual, b.periodID, b.Actual FROM table1 AS a LEFT JOIN table1 AS b ON b.kpiID = a.kpiID AND b.periodID = (SELECT MAX(c.periodID) FROM table1 AS c WHERE c.kpiID = a.kpiID AND c.periodID < a.periodID) WHERE a.periodID = (SELECT MAX(t.periodID) FROM table1 AS t WHERE t.kpiID = a.kpiID) Works as well - minor adjustment needed: Move lines 5-8 to the end.
Hi Ryan,
That changes the meaning of the query - the place where you put those
lines dictates what will happen for a kpiID that has only one row.
This one row is by definition the latest - but there's no second latest.
If you use the query I suggested, you'll get this kpiID in your result,
with it's only row as last measurement and NULLs as it's second latest
measurement.
Your version (after moving those rows) will exclude any kpiID with only
one row. Only kpiIDs with two or more measurements will be displayed. If
that is indeed your requirement, then you can safely move these lines.
And you can change the LEFT JOIN in an INNER JOIN as well, to get some
performance gain.
--
Hugo Kornelis, SQL Server MVP
On Mon, 27 Mar 2006 23:48:50 +0200, Hugo Kornelis
<hu**@perFact.REMOVETHIS.info.INVALID> wrote: On Sat, 25 Mar 2006 08:21:38 +0100, Ryan Dahl wrote:
SELECT a.kpiID, a.periodID, a.Actual, b.periodID, b.Actual FROM table1 AS a LEFT JOIN table1 AS b ON b.kpiID = a.kpiID AND b.periodID = (SELECT MAX(c.periodID) FROM table1 AS c WHERE c.kpiID = a.kpiID AND c.periodID < a.periodID) WHERE a.periodID = (SELECT MAX(t.periodID) FROM table1 AS t WHERE t.kpiID = a.kpiID) Works as well - minor adjustment needed: Move lines 5-8 to the end.
Hi Ryan,
That changes the meaning of the query - the place where you put those lines dictates what will happen for a kpiID that has only one row.
This one row is by definition the latest - but there's no second latest. If you use the query I suggested, you'll get this kpiID in your result, with it's only row as last measurement and NULLs as it's second latest measurement.
Your version (after moving those rows) will exclude any kpiID with only one row. Only kpiIDs with two or more measurements will be displayed. If that is indeed your requirement, then you can safely move these lines. And you can change the LEFT JOIN in an INNER JOIN as well, to get some performance gain.
Hi Hugo,
thanks for pointing this out. SQLServer accepted without any problems.
As mentioned earlier I tested on MS Access, and it seems that it
doesn't support this join-type (no error-description of any kind) so I
made the mistake of assuming there was a small error in the
sql-string.
Thanks again.
Ryan
I've read 2 books written by joe celko, and and both have really helped
me. I frequently profile my code to see what it is doing. So he's
helped me.
Go to the CMP archives for DBMS, DATABASE PROGRAMMING & DESIGN, and
INTELLIGENT ENTERPRISE magazines to go back over ten years. I have
written over 750 columns in the computer trade and academic press,
mostly dealing with data and databases. I currently write for BMC's
DBAzine.com e-magazine.
My six books: SQL FOR SMARTIES (Morgan-Kaufmann, 1995, second edition
1999, third edition 2005), SQL PUZZLES & ANSWERS (Morgan-Kaufmann,
1997), DATA & DATABASES (Morgan-Kaufmann, 1999) and TREES & HIERARCHIES
IN SQL (Morgan-Kaufmann, 2004) and SQL PROGRAMMING STYLE
Morgan-Kaufmann, 2005).
Past magazine columns include: "SQL Explorer" in DBMS (Miller-Freeman);
"Celko on SQL" in DATABASE PROGRAMMING & DESIGN (Miller-Freeman);
"WATCOM SQL Corner" in POWERBUILDER DEVELOPERS' JOURNAL (SysCon); "SQL
Puzzle" in BOXES & ARROWS (Frank Sweet Publishing); "DBMS/Report" in
SYSTEMS INTEGRATION (Cahner-Ziff); "Data Desk" in TECH SPECIALIST
(R&D); "Data Points" in PC TECHNIQUES (Coriolis Group); "Celko on
Software" in COMPUTING (VNC Publications, UK), "SELECT * FROM Austin"
(Array Publications, The Netherlands), and he was editor for the
"Puzzles & Problems" section of ABACUS (Springer-Verlag) and I ran the
CASEFORUM section 18, "Celko on SQL", on CompuServe.
So, what have you done?
if it is SQL Server 2005, use row_number() *untested*:
select * from(
SELECT table1.kpiID, table1.periodID, table1.Actual,
row_number() over(partition by kpId order by periodId desc) rn
FROM table1
) t
where rn<3
That's Joe Celko, the demi-god of SQL for pete's sake. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: (Pete Cresswell) |
last post by:
I've got some SQL that works as far as returning a recordset from a series of
UNION statements.
viz:
SELECT whatever
UNION this
UNION that
UNION other
|
by: sk |
last post by:
I have a table for storing alerts (exceptional situations) occuring on
devices that I monitor. Associated with each alert is an alert code, a
description, the device responsible for causing the...
|
by: Haydnw |
last post by:
Hi,
I have the code below as code-behind for a page which displays two images.
My problem is with the second bit of code, commented as " 'Portfolio image
section". Basically, the SQL query gets...
|
by: Wired Hosting News |
last post by:
Lets say I have 10 products in 10 different stores and every week I get a
report from each store telling me how many items they have left for each of
the 10 products.
So each week I enter in 100...
|
by: Wired Hosting News |
last post by:
I tried to be breif and give a scenario so as not to be overlooked because
it was soooo long. Let me give you real world.
I am a manufacturer of goods and produce 11 items that are distributed...
|
by: jason.teen |
last post by:
Hi,
I have a table such as below and I want to write a query which says
"For each product, Exclude all Rows where Rate is 0 and Code is not D,
but if any of the rows for that product is found...
|
by: jeff95350 |
last post by:
I'm using Access 2000, and I don't know a lot about Access.
My data has fields that hold a code for a time period. For instance, one time period might be '2006010' and that refers to a time period...
|
by: Stan |
last post by:
I am running in ACCESS 2003 a database with a single table. It
records service rendered to clients of a food pantry. As each client
is served the date is entered into 1-6 fields SvcDate1,...
|
by: Darsin |
last post by:
Hi all
I need to perform a summation on a column of a table based on a
criteria given in another column in the same table. The catch is i
need to perform different sums according to the number of...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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,...
|
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...
|
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,...
|
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...
| |