Select Top SQL Query

Hello,

I have a table named READING this table contains POINT_DETAIL_ID , VALUE and TAKEN fields. This table is used to store measurements taken on a point. Our consultants have asked for a report which lists the POINT_DETIAL_ID and the last two VALUES asscoitated with the Point Detail.

Does anybody have an idea if it is possible using SQL and if so how.

Many many thanks.
mimicuserAsked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
In which database you are working? If it is SQL Server 2005 or 2008, this will work.

WITH CTETab(POINT_DETAIL_ID,TAKEN,VALUE,RowNum) AS
(SELECT  POINT_DETAIL_ID,TAKEN,VALUE,
                   ROW_NUMBER() OVER ( PARTITION BY POINT_DETAIL_ID ORDER BY TAKEN  DESC)  As RowNum
  FROM Example_Table)
 
SELECT A. POINT_DETAIL_ID, A.VALUE AS LATEST_VALUE, B.VALUE AS VALUE_BEFORE
FROM CTETab A
JOIN CTETab B
ON A. POINT_DETAIL_ID = B. POINT_DETAIL ID
WHERE A.RowNum = 1 AND B.RowNum = 2
0
 
mirzasCommented:
TAKEN is date time?

It is definitely possible - just provide more details about the table fields.
0
 
mimicuserAuthor Commented:
Sorry always forget how important the field types are:

POINT_DETAIL_ID = uniqueidentifier
VALUE = float
TAKEN = datetime
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
SreejithGCommented:
SELECT TOP 2 * FROM READING  ORDER BY TAKEN DESC

Assuming the TAKEN column stores the time stamp
0
 
mimicuserAuthor Commented:
Sorry Sreejith it only returns two rows. I need the result to look like:

POINT_DETAILID then the Latest Value then Second Latest Value for every point_detail in the table.

It will allow our consultants to look at pointdetail and compare the latest value against what it was before.
0
 
quincydudeCommented:
or is it
SELECT  TOP 2  *  FROM READING group by POINT_DETAIL_ID ORDER BY TAKEN DESC  
?
0
 
quincydudeCommented:
try
select POINT_DETAIL_ID, reading.TAKEN,VALUE
from reading 
where TAKEN in (select top 2 TAKEN from reading  re2 
where re2.POINT_DETAIL_ID=reading.POINT_DETAIL_ID ) 
order by POINT_DETAIL_ID

Open in new window

0
 
mimicuserAuthor Commented:
I am explaining my problem very badly. I have created a very simple word document wich contains an example table and the desired result, I hope this helps.

Again thanks for your efforts so far.
Example-Table-And-Result.doc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.