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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
SreejithGCommented:
SELECT TOP 2 * FROM READING  ORDER BY TAKEN DESC

Assuming the TAKEN column stores the time stamp
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.